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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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