Generate Multiple csv files through SSIS

• yogi123

  SSCrazy

  Points: 2932

  More actions

  October 12, 2016 at 12:55 pm

  #330917

  Hello

  I have one task, where i need to generate csv files which has max 500 records in it. so let's one file has 1250 records, then i need to generate three files and give them name accordingly. so first file has 500 records, second file has another 500 records and third file has 250 records.

  is it possible to do this in SSIS ? if yes, please let me know.

  thanks for your help.

• Phil Parkin

  SSC Guru

  Points: 247133

  More actions

  October 12, 2016 at 1:13 pm

  #1906189

  yogi123 (10/12/2016)

  ---

  Hello

  I have one task, where i need to generate csv files which has max 500 records in it. so let's one file has 1250 records, then i need to generate three files and give them name accordingly. so first file has 500 records, second file has another 500 records and third file has 250 records.

  is it possible to do this in SSIS ? if yes, please let me know.

  thanks for your help.

  Yes, it's possible, and there are multiple ways of solving this.

  One is to create a staging table to contain the rows to be output, with an additional INT IDENTITY(1,1) CLUSTERED PK column.

  Your process becomes:

  1) Truncate the staging table

  2) Insert to staging table (rows to be output)

  3) FOR LOOP in SSIS using a loop counter (i = 1,2,3,4 ...) and batch size (b = 500) to drive the selection query

  select cols from stagingtable

  where PK between (((i - 1) * b) + 1) and (i * b)

  4) Use a derived column to drive the name of your output file, using your loop counter as appropriate (File001.txt, File002.txt etc)

  Remember to exit from the loop when the query returns zero rows.

  ---

• afzaki7

  Newbie

  Points: 7

  More actions

  March 28, 2019 at 3:32 pm

  #2034153

  This video will help you in generating multiple files i a dynamic way 
  Generate multiple files using SSIS