locked
SSIS Conditional foreach loop task RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi, I am using SSDTBI 2013 and SQL Server 2014. We are working on pulling data from flat files from a third party application and these files reside on vendor's shared drive and this is a weekly job and there will be 1 file each for a day. For instance week of 03/06 would have files like ThirdPartyAppFile_03062016, ThirdPartyAppFile_03072016,..............ThirdPartyAppFile_03122016. When we process the file in SSIS we store the name of the file in a table. They don't delete these files for 90 days which is their file retention policy. Now when we run our package once every week we would want to import only those files which weren't already processed, when we loop through the files in foreach loop container. when the job runs on 03/12/2016 it should only load files from 03/06/2012 - 03/12/2016. How do we accomplish this.

    Thanks very much in advance...............

    SQLEnthusiast

    Monday, March 7, 2016 9:22 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    You need to store the file process list in SQL table;

    On every subsequent run   you need to do comparison of the Prcoessed set vs already processed set on every run.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c5a29317-d83d-4249-8bc8-14c02a3c3cfe/ssis-package-load-only-csv-files-that-were-not-loaded?forum=sqlintegrationservices

    https://blogs.msdn.microsoft.com/luti/2008/07/11/ssis-by-example-1-processing-files-in-folder/

    Regards,

    Navin

    Navin.D http://dnavin.wordpress.com



    • Edited by Navind Monday, March 7, 2016 10:08 PM
    • Proposed as answer by Eric__Zhang Tuesday, March 8, 2016 7:49 AM
    • Marked as answer by CSharp Enthusiast Tuesday, March 8, 2016 4:36 PM
    Monday, March 7, 2016 10:01 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    You need to store the file process list in SQL table;

    On every subsequent run   you need to do comparison of the Prcoessed set vs already processed set on every run.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c5a29317-d83d-4249-8bc8-14c02a3c3cfe/ssis-package-load-only-csv-files-that-were-not-loaded?forum=sqlintegrationservices

    https://blogs.msdn.microsoft.com/luti/2008/07/11/ssis-by-example-1-processing-files-in-folder/

    Regards,

    Navin

    Navin.D http://dnavin.wordpress.com



    • Edited by Navind Monday, March 7, 2016 10:08 PM
    • Proposed as answer by Eric__Zhang Tuesday, March 8, 2016 7:49 AM
    • Marked as answer by CSharp Enthusiast Tuesday, March 8, 2016 4:36 PM
    Monday, March 7, 2016 10:01 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    A simple two column table to manage week spans and process status should be used. This table could reside on your server or the one where the files are processed.

    Cheers!! Sumit Bhatnagar

    Tuesday, March 8, 2016 5:38 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi SQLEnthusiast,

    You could use a For loop container to loop the files one by one. Check Dynamic Flat File Connections

    Eric Zhang
    TechNet Community Support

    Tuesday, March 8, 2016 8:49 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks all for your response.

    SQLEnthusiast

    Tuesday, March 8, 2016 4:36 PM