June 28, 2016 at 6:38 pm
Hi,
I have a requirement of exporting the table data into a flat file (.csv format) with each of the files containing only 5000 records each. If the table has 10000 records then it has to create 2 files with 5000 records each. Could you please help me how to achieve this using SSIS?
I have tried BCP command for the above mentioned logic. Can this be done using Data Flow Task?
Any help is much appreciated. Thanks in advance.
June 29, 2016 at 4:28 am
I think you are using SQL Server as source database.
Use below approach to solve this:
SELECT
st.*
, (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) % 2 AS bucket
FROM
SourceTable AS st;
That query will pull back all of your data plus assign a monotonically increasing number from 1 to ROWCOUNT which we will then apply the modulo (remainder after dividing) operator to. By modding the generated value by 8 guarantees us that we will only get values from 0 to 1, endpoints inclusive.
You might start to get twitchy about the different number bases (base 0, base 1) being used here, I know I am.
Connect your source to a Conditional Split. Use the bucket column to segment your data into different streams. I would propose that you map bucket 1 to File 1, bucket 2 to File 2... finally with bucket 0 to file n. That way, instead of everything being a stair step off, I only have to deal with end point alignment.
Connect each stream to a Flat File Destination.
June 29, 2016 at 4:33 am
For more info, refer to this URL.
http://stackoverflow.com/questions/24612375/ssis-export-all-data-from-one-table-into-multiple-files
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply