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

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  May 27, 2013 at 12:26 pm

  #1618816

  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.

  ---

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  May 27, 2013 at 11:33 pm

  #1618853

  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: 247158

  More actions

  May 28, 2013 at 12:25 am

  #1618860

  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.

  ---

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  May 28, 2013 at 6:40 am

  #1618927

  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)