Share via

Split a Flat (Text) File into Multiple Flat (Text) Files using SSIS

  • Article

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 split a flat file which contains around 2 gb data for e.g: 2000 records and we want to split into multiple files and each file should contain 50 records.

Solution: 

Create a new SSIS package.

http://jeeveshfuloria08.files.wordpress.com/2011/05/1.png

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

http://jeeveshfuloria08.files.wordpress.com/2011/05/23.png

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:

http://jeeveshfuloria08.files.wordpress.com/2011/05/2.png

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.

http://jeeveshfuloria08.files.wordpress.com/2011/05/16.png

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.

http://jeeveshfuloria08.files.wordpress.com/2011/05/19.png

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.

http://jeeveshfuloria08.files.wordpress.com/2011/05/20.png

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.

http://jeeveshfuloria08.files.wordpress.com/2011/05/121.png

 Then using condition split task applying condition that its value should be higher than the value of @counter (0) and its value should be less then @counter+50, because  every time @counter value changes or increments.

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

http://jeeveshfuloria08.files.wordpress.com/2011/05/113.png

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.

http://jeeveshfuloria08.files.wordpress.com/2011/05/252.png

Now go to script task. It is very important. We are using script task for giving dynamically file names when file going to create. Here select all variable in readwritevariables.

http://jeeveshfuloria08.files.wordpress.com/2011/05/21.png

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

http://jeeveshfuloria08.files.wordpress.com/2011/05/221.png

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.

http://jeeveshfuloria08.files.wordpress.com/2011/05/141.png

Folder is empty. So time to execute the package. I am executing the package.

http://jeeveshfuloria08.files.wordpress.com/2011/05/41.png

It is still executing the task. Yellow means executing, and green means successfully executed.

http://jeeveshfuloria08.files.wordpress.com/2011/05/51.png

So package successfully executed. Now we have to check in folder where we want to split the files.

http://jeeveshfuloria08.files.wordpress.com/2011/05/61.png

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

http://jeeveshfuloria08.files.wordpress.com/2011/05/71.png

 Like this file each file contains 50 records. So finally task completed and we can also take the source as old db table instead of flat file.