Dump table to multi-line CSV file....

Question

Post reply

Dump table to multi-line CSV file....

Barkingdog

SSCoach

Points: 18670
More actions
May 27, 2013 at 11:47 am

#394855

I want to export a table to a CSV file using SSIS. Each table row has 2 "row type" columns indicating the data following it needs to appear on a new line in the file. Example
Create Table DumpToCSV
(
RT1 char(1) NOT NULL, -- RT = row type
a1 char(1) NOT NULL,
a2 char(1) NOT NULL,
RT2 char(1) NOT NULL, -- another row type column
b1 char(1) NOT NULL,
DateExporteddatetime not null
)
insert into DumpToCSV values ('A', 1, 2, 'B', 3, 2013-05-28)
insert into DumpToCSV values ('C', 4, 5, 'D', 6, 2013-05-28)
Desired single CSV output file:
A,1,2 (split occurs at each "row type" column, A, B, C, D)
B,3
C,4,5
D,6
TIA for any ideas on how to tackle this with SSIS.
Barkingdog

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

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

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

This looks like bread and butter for an asynchronous Script Component to me.

Here's a link that should get you started.

The 'asynchronous' part here refers to the fact that the number of rows entering the Script Component is not necessarily the same as the number leaving it. In your case, you'll have one row entering & then two calls to the .AddRow() method inside the script to generate two rows of output.

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.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Phil Parkin (5/27/2013)
This looks like bread and butter for an asynchronous Script Component to me.
Here's a link that should get you started.
The 'asynchronous' part here refers to the fact that the number of rows entering the Script Component is not necessarily the same as the number leaving it. In your case, you'll have one row entering & then two calls to the .AddRow() method inside the script to generate two rows of output.

Hi Phil,

What happens if there are 3 or more values? Do you have to use the .AddRow() method more than the two times you already have it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Jeff Moden (5/27/2013)
Phil Parkin (5/27/2013)
This looks like bread and butter for an asynchronous Script Component to me.
Here's a link that should get you started.
The 'asynchronous' part here refers to the fact that the number of rows entering the Script Component is not necessarily the same as the number leaving it. In your case, you'll have one row entering & then two calls to the .AddRow() method inside the script to generate two rows of output.
Hi Phil,
What happens if there are 3 or more values? Do you have to use the .AddRow() method more than the two times you already have it?

Hi Jeff

Yes, when the Script Component is running in async mode, you have to call .AddRow() every time you want to add a row to the output buffer. So there is effectively a disconnect between what is going in and what is going out.

In practice, that affords great flexibility - as in this case, one input row can create multiple output rows. Alternatively, multiple input rows can be condensed to a single output row. Obviously, you always need to keep an eye on performance, but as long as the input and output column definitions are known at design time, you have total flexibility and are limited only by your coding capabilities.

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.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Thanks, Phil. Sounds like the near equivalent of using multiple CROSS APPLYs to do an unpivot with the same caveats. Good to know.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)