x

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

more ▼

asked Apr 11, 2011 at 12:40 PM in Default

paws27284 gravatar image

paws27284
373 26 30 34

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

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
more ▼

answered Apr 12, 2011 at 06:39 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 12 14

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Apr 11, 2011 at 08:44 PM

JustinKing gravatar image

JustinKing
87 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x926
x115
x18

asked: Apr 11, 2011 at 12:40 PM

Seen: 1552 times

Last Updated: May 26, 2013 at 02:33 AM