export store procedure output to Excel using SSIS 2005
Hi I have store procedure, which has some dynamic queries and finally using th EXEC i am executing the all the queries so that it can produce one output. Now I want to export this data into Excel file. I think the best way to achive this would be SSIS. can any one help me to to achive this. How can i pass the store procedure output directly to Excel file using SSIS package
August 19th, 2010 11:38pm

I would use BCP to execute the stored procedure and have that output to a CSV file. You can then open the CSV file in Excel. http://msdn.microsoft.com/en-us/library/ms162802.aspxhttp://jahaines.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
August 19th, 2010 11:54pm

Hi contactme, In order to export the output from a stored procedure to Excel using SQL Server Intelligence Services(SSIS), we can following these steps: Create a SSIS project Drag a Data Flow Task(DFT) to the Control Flow page Double-click the Data Flow Task Drag a ADO NET Souce and a Excel Destination to the DFT Double-click the ADO NET Source, configure the connection, and type 'Exec <Stored Procedure>' in the 'SQL command text', click 'OK' to apply Connect the source control with the Excel destination Double-click the Excel Destination, configure the connection, set the mappings, and then click 'OK' to apply Now, we can execute the package to test the exporting. For more information, please see: Excel Destination: http://msdn.microsoft.com/en-us/library/ms137643.aspx If you have any more questions, please feel free to ask. Thanks, Jin ChenJin Chen - MSFT
August 20th, 2010 11:43am

Hi contactme, In order to export the output from a stored procedure to Excel using SQL Server Intelligence Services(SSIS), we can following these steps: Create a SSIS project Drag a Data Flow Task(DFT) to the Control Flow page Double-click the Data Flow Task Drag a ADO NET Souce and a Excel Destination to the DFT Double-click the ADO NET Source, configure the connection, and type 'Exec <Stored Procedure>' in the 'SQL command text', click 'OK' to apply Connect the source control with the Excel destination Double-click the Excel Destination, configure the connection, set the mappings, and then click 'OK' to apply Now, we can execute the package to test the exporting. For more information, please see: Excel Destination: http://msdn.microsoft.com/en-us/library/ms137643.aspx If you have any more questions, please feel free to ask. Thanks, Jin ChenJin Chen - MSFT
Free Windows Admin Tool Kit Click here and download it now
August 20th, 2010 11:43am

Thanks for your reply. However i have SSIS 2005 and not 2008 and I cann't see ADO.NET Source in the Tools. Is there any thing that i am missing or miss understood?? Another important point i would like to putup here is , my store procedure has one input paramter. Please let me know
August 20th, 2010 4:18pm

what Jin chen has said is 100% right, for 2005 and 2008 , so i dont see any difference cant see ADO.Net == its called OLE DB Source in that you can set your SP and pass the variable to itSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
August 20th, 2010 4:48pm

Thanks guys for hleping me out. Here are the steps that i follow 1) Get OLEDB source & configure while configuring i select the "Access Mode" as "Sql command" and then i enter the value for ParameterMapping as @ID ='4' and SQLCommand as EXEC [SP Name] This is configured now 2) Next step i select the Excel destination and here it is giving me problem it says source columns are not available? how do i set up the source columns. I went back to OLEDB source and try to setup the source columns but not sure how do i setup the source output columns from OLEDB source so that i can mapped the the columns to excel destination Again thank you very much for your valuable time and suggestion
August 20th, 2010 5:23pm

A1: your fist part is to make a source object which looks like http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx A2: Make a template Excel file save it in a folder and ech time that the packages runs copy the excel file to a destination folder then the next step will be the DFT. Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
August 20th, 2010 5:46pm

Hey Nik thanks for that links. I have followed the steps that has been mentioned but still i am not able to get the column list which should be the source for Excel /flat file. 2ndly i also read the etire thread where ppl has the issue some one in that tread mentioned that we have to expose the store prcoedure meta data and all. also mentioned that, we have use statement 1=0. to be honest now i a am lost. Could you please help on this.
August 20th, 2010 7:09pm

