Help with multiple excel output files

You may work with 2 or more excel files by using of excel recovery

This text is about various sides of working with excel files

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

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.