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 important. We are using script task for giving dynamically 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 successfully 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.
Richard Mueller edited Revision 2. Comment: Modified title, fixed a few typos, changed tag "Has Images" to "Has Image"