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:
nice example...but I don't see how you got the expressions tied to the variables..I must be missing something
ReplyDeleteYou certanly are. See the section bellow ' The expressions are:'...
ReplyDeletehow can we input expressions with variables?
ReplyDeleteI 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.
ReplyDeleteThank you.
ReplyDeleteNow 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
This comment has been removed by the author.
ReplyDeletedo 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?
ReplyDeleteMake 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.
ReplyDeletei'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.
ReplyDeletedoes your post will help me i'm not sure...???
10X.
This comment has been removed by a blog administrator.
ReplyDeleteGreat 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?
ReplyDeleteRafael,
ReplyDeleteI 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.
Gil,
ReplyDeleteno, 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.
Chris,
ReplyDeleteYou 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.
ctruett3,
ReplyDeletePackage 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.
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?
ReplyDeleteNever 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.
ReplyDeleteHow 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?
ReplyDeleteRafael,
ReplyDeleteas 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
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:
ReplyDelete@[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] ) )
okay, let me try one more time. the colons between the hours min and sec didn't go over well:)
ReplyDelete@[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] ) )
Along with the time stamp i want the date part as well. Something like:
ReplyDeleteDDMMYYYY HH:MI:SS
Can someone help me out with the correct expression please?
panks21,
ReplyDeleteColon is not allowed for a file name. try using another delimiter like '-'...
This is really very good.....
ReplyDeleteI 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] ) )
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.
ReplyDeleteThis also speeds up package loading in the Designer.
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?
ReplyDeletehi, 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.".
ReplyDeleteSoham,
ReplyDeleteTry 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.
please give valid daymonthyear & time stamp for filesrchivepath.
ReplyDeleteThanks 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.
ReplyDeleteAny 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).
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?
ReplyDeleteThanks for your help. That's exactly what I was looking for... You save me a lot of time ;-)
ReplyDeleteThis 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.
ReplyDeleteThank you, Just what I needed. easy enough for a first time SSIS experience, in depth enough to solve my problem
ReplyDeleteJustin,
ReplyDeleteThanks 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.
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\
ReplyDeleteAnyone got any ideas?
Beautiful! Thank you!!!
ReplyDeleteHi Rafael,
ReplyDeleteThanks 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
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?
ReplyDeletehii rafael nice post really helped me '
ReplyDeletei 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
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?
ReplyDeletethis is all very nice, and it is so nice to see that there is a workaround to every problem but.........
ReplyDeleteam 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
Nice Example.. Thanks..
ReplyDeleteSimilar 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
Casper,
ReplyDeleteAppending 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).
Karthik,
ReplyDeleteThese 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.
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
ReplyDeleteanjali,
ReplyDeleteThe 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.
Hi Rafael,
ReplyDeleteI 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?
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:
ReplyDelete[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?
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:
ReplyDelete[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.
Rafeal - great example. I was able to move and rename the files just as I wanted. Thanks a lot.
ReplyDeleteHi Rafael - great example. I was able to move and rename the files in archive folder just as I wanted. Thank you very much.
ReplyDeleteRafael,
ReplyDeleteThank you for this example, worked great for me.
Best regards,
Z
Rafael -
ReplyDeleteI 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.".
Worked like a charm. Thanks for adding the download too, made things a lot easier.
ReplyDeleteCheers,
Kristy.
Rafael -
ReplyDeleteI 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.".
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"
ReplyDeleteI need to copy one excel file from a folder and paste it in two different folders. How this can be achieved?
ReplyDeleteHi Rafael,
ReplyDeleteI 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
I've having the same issue as Sonya did. Can't figure out why it's duping the name.
ReplyDeletectaylor & Shairal,
ReplyDeleteI 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.
Thanks for the good example, it allowed me to move files from within a Foreach Loop Container.
ReplyDelete--Mark
Hi Rafel Salas
ReplyDeleteI 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.
Hey Rafael,
ReplyDeleteI Like your Post that solved my problem and i learned something out of it as well.
Thank You Very much.
GOD BLESS YOU
Hey Rafael,
ReplyDeleteI like your post.
It helped solve my problem and i learned out of it as well.
Thank You Very much
GOD BLESS YOU
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.
ReplyDeletehi,
ReplyDeleteI 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
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
ReplyDeletefor 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?
thanks for help and a nice article.
ReplyDeleteAmit Patel
Thanks for your help and very nice article.
ReplyDeleteAmit Patel
I am having a similar issue as described on Sonia's post,the timestamp and extension are added twice.
ReplyDeleteAt 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
Very nice and useful example. Illustrates the use of Foreeach Loop Container, Variables and expressions and File System Task. Thanks a lot!
ReplyDeleteHi Rafael,
ReplyDeletethis is simply superb.. u really rock...!
the File System Task is buggy, it wil log that your destination file is deleted instead of your source file:
ReplyDeleteInformation: 0xC002F30E at Backup datafile, File System Task: File or directory "c:\temp\backup\20100519_detail.csv" was deleted.
Hola Rafael,
ReplyDeleteI 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!
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.
ReplyDeleteCould 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).
Hello Rafael, I am new with SSIS and I have a problem with a package that I'm creating.
ReplyDeleteI 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.
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...)
ReplyDeleteThank you for the excellent post
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?
ReplyDeleteKevin,
ReplyDeleteLikely, 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.
hi Rafael sals,
ReplyDeleteif 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
Wondering if there was any response to Dave's issue above? I am having the same issue.
ReplyDeleteHi Rafael
ReplyDeleteI 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
hi friends.
ReplyDeletepls 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
Jean Here.
ReplyDeleteThis IS a Good Post as well.
This helped me about 1 year ago.
Thanks Man..
vinothlilly,
ReplyDeleteYour 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.
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?
ReplyDeleteAny good solution for archiving files in this manner?
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..
ReplyDeleteThanks Rafael...It is really a nice post..
ReplyDeletecan you help me on this...Is there any way that i can create and rename the file at the same time...
Thanks...
Hi,
ReplyDeleteYour example works fine for me. But i want keep source files, i want copy not move? why i can do this?
I understand the rename part of this, but can't see where the 'move' part is occurring. :(
ReplyDeleteGreat example even for a newbe like me. Keep your good work!
ReplyDeleteHi Rafael,
ReplyDeleteMy 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
Great example - thanks!
ReplyDeleteHi,
ReplyDeleteI 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.
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.
ReplyDeleteWorks like charm.....thanks
ReplyDeleteThanks 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?
ReplyDeleteHi 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.
ReplyDeleteThe connection managers as they appear in the bottom part of the editor are tie to package you have open in the active tab.
ReplyDeleteI 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
ReplyDeleteThis example works for Copy File, but exact same package fails if i change it to Move File. Please advice.
ReplyDeleteHi Rafael,
ReplyDeleteI 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.
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
ReplyDeleteThis was a very good, helpful, and concise post concerning a common scenario.
ReplyDeleteI 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] ) )
Hi Rafael,
ReplyDeleteThanks 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 ?
Hi,
ReplyDeleteThis 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....
Thank you for this recipe.
ReplyDeleteI 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.
Thanks Rafael.
ReplyDeleteThank you for the nice post helped me in completing one of my complex SSIS package.
ReplyDeleteThank you!
ReplyDeleteWhat about if I need to move only the first file, do some process and restart?
Hi Rafael,
ReplyDeleteI 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
I get the same error when I execute the container, but the file system task works fine.
DeleteThis example assumes WAAY too much previous knowledge, hidden configurations and there's no downloadable sample to make it any use of this tutorial.
ReplyDeleteThis aches like instructions on how to land a Boeing plane:
1. Learn how to fly one by yourself.
2. "Just" click on the red button on your right console for automatic landing. That's it!
I haven't ever used SSIS until a couple days ago, and I figured this tutorial out in about 5 minutes.
Deleteif this is to hard for you, maybe look for another job....
ReplyDelete