Multiple Result Sets to a Flat File?

  • Hi Guys,

    I have a procedure which will return two result sets.

    1==> SELECT

    EmpID

    FROM Employee

    2==> SELECT

    DepID

    FROM Department

    I need to send Output of these result sets to a concern distributor as an attachement.

    How can i achieve this using SSIS Package. I am using VS 2005.

    Appreciate your help!

  • Is it possible to modify the stored procedure?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • yes, i can able to modify my procedure.

  • What do you want the file to look like?

    Just modify your proc to UNION the two result sets and you're good to go.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (3/8/2012)


    Just modify your proc to UNION the two result sets and you're good to go.

    +1

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Phil Parkin (3/8/2012)


    What do you want the file to look like?

    Just modify your proc to UNION the two result sets and you're good to go.

    Thanks Phil!

    I can modify the procedure using UNION, but

    1. the column names of these two result sets are not one and same

    2. First result set may return 2 columns and the second result set may return 3 columns.

    Actually, my output will be [In Flat File]:

    EmpID EmpName

    =============

    1 A

    2 B

    DepID DepName Class

    ==================

    1 D1 C1

    2 D2 C2

    3 D3 C3

  • Right - that makes it a little harder. It also illustrates the risk of oversimplifying your questions 🙂

    This requires two separate dataflows to two separate files.

    After that, use the standard DOS copy command to append one file to the other.

    The File System task does not have the required horsepower.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (3/8/2012)


    The File System task does not have the required horsepower.

    The File System Task doesn't even have the horsepower to decently move a file, let alone do something more complex. 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Thanks Guys,

    I have done it by using two different Data Flow Task calling two different procedures with one flat file connection with unchecking the Overwrite data in file from Flat file Destination. 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

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