SSIS - ForEachLoop Container performing task twice for the last file

  • 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?

    ------------
    🙂

  • 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

  • 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#?

    ------------
    🙂

  • 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