September 26, 2013 at 2:27 pm
Okay, so I can't figure this out and it's driving me nuts. It's minor thing but I don't have any answer. Maybe some SSIS expert can shed some light.
Problem
I have a Foreach Loop container with file system task. File system task renames all the files in the folder; it basically adds date and time in front of the file name. For the last file in the folder, it adds date and time twice. See attached Image.
Here is how I have it setup
1. ForEach Loop Container: Under collection: Enumerator is "Foreach File Enumerator". Then my folder is populated. "Retrieve file name" is setup to be "Name Only". Then I assigned it to a variable. See attached image.
2. Setup a variable for the file location/folder.
3. Setup a variable to create date and time. Here is the code: (DT_STR, 4 , 1252)DATEPART( "year" , GETDATE() ) + "" + RIGHT( "00" + (DT_STR, 2 , 1252)DATEPART( "month" , GETDATE() ) , 2 ) + "" + RIGHT( "00" + (DT_STR, 2 , 1252)DATEPART( "day" , GETDATE() ) , 2 )+(DT_STR,2,1252)DATEPART("hh",GETDATE())+(DT_STR,2,1252)DATEPART("mi" ,GETDATE())+(DT_STR,2,1252)DATEPART("ss",GETDATE())
4. Set up a variable to create original file name with full path by using the path stated in step #2+#1.
5. Set up a variable to create new file name Using steps #2+#3+#1. Here is the code: @[User::FROIOriginalLocation] + @[User::FileNameDate]+ @[User::FileNamesForNameChange]+".txt"
What do you guys think is going on?
------------
🙂
September 27, 2013 at 12:29 am
The set-up seems to be OK.
You can use breakpoints to stop the loop at every iteration, so you can inspect the variables.
On a sidenote: this set-up takes only a few simple lines in .NET 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 27, 2013 at 5:34 am
Thanks. good idea for using the breakpoints. I think I'll do that and post my answer here later. I read up and there are other instances where file system task was executing two times for the last item.
I figured it would only take couple of lines .NET. I know SQL like back of my hand but I'm not a .NET programmer. It's on my agenda to learn .NET in next 1 year. Any good starting points/tutorials/books you can recommend to start learning .NET/C#?
------------
🙂
September 27, 2013 at 5:39 am
I'm no .NET developer myself, I just know the basics so I can script things in SSIS.
When I want to know something, I just google it and 90% of the time I end up at stackoverflow.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply