2

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
Michael Drum
  • 1,151
  • 5
  • 14
  • 26

2 Answers2

1

Simple Solution

  1. First of all you have to create a Package variable of type Object
  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.
  3. Connect the Script Task to the foreach loop you are using
  4. In the foreach loop container select the enumeration type to Ado enumerator and select the Object variable as Source and map the columns you want to use in the execute package task to variables

Reading Flat File into DataTable

you can refer to one of the following links to learn more:

Side Note: If you need more details, give me a reply

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    That sounds like a great solution. The only thing that I need more detail on is how to create/populate a DataTable in the script (the code stuff). I should be able to do everything else. Thank you for your time. – Michael Drum Jul 14 '17 at 20:33
  • 1
    As a side note, my solution was going to be very ridiculous compared to that. I noticed that Foreach Loop Components can execute for every file in a directory so I was going to use a Script Component to make a file for every item with its properties inside. That uses way more space, processing power, and has much more potential to cause problems in the future. – Michael Drum Jul 14 '17 at 20:37
  • @MichaelDrum i provided many links that may helps you. take a look – Hadi Jul 14 '17 at 21:37
  • 2
    You can do this with out C#. Do step 1, then create a dataflow that reads the flatfile dataset into a record set (ADO object), then loop through that object in your foreach. – KeithL Jul 17 '17 at 16:51
  • @KeithL i agree with that – Hadi Jul 17 '17 at 17:18
  • 1
    And not sure why I got down voted on this, but this compares two options of what you are trying to do: https://stackoverflow.com/questions/45039351/feedback-requested-for-ssis-master-package-design-running-a-bunch-of-sub-packa – KeithL Jul 17 '17 at 17:28
  • I do not consider this an answer to my problem because you cannot pass objects as parameters to packages. – Michael Drum Aug 05 '17 at 02:30
  • Read the second step, object variable is used by script task, it is not passed as a parameter. – Hadi Aug 05 '17 at 06:34
  • I think that you misunderstood my answer. Anyway i am happy that my answer helped you to get your own. Good luck – Hadi Aug 05 '17 at 07:13
1

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!
Michael Drum
  • 1,151
  • 5
  • 14
  • 26