Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Newbie Expand / Collapse
Author
Message
Posted Wednesday, May 1, 2013 1:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 6:16 PM
Points: 3, Visits: 36
I was recently assigned a SSIS project for a client.

The requirements are;

Load data from 6 database tables into one excel sheet, while creating 4 new tabs for the new table data coming in. This excel sheet already has tabs for 2 tables, that's why only 4 more new tabs need to be created.
The database tables get updated on a nightly basis so the new data needs to be inserted into the excel sheet. This would also need to be automated, the client would have the updated data every morning when he opens the excel sheet.

Can someone please guide me in the right direction? I haven't used SSIS to an extent like this before.

More information can be provided if need be.

Thank you
Post #1448596
Posted Wednesday, May 1, 2013 5:53 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
syadnan2408 (5/1/2013)
I was recently assigned a SSIS project for a client.

The requirements are;

Load data from 6 database tables into one excel sheet, while creating 4 new tabs for the new table data coming in. This excel sheet already has tabs for 2 tables, that's why only 4 more new tabs need to be created.
The database tables get updated on a nightly basis so the new data needs to be inserted into the excel sheet. This would also need to be automated, the client would have the updated data every morning when he opens the excel sheet.

Can someone please guide me in the right direction? I haven't used SSIS to an extent like this before.

More information can be provided if need be.

Thank you


I've created a simple SSIS SQL-to-XL project and screen captured the results of each step in the attached PDF. This should get you started. (BTW, my version is 2008 so other versions may be slightly different.)

 


  Post Attachments 
SQL-TO_XL.pdf (24 views, 419.16 KB)
Post #1448653
Posted Wednesday, May 1, 2013 6:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 6:16 PM
Points: 3, Visits: 36
Thank you, I really appreciate the help.
Steven Willis (5/1/2013)
syadnan2408 (5/1/2013)
I was recently assigned a SSIS project for a client.

The requirements are;

Load data from 6 database tables into one excel sheet, while creating 4 new tabs for the new table data coming in. This excel sheet already has tabs for 2 tables, that's why only 4 more new tabs need to be created.
The database tables get updated on a nightly basis so the new data needs to be inserted into the excel sheet. This would also need to be automated, the client would have the updated data every morning when he opens the excel sheet.

Can someone please guide me in the right direction? I haven't used SSIS to an extent like this before.

More information can be provided if need be.

Thank you


I've created a simple SSIS SQL-to-XL project and screen captured the results of each step in the attached PDF. This should get you started. (BTW, my version is 2008 so other versions may be slightly different.)

 
Post #1448654
Posted Thursday, May 2, 2013 8:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 6:16 PM
Points: 3, Visits: 36
I've created most of the SSIS package but I'm having trouble with creating a variable.

The variable is for TableName. In the data flow I have an OLE DB Source, Data Conversion and Excel Destination. Now for the OLE DB Source, the data access mode I was going to use was SQL Command with Variable. I can't seem to create a SQL statement within my TableName variable to dynamically select tables from my database. I need to import data from 6 different tables from the same database.

How would I go about completing this?

Any suggestions would help!
Post #1448840
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse