none
ssis multiple files in folder

    Question

  • HI plese Help me out

    i have multiple text files in 1 folder

    prashanth_1.txt

    naveen_1.txt

    prashanth_2.txt

    naveen_2.txt

    praveen_2.txt

    praveen_2.txt

     

    so i have to send into different destinations

    like

     

    prshanth (sql server destination)

    naveen

    praveen

     

    as sequentially so

    means

    prashanth_1.txt       naveen_1.txt       praveen_1.txt    

    prashanth_2.txt       naveen_2.txt       praveen_2.txt

     like sequentially i have to send data base so please help on this task

    i am new to ssis please help

    thanks


    prashanth
    Friday, December 23, 2011 11:30 PM

Answers

  • Need a few more details on this one.  Please advise:

    1. Since sequence matters, will you know when all files are delivered?  Or do you need a mechanism to check for all files and wait until all are there before determining sequence? 

    2. Are the 3 file types (prashanth_X, naveen_X, ...) exactly the same structure (columns, data types, etc)?  Are the target tables structured exactly the same?  Or are these completely different files with completely different targets?

    Regarding 1 above:
    If you do need a way to wait for all files to arrive before kicking off your sequential process, there are many options there.  One option is to use a separate package that has file watcher tasks that check the directory for the existence of the required files.  This package can run periodically and once all files are available, the file watcher tasks can fire an Execute Package Task that calls your main loader package.

    Regarding your requirement for correct sequential processing of the files, a bullet proof approach would be to gather the names of the files to be processed and explicitly sort the file names in the desired order.  One way to do this on the fly would be to load the file names into an object variable (via ForEachLoop container) and then use a script task to leverage the Sort method in C#.  Another approach would be to log your file names to a table, and then select them from the log into an object variable in SSIS in the order you want them processed.

    Regarding 2 above:
    if the file structure is exactly the same in all files and exactly the same in all 3 destinations, you can process the files with a ForEachLoop container and within each loop, dynamically set the connection string based on which file you are processing at that point.

    If the file structures and targets are different, you can still use the For Each Loop container to handle the files in the directory.  Just create a generic package that loads each unique file type and call an Execute Package Task within the main ForEachLoop container.  You can dynamically pass the loading package name that you need to call for that iteration of the loop like this:
    http://social.msdn.microsoft.com/Forums/en-AU/sqlintegrationservices/thread/DE336CA5-21B0-48AA-8646-D836BB0A84AE

    Please post additional details and we can fine tune the design accordingly.

     


    Brent Greenwood, MS, MCITP, CBIP
    /*************************************************
    --Please mark correct answers and helpful posts--
    *************************************************/
    http://brentgreenwood.blogspot.com
    Saturday, December 24, 2011 6:12 PM

All replies

  • Hi prashanth,

    so the other two destinations are just directories, right?

    Architecturally, it will be a ForEach Loop with a Precedence Constraint combo that will tackle your needs.

    See http://www.rafael-salas.com/2009/05/ssis-foreach-loop-looping-through.html


    Arthur My Blog

    Friday, December 23, 2011 11:46 PM
    Moderator
  • Need a few more details on this one.  Please advise:

    1. Since sequence matters, will you know when all files are delivered?  Or do you need a mechanism to check for all files and wait until all are there before determining sequence? 

    2. Are the 3 file types (prashanth_X, naveen_X, ...) exactly the same structure (columns, data types, etc)?  Are the target tables structured exactly the same?  Or are these completely different files with completely different targets?

    Regarding 1 above:
    If you do need a way to wait for all files to arrive before kicking off your sequential process, there are many options there.  One option is to use a separate package that has file watcher tasks that check the directory for the existence of the required files.  This package can run periodically and once all files are available, the file watcher tasks can fire an Execute Package Task that calls your main loader package.

    Regarding your requirement for correct sequential processing of the files, a bullet proof approach would be to gather the names of the files to be processed and explicitly sort the file names in the desired order.  One way to do this on the fly would be to load the file names into an object variable (via ForEachLoop container) and then use a script task to leverage the Sort method in C#.  Another approach would be to log your file names to a table, and then select them from the log into an object variable in SSIS in the order you want them processed.

    Regarding 2 above:
    if the file structure is exactly the same in all files and exactly the same in all 3 destinations, you can process the files with a ForEachLoop container and within each loop, dynamically set the connection string based on which file you are processing at that point.

    If the file structures and targets are different, you can still use the For Each Loop container to handle the files in the directory.  Just create a generic package that loads each unique file type and call an Execute Package Task within the main ForEachLoop container.  You can dynamically pass the loading package name that you need to call for that iteration of the loop like this:
    http://social.msdn.microsoft.com/Forums/en-AU/sqlintegrationservices/thread/DE336CA5-21B0-48AA-8646-D836BB0A84AE

    Please post additional details and we can fine tune the design accordingly.

     


    Brent Greenwood, MS, MCITP, CBIP
    /*************************************************
    --Please mark correct answers and helpful posts--
    *************************************************/
    http://brentgreenwood.blogspot.com
    Saturday, December 24, 2011 6:12 PM