ssis multiple files in folder
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 thanksprashanth
December 23rd, 2011 6:32pm

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
Free Windows Admin Tool Kit Click here and download it now
December 23rd, 2011 6:48pm

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
December 24th, 2011 1:14pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics