SSIS - Best way to copy files from one directory to another

Linda Cornerstone

In SSIS, I was to:

1. take files from c:\directory1
2. copy them to cirectory2 with a different name (concatenated date on the end)
3. delete them from c:\directory1

Should I be using the Script Task for this I am wondering if I should be using the File System Task which copies directories or files, but I don't know if I could rename the files during the copy.

Also, I want the directory names to be in the configuration file. I am not sure how to do this. If I set up a flat file source connection, I need to specify a file name and I just want the directory names. How do I get them in the config file and how to I read them into my script from the config file

Linda

Rafael Salas

Linda,

this post should address your problem:

http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html

then you can use package configuration to set the path value to the variables involved:

JayH

I would use the File System Task rather than script. It would take two operations to do the move and then the rename.

Your configuration would place the directory name in a variable. Above you indicate that you have multiple directory names. If so, I would specify these together as a comma-separated string, then use a script task to split that string into an array that can be iterated by a For Each loop.

Once you have the directory name in a variable either directly from the configuration or from a ForEach iterator, you would do a ForEach to iterate the files in the directory. Hopefully all your files have the same extension since it will make constructing the new filename much easier. If so, only get the filename without the extension from the ForEach into another variable. With the directory variable, the filename variable, and a hard-coded extension you can create expression-based variables to generate the source and destination names for a rename operation. The "destination" or the rename would also be the "source" of the move (or vice versa if you choose to do the move first). Another variable will probably be set from the configuration for the move destination folder.

If you can't hard-code the extension in your expression to generate the new filename, then the expression will be more complicated as you have to insert the date into the filename before the extension.

JayH

Rafael Salas wrote:

Linda, this post should address your problem: http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html then you can use package configuration to set the path value to the variables involved:

Ah, move can't rename, but rename can move. Good one.

Linda Cornerstone

Rafael,
I don't understand your comment:

Rafael Salas said...
You need to access the property pane of the variable. Select the variable and press F5 to make the properties window visible. Then change EvaluetaAsExpression to TRUE and click in the expression property to bring the expression editor. I can't get to the EvaluateAsExpression.

I set up my SSIS package as you described and I get a File System Task errof "The path is not of a legal form." (maybe the problem will be solved if I can fix my first problem)

Linda

Jamie Thomson

Linda Cornerstone wrote:

...and press F5 to make the properties window visible...

It should be F4 by the way!!

-Jamie

Rafael Salas

Linda Cornerstone wrote:

Rafael, I don't understand your comment: Rafael Salas said... You need to access the property pane of the variable. Select the variable and press F5 to make the properties window visible. Then change EvaluetaAsExpression to TRUE and click in the expression property to bring the expression editor. I can't get to the EvaluateAsExpression. I set up my SSIS package as you described and I get a File System Task errof "The path is not of a legal form." (maybe the problem will be solved if I can fix my first problem) Linda

I am talking about the properties of a variable. There is a property EvaluateAsExpression that needs to be set to TRUE. Then you need to provide the expression.

Linda Cornerstone

Jamie,

F4 makes a huge difference!

Thanks,

Linda

Linda Cornerstone

Thanks! I got it. That is some really cool code. Way to go!!!!

Jamie Thomson

Linda Cornerstone wrote:

Thanks! I got it. That is some really cool code. Way to go!!!!

Hi Linda,

I'm interested to know what your opinions as regards DTS and SSIS after this. Many DTS developers come on here and, sometimes understandably, don't have very many kind words to say about SSIS. I am of the opinion that this is because using SSIS takes a different mindset and once a SSIS developer loses that DTS mindset (i.e. trying to employ the DTS way of doing things) then they find SSIS development alot easier.

What do you think

-Jamie

Linda Cornerstone

Jamie,

I learned SSIS first on a different project and now I have this task of converting an existing DTS package to SSIS. I think SSIS is MUCH easier! The auditing, error handling, config files, etc. and executing containers makes it so easy to use. I can't wait to get these DTS packages converted to one nice SSIS package! I am thankful for this forum, or I would be lost.

Linda

Jamie Thomson

Linda Cornerstone wrote:

Jamie, I learned SSIS first on a different project and now I have this task of converting an existing DTS package to SSIS. I think SSIS is MUCH easier! The auditing, error handling, config files, etc. and executing containers makes it so easy to use. I can't wait to get these DTS packages converted to one nice SSIS package! I am thankful for this forum, or I would be lost. Linda

OK thanks for the reply Linda, that's good to know. It certainly seems to be the case that people who experience SSIS before DTS prefer SSIS, I wonder if the opposite is true

Thanks again for taking the time to reply.

-Jamie

[Microsoft follow-up] Some feedback regarding DTS-v- SSIS

Linda Cornerstone

I am using Rafaels example to copy my files from one directory to another and rename them. The FullArchivePathFileName is defined as a variable with Evaluate as Expression = True. The problem is that I am trying to rename the file with a date in the format "MMDDYYYY" and the expression returns 1 digit for May. So, instead of: 05052007, I get 552007. In the past using vbscript, I checked the string length and concatenated a '0' in front of a month or day that was length=1. I cannot figure out how to do this in the Variable expression:

Expression for FullArchivePathFileName:

@[User::ArchivePath] + SUBSTRING( @[User::MyFileValue] , 1 , FINDSTRING( @[User::MyFileValue],".",1) - 1 ) + "-" + (DT_STR, 2, 1252) Month( @[System:tartTime] )+ (DT_STR, 2, 1252) Day( @[System:tartTime] )+ (DT_STR, 4, 1252) Year( @[System:tartTime] )+ SUBSTRING( @[User::MyFileValue] , FINDSTRING( @[User::MyFileValue],".",1) , LEN( @[User::MyFileValue] ) )

Anyone have any ideas

Thanks,

Linda

Rafael Salas

Linda Cornerstone wrote:

I am using Rafaels example to copy my files from one directory to another and rename them. The FullArchivePathFileName is defined as a variable with Evaluate as Expression = True. The problem is that I am trying to rename the file with a date in the format "MMDDYYYY" and the expression returns 1 digit for May. So, instead of: 05052007, I get 552007. In the past using vbscript, I checked the string length and concatenated a '0' in front of a month or day that was length=1. I cannot figure out how to do this in the Variable expression: Expression for FullArchivePathFileName: @[User::ArchivePath] + SUBSTRING( @[User::MyFileValue] , 1 , FINDSTRING( @[User::MyFileValue],".",1) - 1 ) + "-" + (DT_STR, 2, 1252) Month( @[System:tartTime] )+ (DT_STR, 2, 1252) Day( @[System:tartTime] )+ (DT_STR, 4, 1252) Year( @[System:tartTime] )+ SUBSTRING( @[User::MyFileValue] , FINDSTRING( @[User::MyFileValue],".",1) , LEN( @[User::MyFileValue] ) ) Anyone have any ideas Thanks, Linda

Linda,

You should be able to do it with the RIGHT function. See how here:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=691162&SiteID=1

Linda Cornerstone

JayH,

I may try your method if I can't get the other method to work. I first need to explore whether there is a way to name my file name correctly using Rafaels method. It took me a long time to get that set up and I am not ready to get rid of it quite yet.

Linda