I am having an issue with the File System Task.
I was wondering if there is a way to 'Move File' with the File System Task inside of a For Each Loop container but to dynamically set the Destination path variable.
Currently, this is what I have:
FileDestinationPath variable - set to C:\TestFiles
FileSourcePath variable - set to C:\TestFiles
FileNameAndLocation variable - set to blank
For Each Loop Container – Iterates through a folder C:\TestFiles that has .txt files in it with dates in the file name. Ex: Test_09142006.txt. Sets the file path (fully qualified) to the Variable Mapping FileNameAndLocation.
Script Task (within For Each Loop, first step) – Sets the FileDestinationPath to the correct dated folder within C:\TestFiles. For example, if the text files I want to move are for the 14th of September, it takes FileDestinationPath and appends the date folder to the end of it. The text files have a date in the file name (test_09142006.txt) and I am picking this apart (from FileNameAndLocation in the For Each Loop) to get the folder date. (dts.Variables(“User::FileDestinationPath”).Value = dts.Variables(“User::FileDestinationPath”).Value & “\” Month & “_” & Day & “_” & Year & “\”) which gives me “C:\TestFiles\09_14_2006\”.
File System Task (within For Each Loop, second step) – This is where the action is supposed to occur. I want it to take the FileDestinationPath and move the FileNameAndLocation file (from the For Loop) into this folder for each run.
Now as for my problem. I want this package to run everyday but it has to set the FileDestinationPath variable dynamically according to that day’s date. Basically, how do I get this to work since I can’t hard code the destination path variable from the start? I have the DestinationVariable on the File System Task set to the FileDestinationPath variable, after the script task builds it. However, using FileNameAndLocation as the SourceVariable on my File System Task tells me that the “Variable “FileNameAndLocation” is used as a source or destination and is empty.”
Let me know if I need to clarify further…...I may be missing something very simple. Any help would be greatly appreciated!
Try 'harcoding' an initial value for FileDestinationPath variable. If my guess is correct, the designer is complain That value would be override by the scrip task result anyway.|||
Hi Rafael, thanks for the response. Unfortunately, that didn't work. What's strange is how I'm setting the FileNameAndLocation variable in the For Loop variable mapping but once it gets to the File System Task, it fails saying there is no valid path for the variable.
Do I even need to use a SourcePathVariable on the File System Task if I am simply moving files? I thought the Variable Mapping on the For Loop captures that for me, in the FileNameAndLocation variable?
I think I just confused myself.....
|||
 dlackey wrote: |
|
| Hi Rafael, thanks for the response. Unfortunately, that didn't work. What's strange is how I'm setting the FileNameAndLocation variable in the For Loop variable mapping but once it gets to the File System Task, it fails saying there is no valid path for the variable. Do I even need to use a SourcePathVariable on the File System Task if I am simply moving files? I thought the Variable Mapping on the For Loop captures that for me, in the FileNameAndLocation variable? I think I just confused myself..... |
Since you are moving multiple files (that is why you are using a ForEach loop container right?); you have to set IsDestinationPathVaribale and IsSourcePathVaribale to TRUE. Then you have to map DestinationVariable and SourceVariable properties to variables that contain both the path and the file name. I believe you are doing it right for the SourceVariable since you are using FileNameAndLocation which is populated by the ForEach loop container using ‘Fully qualified’ option. The problem, I am afraid, is in your DestinationVariable; if you use FileDestinationPath as you have it right now; it will give an error because is does not contain the file name. I would suggest to modify your script task to concatenate the file name as well.
Rafael Salas
|||
Thanks Rafael, I'll give it a try and let you know what I come up with.
Daniel Lackey
|||
I am working on the same thing at the moment. I have the File System Task setup with a DestinationConnection, then I am trying to use Expressions to change the connection string with a user variable. I have tried to use just the folder that I would like to change and the full path. I always seem to get the error that "The connection ... is not found" where the ... is the user variable. Any ideas would be appreciated.
Thanks,
TJ
|||
I just realized that I was using the expressions from the File System Task and not the expressions of the destination connection. I have it all working now. I am going to put in a few steps of how to do this since I had so much trouble finding information about it.
** I am assuming that everything is formatted for your export file and ready to export at this point.
1) Create a flat file destination.
2) In the properties of the Flat File Connection Manager (FFCM) (the items that show up in your connection managers tab) select the ... next to expressions
3) Select connection string on the left and click the ... on the right. In here you can build your file name.
4) If you are using a variable to change your file name, find your user variable in the tree on the left and drag it to the expression box. If you want something like a file name with the date with the file name then type what you want the file name to be (or use a variable) then add this for the year, month, and day (DT_WSTR,4) DATEPART("yy",GetDate()) + (DT_WSTR,2) DATEPART("mm",GetDate()) + (DT_WSTR,2) DATEPART("dd",GetDate()). Now click ok.
5) I would recommend that you test your package now and verify that your file name is now dynamic in the flat file connection folder.
6) Now to make the folder dynamic add a File System Task (FST) in the Control Flow.
7) Setup the FST with the FFCM that you created in the dataflow as the SourceConnection. Create a new FFCM for the DestinationConnection.
8) In the properties of the new FFCM select the ... next to the expressions.
9) In here you can change the folder location in the same way that you changed the file name.
I hope this saves a few people some time, because I had all sorts of trouble getting this to work. Please feel free to make any notes if I missed a step or if I made a mistake in one of the steps.
Good Luck,
TJ
|||
After days of staring at my package (haha, sounds funny) I figured out the problem. What was happening was my script that creates the destination folder that runs each time before the File System Task to move files, each time the For Each Loop iterates, I was adding the destination file to the DestinationFilePath variable each time. Hence, second iteration would result in 'C:\TestFiles\09_14_2006\09_14_2006' then third would be 'C:\TestFiles\09_14_2006\09_14_2006\09_14_2006' and so on and so on. Therefore, the File System Task would not find the destination file because it did not exist in the first place! ...resulting in my package failing. So as a result, I added a LoopCount to my script and and If statement so that the destination variable is only set on the first run through the loop.
So brief summary of what the package I built is doing (still in rough draft form by the way, but it finally at least works). I have 3 Variables in my project: DestinationFilePath set to C:\TestFiles, FileNameAndLocation set to C:\TestFiles, and LoopCount set to 1. The SSIS package contains a For Each Loop Container that contains a Script Task and a File System Task within it. The For Each Loop goes through my C:\TestFiles folder and enumerates through all files with .txt on the end of it. It sets the fully qualified name of the file to FileNameAndLocation variable in the Variable Mappings property for each iteration. Now, the Script Task builds the DestinationFilePath variable with this script:
Dim RightDate As String = Right(Dts.Variables("FileNameAndLocation").Value, 10)
Dim LeftDate As String = Left(RightDate, 4)
Dim Month As String = Left(LeftDate, 2)
Dim Day As String = Right(LeftDate, 2)
If Dts.Variables("LoopCount").Value = 1 Then
Dts.Variables("DestinationFilePath").Value = Dts.Variables("DestinationFilePath").Value & "\" & Month & "_" & Day & "_" & "2006\"
Dts.Variables("LoopCount").Value = 0
End If
The code is cheesy I know, but it works. I may refine it later. Especially for the year, since that can be different. But you get the point. So after that is set, I then on success move to the File System Task which sets the DestinationFilePath to the Destination Variable and the FileNameAndLocation as the Source Variable. It then moves the file and the For Each Loop moves on to the next one.
This solution works in that each day, the folder will contain that days .txt files. So the script will build a different DestiantionFilePath based upon the date in the file name.
Thanks for your input TJ, what you said will work too from what I can see. But since I was building the destination file name in a script task before the File System Task, I was able to avoid using any expressions or FFCMs.
Thanks for all of your help, I really do appreciate it!
Daniel
|||
Finally, with your post, I was able to get the dynamic file to update w/ current date.
Now how do I pass a variable from a column valuein a table to dynamically change the same filename?
My SQLCommand (select * from tableA) from datareadersource is connected to a flatfile destination. I need to pass
(product_order) from a column in this table-store the result in variable (prodID) and pass on as new filename in file system task.
How to do?
