SSIS:Create a package for dynamically create multiple csv files based on records Count

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Help required!!.

Requirement
I have a StoredProc it returns the 10 Million records. In this case 10 Million records it may increase/decrease..

So in this scenario,we need create a SSIS Package for multiple csv files.

In this Ex .if 10Million Records then 10 csv files, each file should create with 1 million records

done as below, its working fine but taking more time.

steps:
--------
1. Script Task: is used to generate the Folder path with date and time
2.file system Task:Create Folder if Doesn't Exists based on script task file path.
3.Data Flow Task
a.Flat File Source to be set it up
b.Script Component:here i am using SP to return the results based on results, i am splitting the main result table to 10 tables(each table contains 1million records) and looping through the 10 tables.
c.In that loop we are creating the csv file based on that table.

Working as expected but its very long time to create 10 csv files .so wanted to check if any other scenario's. Please help on this.

Thanks,
Murali

Posted 25-Aug-14 7:15am

Murali7156

Updated 25-Aug-14 22:45pm

v6

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Arvind Kumar Chaubey · Answer 1 · 2014-08-25T19:22:00

Solution 1

you have to use for loop container for creating multiple CSV files.

Please check below link - http://stackoverflow.com/questions/7352104/create-multiple-flat-file-from-running-same-query-with-different-criteria[^]

Posted 25-Aug-14 19:22pm

Arvind Kumar Chaubey

Murali7156 · Answer 2 · 2014-09-22T01:33:00

Please find the below link to get the solution perfectly.

http://social.technet.microsoft.com/wiki/contents/articles/3172.split-a-flat-text-file-into-multiple-flat-text-files-using-ssis.aspx[^]

And here major drawback is OLEDB source executes based on result count

for Ex: SP returns 10million Records and our splitting count value is 1million then 10 times this SP Excutes and process the 10 csv output files.