Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS - ForEachLoop Container performing task twice for the last file Expand / Collapse
Author
Message
Posted Thursday, September 26, 2013 2:27 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 12:38 PM
Points: 97, Visits: 359
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?


------------
:)


  Post Attachments 
FileRenamed.JPG (9 views, 26.65 KB)
ForEachLoopContainer.JPG (11 views, 63.38 KB)
ForEachLoopContainer2.JPG (11 views, 46.94 KB)
Post #1499058
Posted Friday, September 27, 2013 12:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:26 AM
Points: 13,622, Visits: 10,514
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




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

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1499204
Posted Friday, September 27, 2013 5:34 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 12:38 PM
Points: 97, Visits: 359
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#?


------------
:)
Post #1499322
Posted Friday, September 27, 2013 5:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:26 AM
Points: 13,622, Visits: 10,514
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1499323
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse