Search code examples
sql-serverssisetlssis-2012flat-file

How can I execute an SSIS Package for every item in a Flat File?

I need to execute a package once for every item in flat file. The package's parameters will be a few of the current item's attributes. What is the best way to proceed? I'm assuming the last thing I do is place an Execute Package Task component inside of a Foreach Loop Container like below:

enter image description here

Solution

  • Each item had three attributes: username, password, and exactAccountName

    These were then used to execute an SSIS package with each login credential.

    In order to achieve this, do the following:

    1. Create a package variable of type Object in the master package.
    2. Add a script task that loop over flat file and convert it to a DataTable or List(of T) and store it into the Object variable. (This step is from Hadi's answer)
    3. Create a foreach loop to execute next.
    4. In the foreach loop container select the enumeration type to Ado enumerator and select the Object variable as Source. (Also from Hadi's answer)
    5. Put a Script Task and Execute Package Task in the foreach loop. Like so:enter image description here
    6. Create three string variables in the master package for username, password, and exactAccountName.
    7. Populate the main function of the Script Task with code like this:enter image description here
    8. Set the parameters of the child package to be the variables specified by the Script Task and you're done!