In a nutshell I have a list of keys. For each key I want to extract data from the db, output the data to an excel sheet, rename the sheet to the key value, create a new spreadsheet and loop through the next key. I am having trouble understanding the use of the destination file container for the spreadsheet. When I set up the connection to the excel destination I have to manually "create a new file" by clicking on the NEW button beside sheet name (A create table stmt opens, when I close this window my excel file is created). Why doesn't the container keep the information the the spreadsheet is a new file everytime. How do I create an empty new file for the connection within the loop?
Get next Key
I have 435 keys that need to result in 435 different excel files. Appreciate the great forum and your help! paws27284
asked Apr 11, 2011 at 12:40 PM in Default
Hi Paws, I can help but I strongly suggest that you don't use the excel destination for this. In my experience, using the flat file connection manager and using csv files is soooo much easier.
I create this simple table for this;
I'm not sure if you've been able to create the loop yet, so I'll help with this.
First of all, create 2 variables for your package, one to hold the list of keys, and one for a single key.
Now, on your control flow, add a data flow task. Then add a data source to get the list of keys (whatever datasource your need) I simply used a OLE DB datasource. Also add a Recordset Destination.
The query for my OLEDB destination is;
Then connect the source to the recordset destination and in the first TAB for variable name choose the User::keys variable, then on the Input Columns tab select the keyID input. Then that is done, we can now use the USER::keys object to loop through the keys.
Go Back to the control flow, and add a FOR EACH Loop container and change the Collection and variable Mappings Tab to look like the ones below
![alt text] ![alt text]
Now what you have is a loop of all your keys. Now to output to multiple csv file with the key as the file name. Add a data flow task inside the loop, and add a OLEDB source and Flat File destination.
For the source the query is;
Click on the parameters button and select USER::Key from the variables list. This assigns the key value variable to the query to return data pertaining to that key. Select the columns for output, and click OK.
Now connect the source to the flat file destination, Open the destination and click NEW for a new flat file connection manager. choose delimited and click OK, click on Browse then choose where you want to save the files. Now this is where using the flat file connection manager is much better than excel because with excel, the file has to exist for the package to run, but with the flat file it doesn't.
Change the details of the file as needed and click on columns to preview the file. Once it is OK, click OK and click on Mappings of the flat file destination and click OK.
Now we are going to modify the Flat file connection that you just created. Select the Flat File Connection you just made and press F4 to open the properties, look at the properties then you can see that the ConnectionString holds the information on the filename and location. So this property is the one we need to dynamically change.
Click on the Expressions ellipsis and enter the information as below; the expression is;
Warning; the expressions need to be converted to the correct output data type, because my key was a type int32, the expression above converts it to a string otherwise there would be an error.
That should be it, if you run the package now using the table I created, there should be 3 csv files.Good luck, and let us know if you need further assistance. : /upfiles/collection.JPG : /upfiles/variable.JPG : /upfiles/expression_editor.JPG
You might want to check out this post. http://www.rafael-salas.com/2008/03/ssis-and-dynamic-excel-destinations_01.htmlIn the past I have always copied the excel template file to a new dynamic filename for output and always left the original file as a reference.
answered Apr 11, 2011 at 08:44 PM