October 12, 2016 at 12:55 pm
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.
October 12, 2016 at 1:13 pm
yogi123 (10/12/2016)
HelloI 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.
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.
March 28, 2019 at 3:32 pm
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