You said that your source is a SP and now you are saying its Excel?? What is your source? is it a OLE or a excel? the link that i had mentioned is to set the OLE as a source (which is your SP) ---for the destination , make a excel file with headers and set the destination object (Desc_Excel Object in the toolbar its at the bottom NOT the top) -- just get the SP running then lets worry about the parameter passing.Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
August 20th, 2010 7:22pm

Thank Nik I was talking about the columns that should the source FOR Excel destination. However let me explain you again what exatly is happening I have store procedure which has a input parameter. This SP has dyanamic queries and all the queries has been assigned to on variable within SP and finally using "Exec var1" command i am executing the queries with the SP. I want to exeucte this SP using SSIS package. For that I am using OLEDB data source to executes the store procedure and for that i have follow the steps that has been mentioned on the following link http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx till now this every thing is wonderful :) Now when i take the Excel as destination and try to connect destination (excel) and Source (OLEDB) this gives me error TITLE: Microsoft Visual Studio ------------------------------ Cannot create connector. The destination component does not have any available inputs for use in creating a path. ------------------------------ BUTTONS: OK ------------------------------ & this is the problem. Now How can i get the list of columns from my Source (OLEDB) that will be input source FOR Excel destination I hope i am able to explain the issue
August 20th, 2010 8:32pm

are you saying that each time you have a different SELECT statment from the SP? will you have a different Metadata each time from the SP? different column names nad data type (different metadata results?) if your answer is Yes You cant use DFT in SSIS to do that , my suggestion is http://www.mssqltips.com/tip.asp?tip=1202 you can also check http://www.eggheadcafe.com/software/aspnet/29713573/query-output-to-excel.aspx and implement it in SSIS but i am not suggesting that , becase if you can do something in SP when your source is SQL why not use a SP. Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
August 20th, 2010 10:11pm

This will not be possible this way. As DFT does not allow dynamic meta data.
August 20th, 2010 10:29pm

columns will remain same, but the codition will be vary. E.g. declare @var1 varchar(1000) SET @var1 ='select * from table1 where id =' + @ID Exec(@var1) I hope this will make clear what exactly the sp is.
Free Windows Admin Tool Kit Click here and download it now
August 23rd, 2010 3:51pm

to make every thing easy and to keep every one one the same track please add the fields like SELECT F1, F2 , F3 ....... then yes you can do that in a DFT in ssis , just do the steps as i askes you ------------------------------------------------------------------------------------------------------- i have a similer example ,but its way bigger and more complicated than your request. it basically select User by User (something what you are looking for ... SET @var1 ='select * from table1 where id =' + @ID) then inseters the work to each file then Emails them to the related User (User by User or file by file) . THIS IS JUST TO GIVE YOU AND IDEA AND MAY NOT BE YOUR ANSWER. http://plexussql.blogspot.com/2010/04/emailing-work-log-notification-to.htmlSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
August 23rd, 2010 4:51pm

This doesnt work if the ADO Net Source is inside a OnError Event Handler and if there are parameters to the stored procedure call. It does not seem to work at all.
Free Windows Admin Tool Kit Click here and download it now
February 24th, 2011 3:26pm

Did you follow this technique and it did not work?Arthur My Blog
February 24th, 2011 3:53pm

It doesnt look like using expressions to set the ADO NET Source SqlCommand property in a Data Flow Task inside a Event Handler works. It doesnt change the text of the sqlcommand like it does outside of a Event handler.
Free Windows Admin Tool Kit Click here and download it now
February 24th, 2011 4:11pm

I faced the same problem. The problem is because the stored procedures do not have meta data of the output columns and hence cannot be automatically retrieved by SSIS. Please read this article below and hope this helps. http://consultingblogs.emc.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx
March 30th, 2011 12:12pm

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

Other recent topics Other recent topics