I'm a novice with a for each loop. I'm looking to create .csv files for each invoice in a table and then assign each .csv the appropriate invoice number from a table. From what I understand I will need to create a data flow task within the for each loop and output a .csv file. I'm having trouble with the looping for a variable per invoice and rename the .csv to that invoice. I greatly appreciate any help.
1 Answer
In summary, you need to save your entire dataset containing a list of invoice numbers as an object, then create a container to loop over that dataset and populate one or more simple variables (e.g. ints, strings) from each row. Any tasks you add inside the container will execute once per row and can use the values stored in the variables. In this case, because you want the output filename to derive from the dataset, you need to use global variables and a connection manager with an expression based upon those variables.
Suggest the following steps:
- Create a variable of type Object e.g. @InvoiceResults
- Create a variable to contain the invoice number e.g. @InvoiceNumber. This variable needs to be created globally (i.e. not within a specific task) because your flat file connection manager will use it.
- Create another global string variable to contain the filename e.g. @InvoiceFileName. Set the expression to build the filename from the @InvoiceNumber variable e.g. 001 -> c:\Invoice_001.csv.
- Create a File connection manager. Hit F4 to bring up the properties screen and set the FileName expression to the @InvoiceFileName variable.
- Create an Execute SQL task to retrieve the invoice numbers.Choose the Full Result Set output type, then swap to the Result Set tab and specify the object variable (e.g. @InvoiceResults). This task will execute the query and store the results in the object to be used later.
- Create a Foreach Loop Container to run after the Execute SQL Task. On the Collections tab, Choose the Foreach ADO Enumerator and specify the object variable e.g. @InvoiceResults. On the Variable Mappings tab, add the @InvoiceNumber variable. This tells the container to iterate over the contents of the object and populate this variable for each row. Any tasks in this container will now run once per row and can use the variable to obtain the invoice number.
- Create a data flow task in the container. Presumably you need a data source to retrieve the data for a single invoice, using @InvoiceNumber as a parameter, then send this to a flat file destination using the connection manager you created above. Because the flat file destination uses the connection manager from inside the foreach container, the connection should be pointing to the filename derived from the invoice number.
There is a tutorial with pictures here (not made by me).
