Generate Multiple csv files through SSIS

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

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

    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

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

Viewing 3 posts - 1 through 2 (of 2 total)

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