Copy and Rename files from one directory to another using SSIS

Last Post 18 Apr 2008 05:31 AM by sqlserverdeveloper. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
sqlserverdeveloper
New Member
New Member

--
16 Apr 2008 09:04 AM
I am trying to do the following:
1. Copy files from c:\source to c:\Archieve folder
2. Then rename the files in c:\Archieve folder as File1_datetimestamp.xls
Any help is greatly appreciated, Thanks!!
sqlserverdeveloper
New Member
New Member

--
18 Apr 2008 05:31 AM
Here's what I did so far, struggling with this for the past two days to get it work, any help is greatly appreciated:
I have foreach loop container, inside which there's script task and data flow task, script task checks for the source file existence and then if the file exists it will load the data into the table using the data flow task. Now I added file system task
to the data flow task, i.e Inside foreach loop container added the following:
Script task
|
|
|
|
dataflowtask
|
|
|
|
File system task

so that once the data is loaded using the data flow task, I want
the source files to be moved to the archieve folder and then rename as filename_datetimestamp:
The following are the steps that I followed after adding the File system task:
1. Foreach loop editor:
Enumerator configuration:
Folder: C:\Source
Files: *.xls
Retrieve File Name: Fully qualified

Variable mappings:
New variable:
Name: MyFileValue
Type: string
Value: \File1 9-15.xls

Then added the follow variables:
1. Name: SourcePath
data type: string
Value: C:\Source

2. Name: ArchivePath
data type: string
Value: C:\Source\Archieve

3. FullSourcePathFileName
For this variable: In the properties:
EvalAsExpress: True
Expression: @[User::SourcePath] + @[User::MyFileValue]

4. FullArchivePathFileName
For this variable: In the properties:
EvalAsExpress: True
Expression: @[User::ArchivePath] + SUBSTRING( @[User::MyFileValue] , 1 , FINDSTRING( @[User::MyFileValue],".",1) - 1 ) + "-" + (DT_STR, 2, 1252) Month( @[System::StartTime] )+ (DT_STR, 4, 1252) Year( @[System::StartTime] )+ SUBSTRING( @[User::MyFileValue] , FINDSTRING( @[User::MyFileValue],".",1) , LEN( @[User::MyFileValue] ) )

Then in the Filesystem task editor:
Isdestinationpathvar: True
Destvar: user::FullArchivePathFileName
Overwritedest: False

Oper: Rename File

SourceConn:
Issourcepathvar: True
Sourcevar: User::FullSourcePathFileName

The error I am getting:
[File System Task] Error: An error occurred with the following error message:
"Could not find a part of the path '\\folder1\Source\\folder1\Source\File4 12-10.xls'.".

Sorry for posting such a lenghty one, I wanted to try my best to resolve this issue, before I give up.
Thanks in advance for all your help on this!!!


Acceptable Use Policy
---