none
SSIS question - loop through values in textfile

    Question

  • I am new to SSIS and am trying to create SSIS package.

    I have value per line in a text file. There is a table consisting of records with each of the values in the text file. The SSIS package should generate text files for each of the value in the text file with the data from the table.

    Details:

    Text File – abcd.txt

    10

    11

    12

    13

    Table

    1

    10

    2

    11

    3

    12

    4

    13

    5

    10

    6

    11

    7

    12

    8

    13

    9

    10

    The SSIS package should create the following text files with the records that are associated with 10, 11,12 and 13:

    10.txt

    11.txt

    12.txt

    13.txt

    Thanks in advance



    Thursday, July 17, 2014 10:24 PM

Answers

  • Add a ForEach loop container to your SSIS package and configure it such that it reads the concents from the text file which has the numbers you want to create output files for. Also, set it up such that each value read from the flat file is mapped to a variable (call it varFileNumber). 

    Inside the loop container, add a DataFlow task to your SSIS package. Double click the DataFlow task and add a OLEDB source component to it. Set it up such that it points to your SQL table that has the actual data you want to read. Use the "Data from a SQL query" option while configuring it and use the below query to act as your source:

    SELECT * FROM table WHERE colID = @FileNumber.

    Go to the Parameter Mapping tab of the OLED Source component and map the @FileNumber to the local variable varFileNumber yo'd created while setting up the loop container.

    Finally, drag and drop a Flat File Destination component and map it to a flat file on disk (say in C:\ directory). This will create a Flat File Connection Manager in your SSIS package. Set the "FileName" property of this connection manager by using "Expression" option to:-

    "C:\" + (DT_STR)varFileNumber + ".txt"

    Note: you can also dynamically set the folder where these files should get created as well by using Package configurations.

    When you run this package, the loop will run as many times as there are numbers in your first file, filtering the data from SQL table for that particular number each time it runs and will create one file each for the numbers in the first file. 

    I hope this helps!

    Cheers!

    Muqadder.

    Thursday, July 17, 2014 11:51 PM
  • The package looks like below

    1. Add a data flow task to get contents from file onto a staging table (flat file source & oledb destination)

    2. Execute SQL task to populate object variable with ID list from staging table

    add a object variable in SSIS (@[User::IDList]), use query as below in execute sql task

    SELECT ID FROM STagingTable

    Set resultset option as FullResultSet and map index 0 to IDList variable in resultset tab

    3. Add a ForEachLoop with ADO enumerator and map to IDList variable. Add another int variable ID to receive iterative values

    4. Inside Loop add a data flow task with OLEDB Source pointing to table.

    use query as

    SELECT *
    FROM Table
    WHERE IDField = ?

    map parameter0 to [User::ID] variable in parameter mapping

    link to flat file destination and set connectionstring property to expression like below

    "<your file folder path>\" + (DT_STR,10,1252) @[User::ID] + ".txt"

    to make filename dynamic

    execute the package and you'll get different files created for each ID value with related data

    similar logic is also explained here with only different being data going to different tabs of excel

    http://visakhm.blogspot.in/2013/09/exporting-sqlserver-data-to-multiple.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, July 18, 2014 2:38 AM

All replies

  • Add a ForEach loop container to your SSIS package and configure it such that it reads the concents from the text file which has the numbers you want to create output files for. Also, set it up such that each value read from the flat file is mapped to a variable (call it varFileNumber). 

    Inside the loop container, add a DataFlow task to your SSIS package. Double click the DataFlow task and add a OLEDB source component to it. Set it up such that it points to your SQL table that has the actual data you want to read. Use the "Data from a SQL query" option while configuring it and use the below query to act as your source:

    SELECT * FROM table WHERE colID = @FileNumber.

    Go to the Parameter Mapping tab of the OLED Source component and map the @FileNumber to the local variable varFileNumber yo'd created while setting up the loop container.

    Finally, drag and drop a Flat File Destination component and map it to a flat file on disk (say in C:\ directory). This will create a Flat File Connection Manager in your SSIS package. Set the "FileName" property of this connection manager by using "Expression" option to:-

    "C:\" + (DT_STR)varFileNumber + ".txt"

    Note: you can also dynamically set the folder where these files should get created as well by using Package configurations.

    When you run this package, the loop will run as many times as there are numbers in your first file, filtering the data from SQL table for that particular number each time it runs and will create one file each for the numbers in the first file. 

    I hope this helps!

    Cheers!

    Muqadder.

    Thursday, July 17, 2014 11:51 PM
  • The package looks like below

    1. Add a data flow task to get contents from file onto a staging table (flat file source & oledb destination)

    2. Execute SQL task to populate object variable with ID list from staging table

    add a object variable in SSIS (@[User::IDList]), use query as below in execute sql task

    SELECT ID FROM STagingTable

    Set resultset option as FullResultSet and map index 0 to IDList variable in resultset tab

    3. Add a ForEachLoop with ADO enumerator and map to IDList variable. Add another int variable ID to receive iterative values

    4. Inside Loop add a data flow task with OLEDB Source pointing to table.

    use query as

    SELECT *
    FROM Table
    WHERE IDField = ?

    map parameter0 to [User::ID] variable in parameter mapping

    link to flat file destination and set connectionstring property to expression like below

    "<your file folder path>\" + (DT_STR,10,1252) @[User::ID] + ".txt"

    to make filename dynamic

    execute the package and you'll get different files created for each ID value with related data

    similar logic is also explained here with only different being data going to different tabs of excel

    http://visakhm.blogspot.in/2013/09/exporting-sqlserver-data-to-multiple.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, July 18, 2014 2:38 AM