SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS - ForEachLoop Container performing task twice for the last file


SSIS - ForEachLoop Container performing task twice for the last file

Author
Message
//Ravi
//Ravi
Mr or Mrs. 500
Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)

Group: General Forum Members
Points: 594 Visits: 451
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?

------------
Smile
Attachments
FileRenamed.JPG (17 views, 26.00 KB)
ForEachLoopContainer.JPG (23 views, 63.00 KB)
ForEachLoopContainer2.JPG (21 views, 46.00 KB)
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62085 Visits: 13297
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 :-D


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
//Ravi
//Ravi
Mr or Mrs. 500
Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)

Group: General Forum Members
Points: 594 Visits: 451
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#?

------------
Smile
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62085 Visits: 13297
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search