Split a flat(text) file into multiple flat(text) files using ssis

Split a flat(text) file into multiple flat(text) files using ssis

Summary: This is in an example of splitting a flat file into multiple files based on business scenario.

Business Scenario:  In our situation we want to splitting a flat file which contain around 2 gb data for e.g: 2000 records and we want to split into multiple files and each file should contain 50-50 record.

Solution:  Create a new ssis package.

i created  3 variable:

no  variablename    datatype    values

1.       counter                 int32           0

2.       countrows          int32            0

3.       pathname            string          C:\Users\jeevesh.fuloria\Desktop\Exel Sheets\may task\split folder\j

here we have to create some dataflow task and control flow task. so for this scenario i am taking some control flow task such as sequence container,dataflow task,for loop container, script task and some dataflow task such as row count, conditional split etc. see the picture below:

In top dataflow task name count total rows here i am counting the total rows in a flat file how many rows are there in flat file.

next one is for loop container. in for loop contain i am using 3 variable which i have created earlier.This is basically for looping purpose till reach the counter to  total rows in flat file.

here i am increasing the variable value @counter=@counter+50. every times it will increment by 50 to till total rows.and @counter<@countrows means it check the condition whether @counter value is less then @countrows or not.when @counter value reach the @countrows then it will exit from the loop.and firstly i put 0 value in @counter.

now here this is second data flow task naming splitting flat file.i am taking flat file source and flat file destination for splitting purpose. and also using data conversion and conditional split task. data conversion is basically for converting the datatype of column.here i am converting the datatype from varchar to numeric of id column.

 then using condition split task.here applying condition that is value should be high than the value of @counter (0) and is value should be less then @counter+50,because  every time @counter value change or increment.

[Data Conversion].id > @[User::counter] && [Data Conversion].id <= @[User::counter] + 50

then go to flat file destination and select the path, and right click on flat file connection manager for destination, go to properties and click expression,then take connection string and put the pathname variable value ,click ok.

now go to script task.it is very imporant. we are using script task for giving dynamicallly file names when file going to create. here select all variable in readwritevariables.

the click on edit script for writing some script for giving dynamic name.

now click on ok. every thing is complete in package for doing the task. firstly we check the folder where we want to splitting the files.

folder is empty. so time to execute the package .i am executing the package.

it is still executing the task. yellow means executing, and green means sucessfully executed.

so package successfully executed. now we have to check in folder where we want to split the files.

so here we have multiple files,each file contain 50-50 rows.we can check the files data.

 like this file each file contain 50-50 records. so finally task completed. and we can also take the source as old db table instead of flat file.


About jeeveshfuloria

i am working in iSpace global solution pvt. ltd. in MSBI field and pursuing b.tech through AMIE.
This entry was posted in SSIS. Bookmark the permalink.

3 Responses to Split a flat(text) file into multiple flat(text) files using ssis

  1. El Inexpugnable says:

    Good post.

  2. sawan says:

    Amazing post, saved my Ass !

  3. Gus GH says:

    es un post de microsoft no es tuyo

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s