question

paws27284 avatar image
paws27284 asked

Help with multiple excel output files

Hi, 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? ForEach 1.Get variable from loop and run SQL statement into temp table 2.Load temp table into excel file "data.xls" (This data.xls file exists because I have to hit the new before executing the package or I get file does not exist.) 3.Rename "data.xls" to "key1.xls" 4.Delete rows from Temp Table 5. ?Tried to create new "data.xls" file ??? Get next Key I have 435 keys that need to result in 435 different excel files. Appreciate the great forum and your help! paws27284
ssisexceloutput
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JustinKing avatar image
JustinKing answered
You might want to check out this post. http://www.rafael-salas.com/2008/03/ssis-and-dynamic-excel-destinations_01.html In 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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Daniel Ross avatar image
Daniel Ross answered
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; CREATE TABLE KEYS(keyID INT,keyValue NVARCHAR(50)) GO INSERT INTO KEYS SELECT 1,'key value 1' UNION SELECT 2,'key value 2' union select 3,'KEY VALUE 3' 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. Name Scope Data Type Value key "Package" Int32 0 keys "Package" Object default value 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; select distinct(keyID) as keyID from keys 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][1] ![alt text][2] 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; select keyid,keyvalue from keys where keyid=? 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; "D:\\testSSIS\\" + (DT_WSTR, 10) @[User::key] + ".csv" ![alt text][3] 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. [1]: /upfiles/collection.JPG [2]: /upfiles/variable.JPG [3]: /upfiles/expression_editor.JPG
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.