SSIS - Export multiple SQL query's to one excel spreadsheet
Hi All, I am using SSIS and I need to export 3 SQL query's to one excel spreadsheet. Each of the queries has a different number of columns and different column names. These 3 queries are actually a stored proc so i need to be able to run the proc and export the results to one spreadsheet. I also need to add a blank row after each query result. And each query result should also have the column headings included in the spreadsheet. I tried using an OLEDB source component and putting in the 3 queries and then selecting my excel dest spreadsheet. But this didnt work when i run this it only exports the 1st query not all 3. I am pretty sure this is possible in SSIS but cant figure it out. Any help would be appreciated!
April 28th, 2011 10:06am

I don't think you can copy all three results into single spreadsheet but you can copy the results into different spreadsheet in sinle excle file please check here https://support.quest.com/Search/SolutionDetail.aspx?id=SOL72948&category=Solutions&SKB=1http://uk.linkedin.com/in/ramjaddu
Free Windows Admin Tool Kit Click here and download it now
April 28th, 2011 10:14am

Hello, I Will suggest you to handle this in TSQL Query.As your columns are not changing dynamic so you can hard code things. Example. Your First Query SELECT COL1,Col2,Col3 from dbo.MyFirstTable UNION ALL SELECT '','','' --Adding Blank Row UNION ALL SELECT 'NAME','ID','Address' --Adding Heading SELECT NAME,ID,ADDRESS FROM dbo.MySECOND TABLE You will repeate the same steps for your third query. UNION ALL work with Eqaul number of Columns, so if you have more columns in Second or third query , then add a Black Column in your first Query to make then same. thankshttp://sqlage.blogspot.com/
April 28th, 2011 10:14am

I tried your suggestion Aamir but a union stmt creates one resultset and columns and data types have to be identical for this to work. My queries are not identical they each have different fields and different data types. My excel spreadsheet needs to have 3 separate sections for each query with the column headings included.
Free Windows Admin Tool Kit Click here and download it now
April 28th, 2011 10:41am

You can convert those columns to Varchar... if you want and write to Execl, This way you don't have to go for script task. The other option you have to use Script task. Here are the steps you need to follow 1-change your Stored procedure, so you can pass some parameter, Lets say DataSetNumber, So when you pass 1, it should return you first DataSet , if you pass 2 , it should return you second query results and so on. 2--Create a Object type variable in your package and ExcelLastRowNumber int32 3--use Execute SQL task , use your procedure and pass parameter , and save the result set into Object type variable(Full Result Set) 4-- use this object type variable in script task , write data to Execel file. Get the last row number into your ExcelLastRowNumber variable. 5--Next: you can use second Execute Sql task, pass parameter 2 and then save the result set into Object variable. Script task to write into excel , Use your ExcelLastRowNumber+1(Blank) , then write your dataset to Excel. And follow above 5th step for Third Query... You can use Foreach loop to pass the numbers to your procedure, if you like instead of using three Execute sql task and then three script task. That way you will be using only one Execute SQL task and one script task.\ links http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81979 ... you might need to install Excel assemblies that are required to write into Excel by using Script task. Full resultset, execute sql task http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx Thanks http://sqlage.blogspot.com/
April 28th, 2011 10:58am

I am not good with scripting. Any examples that i can use to accomplish this?
Free Windows Admin Tool Kit Click here and download it now
April 28th, 2011 11:21am

I am not good with scripting. Any examples that i can use to accomplish this? Any help??
April 28th, 2011 11:21am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics