March 23, 2007

SSIS: File System Task Move and rename files in one step

UPDATE: I have included a link to download the sample package at the end of the post!

In some ETL scenarios, when processing files, it is necessary to rename the already processed files and move them to a different location. In SSIS you can accomplish that in a single step using the File System Task. The example I have prepared assumes the package will process a set of files using a ForEach Loop container; then for each file, using the 'Rename' operation in File System Task will do both; rename and move the file.

Here are some screen shots and notes about the package:

First of all, the big picture. The control flow has a ForEach Loop Container with a File System Task inside. Notice that the DataFlow task is empty and it is intended to show where the real ETL work should go; but this can be different or not required at all.


























Then details about the ForEach Loop container. Basically ,this container is configured to process all *.txt files in C:\Temp\Source folder, where all the files 'to be processed' are expected to be.










































Now the trick, few variables, some of them using expressions:









The expressions are:

in FullSourcePathFileName:
@[User::SourcePath] + @[User::MyFileValue]

in FullArchivePathFileName:
@[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] ) )

Notice that SourcePath and ArchivePath variables hold only the origin and destination paths of the files.
Note: Make sure you set EvaluateAsExpression property of the variable as TRUE.

Lastly, the File System Task should be configured like this:




I am pretty sure there are different ways of accomplishing this simple task; but I like this one because it does not require writing custom code and relies on expressions.

You can download the sample package from here:



115 comments:

  1. nice example...but I don't see how you got the expressions tied to the variables..I must be missing something

    ReplyDelete
  2. You certanly are. See the section bellow ' The expressions are:'...

    ReplyDelete
  3. how can we input expressions with variables?

    ReplyDelete
  4. I think I got your doubt. 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.

    ReplyDelete
  5. Thank you.
    Now I could able to assign variables to expressions.But still I'm unable to get the file copy process(I followed the method which you explained)
    Appreciate your help

    Thank you

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. do you have a connection manager connection to the destination file? I try the same thing, but it errors out saying that the file doesnt exist. Yes, the file doesnt exist, i was thinking it would create it. instead it just errors out. help?

    ReplyDelete
  8. Make sure you are using rename. When you use rename, File System task don't expect the file to exists. Make sure also you provide intial values to the variables, so the package can be validated before running.

    ReplyDelete
  9. i'm wanna move all my ssis proj to other machine and i've a lot of file system connection whice i must rename them to the new path.
    does your post will help me i'm not sure...???
    10X.

    ReplyDelete
  10. This comment has been removed by a blog administrator.

    ReplyDelete
  11. Great example and thank you. Regarding the entry of the expression formulas, it's actually F4 to open the variable properties window. I must have initiated debugging on my package a half-dozen times with the F5 key before I opened up the menu items and found F4. Also, when using the properties window of the variable, I couldn't get the expression builder to come up. I ended up editing the expression in a text editor and then pasted the result back to the expression value field. Any thoughts on this?

    ReplyDelete
  12. Rafael,
    I want to thank you for putting up with us lesser skilled SSIS monkeys.

    It has not been necessary to post previously, as most thinks can be found from previous examples you've provided.

    However, the last post speaking about providing a path and filename for the rename method in the package bothers me some. We have multiple enviroments, and the package being developed in DEV may not use the same paths as PreProd or Staging. Currently, I'm receiving validation errors when I move the package from one Environment to the next. It's not really a big deal to open the package in BIDS, and edit it, but, in theory, all values should be picked up from the Variables, and only the configuration XML need be changed while moving to other Environments.

    Unfortunately, I'm bumping my head trying to resolve this particular issue. We do not want, as DBA's, to continue doing the deployments ourselves ... we supply steps for the Tech teams to deploy when possible. Opening up a programming gui and modifying the program actually violates compliance protocols.

    I have a script task that sets all my current connections dynamically so passwords between environments work ok, but, the hard coded file paths in some of the controls fail validation, even though the get overwritten after the fact. I'm sure I'm just missing something.

    ReplyDelete
  13. Gil,

    no, this post won't help in that case. If you need to change properties like paths, connection strings, etc. each time you deploy the SSIS solution to a new environment I would recommend you to use package configurations.

    ReplyDelete
  14. Chris,

    You are right, to see the properties window you can press F4. Regarding the expression editor not available to edit the expression of the variables; that is something that was added as part of the service pack 1. make sure you install the latest SP.

    ReplyDelete
  15. ctruett3,

    Package configuration should do the trick for you when executing the packages in different environments. But it won't change the values provided at design time, so if you want to edit the packages then, yes you have to modify the values each time you open them.

    ReplyDelete
  16. Thank you for sharing the code. It does just what you said. I need to go one step further, though. As I'm looping through the files, I want to pull them into SQL. I have my Foreach Loop Container set up and within it, I have an Execute SQL task and also the File System Task. I double-click the Execute SQL task to get to Data Flow and set up a Flat File Source and an OLE DB Destination. However, I cannot figure out how to make the Flat File Source accept a variable for the file name. I want it to basically point at what would be the @[user::FullSourcePath] variable. Instead, I have to point to a physical file in order to do the mappings. This results in me looping through and archiving all my text files but only the first file gets dumped into the SQL table over and over. Can you help me?

    ReplyDelete
  17. Never mind, Rafael! I got it. I needed my variable to be scoped all the way up to the package level. I do have another question, though. I am wanting to use my file name as an input variable into a stored proc that runs for each file. (My user names the files with the dates and then I run a stored proc that performs activity based on that date being input as a variable.) Any thoughts on how I can map a variable like @[User::MyFileName] to be the kind of variable I can feed into a SQL stored proc like exec sp_someproc @myfilenamevariable? Thanks again.

    ReplyDelete
  18. How do I connect to an external server with a different user than the one running the package? How do I specify which user to use with a file connection?

    ReplyDelete
  19. Rafael,
    as much as like your sample, I can't get the filesystem task to perform a simple task like rename. I have created two package level variables assigned the expressions to them. Also set the evaluate expression to true. I get error saying, the package cannot access the file. Any help is greatly appreciated.
    Srinivas

    ReplyDelete
  20. the only thing I would add is a timestamp - I was having problems with similar filenames after renaming if it happened more than once in a day. I dropped in the time as well:
    @[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((DT_WSTR,30)GETDATE(), 12,8)+ SUBSTRING( @[User::MyFileValue] , FINDSTRING( @[User::MyFileValue],".",1) , LEN( @[User::MyFileValue] ) )

    ReplyDelete
  21. okay, let me try one more time. the colons between the hours min and sec didn't go over well:)

    @[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((DT_WSTR,30)GETDATE(), 12,2)+ "-" +substring((DT_WSTR,30)GETDATE(), 15,2)+ "-" +substring((DT_WSTR,30)GETDATE(), 18,2)+ SUBSTRING( @[User::MyFileValue] , FINDSTRING( @[User::MyFileValue],".",1) , LEN( @[User::MyFileValue] ) )

    ReplyDelete
  22. Along with the time stamp i want the date part as well. Something like:

    DDMMYYYY HH:MI:SS

    Can someone help me out with the correct expression please?

    ReplyDelete
  23. panks21,
    Colon is not allowed for a file name. try using another delimiter like '-'...

    ReplyDelete
  24. This is really very good.....
    I had a bit confusion on how we can set the expression to a variable, but later on when i went through the conversation i found out the solution.

    In renaming the file if you want to rename the file with file name appended with date month year below is the code:

    FullArchivePathFileName:

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

    ReplyDelete
  25. With respect to validation errors, you can set the DelayValidation property on your tasks to True. This will delay validation until the package is executed. You can then have a script step to modify the variables based on a config file.

    This also speeds up package loading in the Designer.

    ReplyDelete
  26. I am not able to open property pane of a variable to write expression by pressing F4 .. can u plz guide me how can I do that?

    ReplyDelete
  27. hi, i did follow the steps but getting --> Error: 0xC002F304 at Copy and Rename File, File System Task: An error occurred with the following error message: "Illegal characters in path.".

    ReplyDelete
  28. Soham,

    Try using the option in the menu. The property pane usually appears a the bottom left side of the screen.

    Kai, the expression is clearly resolving a string that is not a valid path, double check the expression and its result by clicking evaluate expression.

    ReplyDelete
  29. please give valid daymonthyear & time stamp for filesrchivepath.

    ReplyDelete
  30. Thanks for posting this. One issue. The File System task uses the variable value instead of the expression and therefore is only allowed to go through the loop once and can't rename or remove anything past that package.

    Any ideas on why the File System task wants to take the value over the expression ? (Works fine for my dataflow if I disable the File System Task).

    ReplyDelete
  31. Hi - I want to move all the files in one folder to an archive folder. But with this example it looks for the same file. After it moves the first file it comes back and looks for the same file instead of the next file to be moved. What am I doing wrong?

    ReplyDelete
  32. Thanks for your help. That's exactly what I was looking for... You save me a lot of time ;-)

    ReplyDelete
  33. This is a great post, I had to modify it, but make sure you change EvaluateAsExpression to True, or the variable filename will not be used, took me a few minutes to figure out why it keep using the default variable values.

    ReplyDelete
  34. Thank you, Just what I needed. easy enough for a first time SSIS experience, in depth enough to solve my problem

    ReplyDelete
  35. Justin,

    Thanks for pointing that out. I have updated the post to specify that you have to set EvaluateAsExpression property to true on the variables using expression. I believe that is issue reported by SSISMonster and Travis.

    ReplyDelete
  36. This is a great example. I need help adjusting the SourcePath. Instead of it being static at c:\sourcepath, i need it to look one folder deeper at "TodaysDate". So today i would need to start in c:\sourcepath\10282008\ Tomorrow i would need to start in c:\sourcepath\10292008\
    Anyone got any ideas?

    ReplyDelete
  37. Hi Rafael,

    Thanks for the post. I'm trying to insert about 500 CSV Files into a table, and then archive each file after it has been processed.

    The issue is that your package works 100 % if there is a blank Data Flow Task before the file system task, but as soon as I put actual workings into the Data Flow Task the package fails.

    Either the Data Flow Task works and the File Task fails if I change the "Retrieve File Name" to "Fully Qualified", or the File System task works but the Data Flow Task fails when the "Retrieve File Name" is set to "Name and Extension".

    Something seems to be happening with the file path values that are used when you change the type of File Name Retrieval you want to use.

    Any ideas on what to do?

    Thanks,
    Ignacio

    ReplyDelete
  38. It's worth noting that counter intuitively this doesn't work when the Operation is set to "Move file" rather than "Rename File". Go figure?

    ReplyDelete
  39. hii rafael nice post really helped me '

    i have one problem with this when i execute the package i got the error

    =>[File System Task] Information: File or directory "E:\Data\Destination" was deleted.

    =>[File System Task] Error: An error occurred with the following error message: "Access to the path 'E:\Data\Destination' is denied.".



    this is wat i m getting even i had provide the access level to full but still same problem but if i try to move without renaming it is working fine please help thanks

    ReplyDelete
  40. I felt hard to understand the regular Expressions in this article because I don't know their syntax... I tired to find in google but I could not find... Do you know any articles on the regular expressions?

    ReplyDelete
  41. this is all very nice, and it is so nice to see that there is a workaround to every problem but.........
    am i the only one that sees the naked king?? i mean so much work and code (variables and commponents and and validation errors and properties editing) and all it should have realy taken was that our firnds and redmond have put an option to use wild cards in this "lovely" (NOT) compnonent calld "file system task" i mean wildcards have been with us since the old dos days (even b4 that i think) so was it that hard for them to allow the use of it here???? i mean why do i need to bother with all this?
    wouldnt it be so much more easier like that? oh imagine

    ReplyDelete
  42. Nice Example.. Thanks..

    Similar to the filesystem task, can we also dynamically pass file names to Flat File Connection?

    The actual requriement is to load multiple files in a directory to a database. The files names are dynamic appended with a date field

    ReplyDelete
  43. Casper,
    Appending a subfolder to SourcePath could be possible by having the subfolder name in an additional variable, then you would modify the expression in FullSourcePathFileName variable to include the variable with the subfolder name.

    Ignacio,
    I see your point, and it is totally valid. A flat file connection manager requires a full qualified file name (path and file name). You have 2 options, either create a separate containers or have 2 packages; one to process the files and another to archive them. The other option is to use Fully qualified file name in the foreach container and tweak the expression in the File System task to remove the unwanted part (path).

    ReplyDelete
  44. Karthik,
    These are not regular expressions, but rather SSIS expressions. Books on line has a reference that would help you getting started: http://technet.microsoft.com/en-us/library/ms141232.aspx

    Daniel, good suggestion. You should log that as a suggestion in the SQL Server connect site where members of the SQL Server team would consider it: http://connect.microsoft.com/sqlserver

    Logesh,

    Absolutely, you just have to include an expression in the flat file connection manager to make it 'dynamic' at run time. The principle is the same, and is you do little of research you will find examples.

    ReplyDelete
  45. i have a doubt while using file system task if the files are present in the newtwork drive and if we schedule the package it is throwing error can you suggesnt on this why

    ReplyDelete
  46. anjali,
    The problem could be caused by many things. Make sure the ID running the job has proper permissions over the netwrok resources. I would recomend you to post your question in MSDN SSIS forum (http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/threads/) and shared more details about your package and error messages.

    ReplyDelete
  47. Hi Rafael,

    I have used this solution on several SSIS packages and it has worked out great. However, on this last package, when I am renaming the file, for some reason it is adding the time stamp and extension several times, as though it is not resetting.

    For instance, I have used both of these expressions
    - @[User::ArchivePath] + SUBSTRING( @[User::MyFileValue] , 1 , FINDSTRING( @[User::MyFileValue],".",1) - 1 ) + "-" + (DT_STR, 2, 1252) Day( @[System::StartTime] ) + (DT_STR, 2, 1252) Month( @[System::StartTime] )+ (DT_STR, 4, 1252) Year( @[System::StartTime] )+ SUBSTRING( @[User::MyFileValue] , FINDSTRING( @[User::MyFileValue],".",1) , LEN( @[User::MyFileValue] ) )

    or

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

    The file name is changed to

    \\webserver\location\test\Archive\TSi_Details392009.TXTTSi_Details392009.TXTTSi_Details392009.TXTTSi_Details392009.TXT

    Do you have any idea if I am forgetting something or a flag I need to set?

    ReplyDelete
  48. Rafael, I used your procedure on my vm (while waiting for the arrival of our new server) and it worked great! However I've moved the package over to my production machine and cannot get it to work now. I get the following error when trying to process the Foreach Loop:
    [File System Task] Error: An error occurred with the following error message: "Could not find file 'C:\Program Files\OLAPFiles\abc.csv'.".

    I have remapped my SourcePath and ArchivePath to their new directories, and changed the Value in each variable to the correct path. Any suggestions?

    ReplyDelete
  49. Rafael, In anticipation of a new server and SQL ’08, I created several SSIS packages on a virtual machine that included your file system task to date and move my files after they were processed. Everything worked spectacular on the vm, however I have moved my package from my vm to my production machine and I now get the following error:
    [File System Task] Error: An error occurred with the following error message: "Could not find file 'C:\Program Files\OLAPFiles\abc.csv'.".
    I have changed the value of the four variables that contain the source or archive path, but I still cannot get it to work.
    What am I overlooking? Suggestions?
    Thanks in advance for your response.

    ReplyDelete
  50. Rafeal - great example. I was able to move and rename the files just as I wanted. Thanks a lot.

    ReplyDelete
  51. Hi Rafael - great example. I was able to move and rename the files in archive folder just as I wanted. Thank you very much.

    ReplyDelete
  52. Rafael,

    Thank you for this example, worked great for me.

    Best regards,

    Z

    ReplyDelete
  53. Rafael -

    I keep getting a msg below

    [File System Task] Error: An error occurred with the following error message: "The path is not of a legal form.".

    ReplyDelete
  54. Worked like a charm. Thanks for adding the download too, made things a lot easier.

    Cheers,
    Kristy.

    ReplyDelete
  55. Rafael -

    I get the same error message as Ken. Anyway you can help?

    [File System Task] Error: An error occurred with the following error message: "The path is not of a legal form.".

    ReplyDelete
  56. Nice article. Is it possible to copy or move a file from one folder to two diiferent folders. Meaning a file called Excel.xls should be copied or moved to folders "Backup" and "Copied"

    ReplyDelete
  57. I need to copy one excel file from a folder and paste it in two different folders. How this can be achieved?

    ReplyDelete
  58. Hi Rafael,
    I am trying to process each file inside the for each loop container but I cant create a file connection and assign the porperty conection string equal to the FileName variable because it is not visible. Help?
    Thanks
    Sam

    ReplyDelete
  59. I've having the same issue as Sonya did. Can't figure out why it's duping the name.

    ReplyDelete
  60. ctaylor & Shairal,
    I was getting the same error and it turned out I had mixed up the variables assigned to "Destination Variable" and "Source Variable".

    Thanks for the article.

    ReplyDelete
  61. Thanks for the good example, it allowed me to move files from within a Foreach Loop Container.
    --Mark

    ReplyDelete
  62. Hi Rafel Salas

    I have a ForEach Loop Container that perform a data task(loads data from text file) and a File System task that moves each file after its loaded to the table. But now I have a requirement where I need to create a folder on the fly and then move all the text files to that folder (the folder name should have a current date and time as well). Is there a link I can use for this purpose.

    ReplyDelete
  63. Hey Rafael,
    I Like your Post that solved my problem and i learned something out of it as well.

    Thank You Very much.

    GOD BLESS YOU

    ReplyDelete
  64. Hey Rafael,
    I like your post.
    It helped solve my problem and i learned out of it as well.

    Thank You Very much

    GOD BLESS YOU

    ReplyDelete
  65. I've downloaded your sample file but it will not load. I get the following error: Error loading Move and Rename File 1 step.dtsx: Failed to load XML due to error 0xC00CE584 "DTD is prohibited. Line 1, Column 11". This happens when loading a package and the file cannot be opened or loaded correctly into XML document. This can be the result of either providing an incorrect file name to the LoadPackage method or the XML file specified having an incorrect format.

    ReplyDelete
  66. hi,

    I hope you or someone else might be able to me with this.

    I've basically set up a package, like your example, but I get an unexplainable error.

    For a file that already existed in my chosen For Each Loop directory, the process works a treat, creating a renamed copy of a source file.
    If however I created a new file in that directory (simply a blank text document) it wont process the file??

    I get the error: [File System Task] Error: An error occurred with the following error message: "The process cannot access the file 'C:\...\Documents\commtrac_jde\Myfile.txt' because it is being used by another process.".

    The file isn't open or being accessed by anything else!! I even restarted my machine and then run the package, but the same error occurs.

    I would really really appreciate your help

    ReplyDelete
  67. Rafael ON MY sis package I need to rename each file and add date here only you add date you are not changing the file name how can I do it INSIDE foreach loop
    for instance my sources files are

    07) Jan 2010 data.txt
    chn_0110_Magellan.txt

    and I need change to

    ctr_bvf_22010.txt
    ctr_chn_22010.txt

    how do I create a variable at collection level that grabs the first part of the names and really rename my files?

    ReplyDelete
  68. thanks for help and a nice article.

    Amit Patel

    ReplyDelete
  69. Thanks for your help and very nice article.

    Amit Patel

    ReplyDelete
  70. I am having a similar issue as described on Sonia's post,the timestamp and extension are added twice.
    At run time I get" "Could not find file \\Myfile_backup_201003242200.bak201003242200.bak' though the expression evaluates OK.

    This is my expression:
    @[User::SourcePath] + @[User::MyFileValue]+
    (DT_STR,4,1252) DatePart("yyyy",getdate()) +
    Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
    Right("0" + (DT_STR,4,1252) DatePart("d",DateAdd("d", -1, getdate())
    ),2) + "2200.bak"

    Any help appreciated.

    Thanks,
    Jose

    ReplyDelete
  71. Very nice and useful example. Illustrates the use of Foreeach Loop Container, Variables and expressions and File System Task. Thanks a lot!

    ReplyDelete
  72. Hi Rafael,

    this is simply superb.. u really rock...!

    ReplyDelete
  73. the File System Task is buggy, it wil log that your destination file is deleted instead of your source file:

    Information: 0xC002F30E at Backup datafile, File System Task: File or directory "c:\temp\backup\20100519_detail.csv" was deleted.

    ReplyDelete
  74. Hola Rafael,
    I have a problem moving/copying files to a network location.
    The package works perctly from Visual Studio. But when I deploy it and run it with the run package utility it gives me the error "could not find file". This is even without using SQL Server Agent.

    It seems to me that this is an access rights issue because if I use another network location the package runs OK.

    However, what's strange is that I do have full access to the network location I need, because it works perfectly from Visual Studio.
    Any ideas?

    Thanks!

    ReplyDelete
  75. Hi Rafael, I have a situation. There is a Source Folder and there are multiple xml files that gets creates such as file201006010600.xml, file201006010605.xml,file201006010610.xml,file201006010615.xml. My Objective is to move these files to an Archive folder. WHen I used your example I am getting the following error.

    Could you please help.
    Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task.
    Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
    Error: 0xC002F304 at File System Task, File System Task: An error occurred with the following error message: "Could not find file 'C:\Documents and Settings\Administrator\Test1.xmlTest1-62010.xml'.".
    Warning: 0x80019002 at File System Task: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
    Task failed: File System Task
    Warning: 0x80019002 at Foreach Loop Container: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
    Warning: 0x80019002 at Move and Rename: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
    SSIS package "Move and Rename.dtsx" finished: Failure.
    The program '[2188] Move and Rename.dtsx: DTS' has exited with code 0 (0x0).

    ReplyDelete
  76. Hello Rafael, I am new with SSIS and I have a problem with a package that I'm creating.
    I am exporting the content of a table in SQL into a flat file destination with this name:

    CAFXCLI_DP001_SP000_20100617_233714.TXT

    Now, I need 100 flat files with the same content but with different names, I have to change this part: DP001 in the file name, to this one: DP002, DP003, ..., until DP099 for my 100 flat files.

    I am not so good with my english but I hope you can understand my problem and help me please.

    Thank you.

    ReplyDelete
  77. One of the best, if not THE best, examples of for-each with file rename I have seen. Very easy to read and understand (aka, copy...)

    Thank you for the excellent post

    ReplyDelete
  78. Hi, I wish to use the System::StartTime variable in a Variable expression to create a dynamic filename, but can't get your example to work. I've set "EvaluateAsExpression" to true and have entered an expression similar to "Z:\ThisLocation\ThisFile" + (DT_STR,2,1252)Month(@[System::StartTime]) + ".xls" but it throws an evaluation error. I tried a basic expression of "Month(@[System::StartTime]) and that worked, but as soon as I tried to add the explicit conversion (DT_STR,2,1252) in front it failed. What am I doing wrong?

    ReplyDelete
  79. Kevin,
    Likely, The expression you are using evaluates to an invalid file name (contains colons). You need to tweak your expression further to make sure the final result can be used as file name.

    ReplyDelete
  80. hi Rafael sals,
    if my source file is \\sql.hccsj.local\Backup\test2.txt

    and destination folder is \\apps1\Ambulance_Backup

    Your solution won't work as it kept asking can not find files...

    any idea?

    thanks

    Hui

    ReplyDelete
  81. Wondering if there was any response to Dave's issue above? I am having the same issue.

    ReplyDelete
  82. Hi Rafael

    I have tried to very carefully follow your example of the Rename and Move in one step. I'm getting an odd error because the error message points to my "C" drive even though all of my variables point to the "M" drive and it doesn't show the correct directory. I almost have to be pointing the "C" drive somewhere and not know it right?

    The error message says:
    Error: 0xC002F304 at File System Task, File System Task: An error occurred with the following error message: "Could not find file 'C:\Documents and Settings\plong\137TPA_ELIG_BI_FORMAT Aug2010.txt'.".


    My variables are set up as follows with all of them having scope limited to the for each loop and the two FullPathFileName variables are set to EvaluateAsExpression = True

    MyFileValue - abc.txt
    FullSourcePathFileName - @[User::SourcePath] + @[User::MyFileValue]
    FullArchivePathFileName - @[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] ) )
    SourcePath - M:\Department\IT\PLong\FACTS TO BI\Membership\
    ArchivePath - M:\Department\IT\PLong\FACTS TO BI\Archive\

    I am currently running Visual Studio 2005 Version 8.0.50727.762


    Thanks for any help

    ReplyDelete
  83. hi friends.
    pls solve my query
    i am store multiple file path with different formats like .xls, .xml in file table. i want to read this file path using loop and import into table using ssis

    how can i do this

    ReplyDelete
  84. Jean Here.
    This IS a Good Post as well.
    This helped me about 1 year ago.
    Thanks Man..

    ReplyDelete
  85. vinothlilly,
    Your scenario is more complex than what this post is trying to accomplish. I would use something list this to get the file names/path from the table: http://www.rafael-salas.com/2006/12/import-header-line-tables-into-dynamic_22.html . Then you may need to create separate data flows or packages if file have different formats.

    ReplyDelete
  86. As you have moved and renamed files, is it possible to create a zip folder of all files within a folder and its sub-folders that are older than a year and delete the files once zipped. Then copy the .zip file to a specific folder?
    Any good solution for archiving files in this manner?

    ReplyDelete
  87. I followed these steps but what i found is this suitable when we have a exactly one file. but in my problem i can have more than one file.. therefore please help me to solve my matter.. thank you..

    ReplyDelete
  88. Thanks Rafael...It is really a nice post..
    can you help me on this...Is there any way that i can create and rename the file at the same time...

    Thanks...

    ReplyDelete
  89. Hi,
    Your example works fine for me. But i want keep source files, i want copy not move? why i can do this?

    ReplyDelete
  90. I understand the rename part of this, but can't see where the 'move' part is occurring. :(

    ReplyDelete
  91. Great example even for a newbe like me. Keep your good work!

    ReplyDelete
  92. Hi Rafael,

    My file path in dev is "G:\" and production is "H:\". Is there a way to pass in the file path using config file with package variable and without setting up environment variable? I tried to passed it in but it is only reading the value at design time, which is the one pointing to the development path.

    Thanks,
    Nick

    ReplyDelete
  93. Great example - thanks!

    ReplyDelete
  94. Hi,
    I have few files that arrive without the extension. Hence the 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] ) ) throws an error. Is there a resolution for such a scenario? If yes, please help. Thanks.

    ReplyDelete
  95. Rafael, I needed to do a similar task and your solution helped me perfectly. Thanks a lot for taking the time to explain this in a blog.

    ReplyDelete
  96. Works like charm.....thanks

    ReplyDelete
  97. Great Example. My souce file is SPY_IMPORT.txt. This example helped me to move and rename the file. I have changed a bit and final file I am gettiing with following format SPY_IMPORT-2011_7_20.txt
    Specially the SAMPLE PACKAGE is very helpfull.
    However the Month is coming in single digit.
    How can we cahange that to two digits. I mean how to get the file as SPY_IMPORT-2011_07_20.txt?

    ReplyDelete
  98. Hi I got ther solution for my post also:
    the expression will be
    @[User::ArchivePath] + SUBSTRING( @[User::MyFileValue] , 1 , FINDSTRING( @[User::MyFileValue],".",1) - 1 ) + "-"+ (DT_STR, 4, 1252) Year( @[System::StartTime] ) + Right("00", 2 - Len((DT_STR, 2, 1252) Month( @[System::StartTime] ))) + (DT_STR, 2, 1252) Month( @[System::StartTime] )+ (DT_STR, 2, 1252) Day( @[System::StartTime] )+ SUBSTRING( @[User::MyFileValue] , FINDSTRING( @[User::MyFileValue],".",1) , LEN( @[User::MyFileValue] ) )
    And it will give output as SPY_IMPORT-20110720.txt

    ReplyDelete
  99. Thanks Rafael, I have been using this mechanism for sometime, but I notice it preserves the "last modified date" on the file. Do you know how I can make it change the "last modified date" to the date & time the file was moved/renamed?

    ReplyDelete
  100. Hi Rafael, your article is great. I learn a lot from this. One question, when clicking on 'Move and Rename File Step' (Connection Managers) and select properties, how do I know it's related to this particular SSIS package and not another? Thanks. David.

    ReplyDelete
  101. The connection managers as they appear in the bottom part of the editor are tie to package you have open in the active tab.

    ReplyDelete
  102. I have an issue I can't seem to find an answer on with SSIS that is slightly similar to your scenario. I have 10-15 dbf files that need copied to another directory before I can import them to my db. My issue is that there are over 100 dbf tables in the source directory. How do I do a for loop that only does the 10-15 dbf tables I need to run the file system copy task on? Thanks, Dan

    ReplyDelete
  103. This example works for Copy File, but exact same package fails if i change it to Move File. Please advice.

    ReplyDelete
  104. Hi Rafael,

    I am having a problem. First of all thank you for this. I am having error
    [File System Task] Error: An error occurred with the following error message:
    "The parameter is incorrect.".

    In one machine package runs fine with no error, but i am trying to run this package in other machine and i am having error. Please email me if you can. Thanks.

    ReplyDelete
  105. Very good article...do you know how to move multiple files that the names vary? This works great when you know the name of the file but if the file names vary and I just want to move any .txt file, how would I tweak this? Thanks, Mike

    ReplyDelete
  106. This was a very good, helpful, and concise post concerning a common scenario.
    I did manage to add a little to the FullArchivePathFileName variable that allows it to have a 2 digit month and a 2 digit day in the format of YYYYMMDD and the 1 digit days and months add a 0 to the front making them 2 digits as well. Without that, you couldn't distinguish 1212008 whether it was 12/1/2008 or 1/21/2008, or in my case with the year first, 20080121 instead of 2008121

    C:\Temp\Archive\abc-20080121.txt
    @[User::ArchivePath] + SUBSTRING( @[User::MyFileValue] , 1 , FINDSTRING( @[User::MyFileValue],".",1) - 1 ) + "-" + (DT_STR, 4, 1252) Year( @[System::StartTime] )+ RIGHT("0"+(DT_STR, 2, 1252) Month( @[System::StartTime] ),2)+ RIGHT("0"+(DT_STR, 2, 1252) Day( @[System::StartTime] ),2)+ SUBSTRING( @[User::MyFileValue] , FINDSTRING( @[User::MyFileValue],".",1) , LEN( @[User::MyFileValue] ) )

    ReplyDelete
  107. Hi Rafael,
    Thanks for the article. It was very helpful. However I'm encountering few problems. I have a folder which gets files every now and then. The files are named in this fashion: ABC-2011.xls,ABC-2012.xls and they have the same metadata. Right now the folder has only ABC-2011.xls and when I run my package, it reads data from the sheet and loads the data to the database, rename and move the data. If I run the package a secons time, it fails because there are no files in the folder. Is there any way by which we can dynamically assign value to the variable "MyFileValue" at teh time of package execution ?

    ReplyDelete
  108. Hi,
    This is really nice article. without any problem I completed the ETL. But at the very begining i couldn't understand some important points like,
    1)how to assign new variables?
    2)Where write the expressions?

    I think if you pointed out these main parts, It will be really helpful for the beginners or people who not try these kind of task before.

    Other than that I really appreciate your great help.
    Thanks....

    ReplyDelete
  109. Thank you for this recipe.

    I tried it, but wanted to move the file instead of renaming it. The task fired an errr every time I tried to run it.

    Out of despair, I changed the move to a copy followed by deleting the original with the reasoning that the source file may have been kept open. And, much to my surprise, I have been right on this one... it works. Strange but true.

    ReplyDelete
  110. Thank you for the nice post helped me in completing one of my complex SSIS package.

    ReplyDelete
  111. Thank you!
    What about if I need to move only the first file, do some process and restart?

    ReplyDelete
  112. Hi Rafael,
    I am getting below error.. Please assist
    [File System Task] Error: An error occurred with the following error message: "Could not find file 'C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\pankajtxt.txtpankajtxt.txt'.".

    Progress: Operation Complete - 100 percent complete
    Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
    Task File System Task failed
    Finished, 3:09:52 PM, Elapsed time: 00:00:00.031
    Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
    Finished, 3:09:52 PM, Elapsed time: 00:00:00.063

    ReplyDelete

I will love to hear your feedback and comments. Thanks.