February 14, 2017 at 2:48 am
Hi,
Please correct me If I am wrong area to post, but I would like to post a question bit related to SSIS, I had two Store Procedures which has basically select statement output. I need to send this two separate output to one excel File with 2 sheets. Could some one can help me with this.
Many Thanks in Advance.
February 15, 2017 at 2:31 pm
It's a relative piece of cake. You can do it a couple of ways. I just tried taking the exact same output and using a Multi-Cast to send it to two different sheets in the same Excel File, and that worked. With different sources, you may need to use multiple data flows, but I'd try using just one first. The only caveat is to be sure both SPROCs can run at the same time. If that's okay, then create your data flow task. Create a single Excel connection manager for your Excel file. Then create an OLE DB Connection Manager for your SQL Server. Next, create an OLE DB Source for each SPROC. Next, create an Excel Destination for each of the OLE DB Source objects. Configure your spreadsheet by ensuring that the columns you'll be receiving from each data source exist in row one of each of your spreadsheet tabs that you intend to send data to. This has to exist at least during the package creation. Then you connect each data source to it's Excel Destination by dragging the outputs. Next step is to edit each destination and map the columns. Then run your package. Be sure to use test data and a test spreadsheet so if you mess something up, you're not hurting anything.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply