Search code examples
sql-serverssisetlsql-server-data-toolsforeach-loop-container

For Each Loop SSIS. Dependent on SQL Query

  1. I have an SSIS package which checks for the unprocessed file present in a tracking table and then processed it. Till date only one file would come in and we would process it and as such the process was designed accordingly.

  2. However now multiple files can come in one go and we store those multiple files in the tracking table and we have a column which keeps a track of the unprocessed file.

I am trying to use the For Each loop to process all the unprocessed file. So I get the count of the unprocessed files and would like to simply tun the Point 1 by passing a parameter to the step 1 but I have not been successful in doing it using Foreach From Variable Enumerator. Am I missing something ?

Solution

  • You can do this using the following steps:

    1. Add an Execute SQL Task to get unprocessed files and store the resultset inside a variable of type System.Object
    2. Add a Foreach loop container, change the type to ADO enumerator and select the variable as source
    3. In the variable mapping tab map the result (each file path) to a variable of type string
    4. Inside the foreach loop container add a dataflow task that contains the Flat File source and implement the processing logic you need
    5. Add a flat file connection manager define the columns
    6. Click on the flat file connection manager, press F4 to show the property tab, go to expression.
    7. Select the connectionstring property and use the variable that holds the filepath as expression

    Detailed articles