none
Loop over multiple text (CSV) files in a directory

    Question

  • Environment: SSIS 2008

    Problem: Need to dynamically iterate through each text (CSV) file in a directory and load the contents of each file into a separate SQL Server table with a different structure

    Attempted solution: Use ForEachLoop container. Inside the container use a dummy ScriptTask and multiple DataFlowTasks for each flat file - table combination. Can define only one precedence constraint for one combiantion. Attempting to define other precedence constraints fail with the From source pointing to the container boundary instead of the ScriptTask

     

    Tuesday, October 25, 2011 2:36 AM

Answers

  • What i would do is first

    make 6 pakages that have with 6 source and 6 destination , as you had mentioned the source is a CSV file so make the SOURCE FOLDER different for each ETL package

    1- As you know that CSV columns can not be moved/relocated or in other words displaced 

    2- make a 7th package that reads each file one by one and moves it to the right folder

    3- how?

    4- read each file one by one , read only the first row (or youcan redirect the first row), remeber to read the file in ONE SOURCE column

    http://sudheer-bandaru.blogspot.com/2011/05/importing-poorly-formatted-text-files.html

    5- now by having a table that has the package folder and the column list

    ETL name  ,  FolderToMoveTo                                 , column names

    Pkg 1       , C:\ETLPkgsRoot\Pkg1\TobeProcessed    , "Col1,Col2,col3"

    Pkg 2       , C:\ETLPkgsRoot\Pkg2\TobeProcessed     , "Col1,Col2,col3,COL5,COL6,COL7,COL8,COL9"

    Pkg 2       , C:\ETLPkgsRoot\Pkg3\TobeProcessed     , "ColABC1,ColXYZ2"

    6- you can identify what file you have picked and redirec the file to the right folder

     


    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Monday, October 31, 2011 8:08 PM

All replies

  • Should add that I also downloaded the FileWatcherTask and tried to use it in place of the ScriptTask but ran into the same problem with the precedence constraints.
    Tuesday, October 25, 2011 2:54 AM
  • As per my understanding, You want to transfer the data from a common file to many SQL Server table which are of different structure.

    For a single Flat File Source in the DFT

    - use a multicast transformation to get multiple output

    - map the OLEDB destination table to the required columns in the source 

    For all the source files, you have to use that many DFT since the structure of the source and destination columns can differ.

    * Why are you using a FOREACH loop

    • Marked as answer by Eileen Zhao Monday, October 31, 2011 1:48 AM
    • Unmarked as answer by zonts Monday, October 31, 2011 3:54 PM
    Tuesday, October 25, 2011 5:16 AM
  • Hi,

    Thanks for your reply.

    A ForEachLoop Container (FLC) is needed because there are DIFFERENT Source flat files each of which has to load a DIFFERENT Sql Server Destination table. In other words, if there are 6 pairs of source --> destination, then 6 diferent DataFlowTasks (DFTs) are needed. Each DFT has to have a distinct Precedence Constraint emanating from the (dummy) ScriptTask within the FLC.

    For your ready reference, I used the solution suggested in this thread:

    http://social.msdn.microsoft.com/Forums/en-us/sqlintegrationservices/thread/110a8e79-a7f0-482c-b229-e4a6573b38f1

    - jps

    Monday, October 31, 2011 4:19 PM
  • What i would do is first

    make 6 pakages that have with 6 source and 6 destination , as you had mentioned the source is a CSV file so make the SOURCE FOLDER different for each ETL package

    1- As you know that CSV columns can not be moved/relocated or in other words displaced 

    2- make a 7th package that reads each file one by one and moves it to the right folder

    3- how?

    4- read each file one by one , read only the first row (or youcan redirect the first row), remeber to read the file in ONE SOURCE column

    http://sudheer-bandaru.blogspot.com/2011/05/importing-poorly-formatted-text-files.html

    5- now by having a table that has the package folder and the column list

    ETL name  ,  FolderToMoveTo                                 , column names

    Pkg 1       , C:\ETLPkgsRoot\Pkg1\TobeProcessed    , "Col1,Col2,col3"

    Pkg 2       , C:\ETLPkgsRoot\Pkg2\TobeProcessed     , "Col1,Col2,col3,COL5,COL6,COL7,COL8,COL9"

    Pkg 2       , C:\ETLPkgsRoot\Pkg3\TobeProcessed     , "ColABC1,ColXYZ2"

    6- you can identify what file you have picked and redirec the file to the right folder

     


    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Monday, October 31, 2011 8:08 PM
  • Environment: SSIS 2008

    Problem: Need to dynamically iterate through each text (CSV) file in a directory and load the contents of each file into a separate SQL Server table with a different structure

    Attempted solution: Use ForEachLoop container. Inside the container use a dummy ScriptTask and multiple DataFlowTasks for each flat file - table combination. Can define only one precedence constraint for one combiantion. Attempting to define other precedence constraints fail with the From source pointing to the container boundary instead of the ScriptTask

     

    If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus. It is an extension of the standard Data Flow Task with support for dynamic columns at runtime. You can accomplish the requirement described aboe with only one data flow. No programming skills are required.
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    Tuesday, November 01, 2011 8:24 PM