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 12»»

Output multiple SQL query result into excel using SSIS Expand / Collapse
Author
Message
Posted Thursday, February 7, 2013 7:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:46 AM
Points: 8, Visits: 31
Hi All,

I am new to SSIS and am currently working on a requirement as follows:

We have 30 sql scripts which queries various tables and gives the errors for each packages we have. We manually copy the script outputs to separate tabs in an excel sheet and send it to our clients. I am working on automating this using SSIS. So right now I have created a package which runs one sql script and writes to excel file tab. So to output 30 script results do I have to implement the above solution 30 times or is there any optimum way of implementing them?

Any suggestions would be welcome.

Thanks,
Athi.
Post #1417425
Posted Thursday, February 7, 2013 7:45 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:50 PM
Points: 1,069, Visits: 1,232
Some ideas to help.

For Each Loop Container and through folder of sql scripts picking up the sql file name and using this as a variable. Use a Command Line task using SQLCMD command. Export the results to csv.

1. Pull over For Each Loop Container
2. Locate the folder location with the 30 sql scripts
3. Use this location in FEL (For Each Loop). Store FileName in Variable
4. Pull over Process Task and use sqlcmd using Variable as parameter to execute sql statement

sqlcmd -Sservername -d myDB -E -i D:\SQL\Script1.sql -o "MyData.csv"

See http://www.sqlservercentral.com/Forums/Topic799839-324-1.aspx#bm897838

You will need to make the output variable as well. Probably use filename in FEL.

This would output 30 csv files with data from sql scripts. I know you wanted excel but this could help guide you to your goals.


Another idea is to use RecordSet Destination which looks interesting as well.
http://consultingblogs.emc.com/jamiethomson/archive/2005/05/30/1489.aspx
Post #1417430
Posted Saturday, February 9, 2013 10:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
Do the 30 scripts deliver the exact same shaped resultsets, e.g. exact same number and type of columns, or are there some variations from one to the other?

Do you want one Excel file with 30 tabs or 30 separate files?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1418078
Posted Monday, February 11, 2013 7:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:46 AM
Points: 8, Visits: 31
No all the scripts have different number of columns and different column names. I want one Excel file with 30 tabs.
Post #1418444
Posted Monday, February 11, 2013 10:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
You will need 30 different Source and Destination combinations in your SSIS Package. Given how Excel work and the fact that concurrent access is not a good idea I would recommend either having 30 different Data Flow Tasks that run sequentially to load the 30 tabs, or having one Data Flow with 30 Sources writing to 30 Destinations but again all in sequence so only one thing writes to the Excel file at a time.

As for creating your Excel file, you can create that dynamically each time the package runs. I do not remember the exact technique offhand but it has to do with issuing a CREATE TABLE statement to the Excel Connection via an Execute SQL Task which creates the Workbook initially and one tab. You would then need to issue another 29 CREATE TABLE statements to get to your 30 tabs.

Another option is to create a "template" Workbook, one that already has 30 empty tabs setup how you like. You would then make a copy of this "template" and load data into that and ship that to your customers.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1418569
Posted Tuesday, February 12, 2013 7:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 5,317, Visits: 12,353
Good luck with this. Not the easiest project for starting off your SSIS journey.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1418950
Posted Tuesday, February 12, 2013 7:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:46 AM
Points: 8, Visits: 31
I have created a sample package for the first 5 scripts having 5 different sources,dataflow task and destination. I wanted to know if there is any other simple way of implementing it other than having 30 sources and destinations. I would try out the foreach loop technique as suggesting in the first reply.

Thank you for all your suggestions and help.
Post #1418961
Posted Tuesday, February 12, 2013 7:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 5,317, Visits: 12,353
athi_ssn (2/12/2013)
I have created a sample package for the first 5 scripts having 5 different sources,dataflow task and destination. I wanted to know if there is any other simple way of implementing it other than having 30 sources and destinations. I would try out the foreach loop technique as suggesting in the first reply.

Thank you for all your suggestions and help.


Nothing simple, I'm afraid.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1418970
Posted Tuesday, February 12, 2013 7:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
athi_ssn (2/12/2013)
I have created a sample package for the first 5 scripts having 5 different sources,dataflow task and destination. I wanted to know if there is any other simple way of implementing it other than having 30 sources and destinations. I would try out the foreach loop technique as suggesting in the first reply.

Thank you for all your suggestions and help.

Sorry, no easy way. That's why I asked about the shape of the resultsets. If some were the same then you could have used a loop for those but an OLE DB Destination can only handle one type of resultset.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1418974
Posted Tuesday, February 12, 2013 7:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:46 AM
Points: 8, Visits: 31
If I split my scripts into 30 different files(1 per file) and use foreach loop container, would it work? Would it work? Can the data flow task grab query from the file?

Please advice.
Post #1418977
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse