February 7, 2013 at 7:04 pm
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.
February 7, 2013 at 7:45 pm
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
February 9, 2013 at 10:54 pm
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
February 11, 2013 at 7:53 am
No all the scripts have different number of columns and different column names. I want one Excel file with 30 tabs.
February 11, 2013 at 10:56 am
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
February 12, 2013 at 7:13 am
Good luck with this. Not the easiest project for starting off your SSIS journey.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 12, 2013 at 7:28 am
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.
February 12, 2013 at 7:41 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 12, 2013 at 7:48 am
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
February 12, 2013 at 7:53 am
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.
February 12, 2013 at 8:25 am
Not if you're using a Flat File Destination but you could go back and have a look at the solution-structure brad.mason5 posted earlier to get multiple csv files.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply