Multiple Result Sets to a Flat File?

Question

Multiple Result Sets to a Flat File?

sqlusers

SSCrazy

Points: 2463
More actions
March 8, 2012 at 4:27 am

#258366

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!

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

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

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

sqlusers SSCrazy Points: 2463 More actions · Answer 2

sqlusers

SSCrazy

Points: 2463

March 8, 2012 at 9:31 am

#1456629

yes, i can able to modify my procedure.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

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

sqlusers SSCrazy Points: 2463 More actions · Answer 5

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

Phil Parkin SSC Guru Points: 246986 More actions · Answer 6

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

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

sqlusers SSCrazy Points: 2463 More actions · Answer 8

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