Post reply

SSIS output from SP to flat file

  • June 11, 2010 at 7:11 am

    #100261

    Hi All,

    I have simple SP in SQL2005, which produces to results tables (please see attached)

    Is it possible to create an SSIS package which puts the results of the two tables into Excel?

    Or

    Would I have to brake the SP down into two sperate ones?

    Thanks

  • June 12, 2010 at 11:04 am

    #1179546

    Hi could you please try this:

    1) At the end of the sp try to inset the records from two tables into a single table(temp table) which has got the columns from both the tables

    2) While insert the records of the first table make the values for other columns as NULL and same way while inserting the records from second table.

    3) In package after the OLEDB source place a Multicast.

    4) Pass the out put of the Multicast into two destinations as required.

    If you are doing like this you might have to make a small change in the sp as it is using temp table, please see the attachment.

    Thanks & Regards,
    MC

  • June 14, 2010 at 3:24 am

    #1179718

    Do you need it in one excel or two excel file?

  • June 14, 2010 at 4:14 am

    #1179738

    Either would be fine

  • June 14, 2010 at 4:46 am

    #1179751

    you can do it like the way i did in one of my SSIS,

    1. Take Execute SQl task to excute your SP and put the data in two tables.

    2. Take Data flow task, give source(OLE DB) and destination(EXCEL) for first table and then another Data flow task for second table.

    3. Can use send mail to send this file to user as attachment.

  • June 14, 2010 at 6:22 am

    #1179803

    Hi

    Can I know whether the solution which I gave has solved your issue? Or is it like my understanding about the problem was not correct ?

    Thanks & Regards,
    MC

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply