SSIS How to Export Table Data in to Multiple CSV Files

SSIS How to Export Table Data in to Multiple CSV Files

In this article, we will learn How to export table data into multiple CSV files using one reference column. For example, we have COVID 19 cases, recovery, and deaths count for each country and based on date. We need to rename the CSV based on the date column.
Following is the demo video for the outcome.

As shown in the above video, our SSIS package will create multiple files based on the "RecordDate". Let's replicate the same in our SSIS package.

We will start with a new blank SSIS project. The first thing we need to create the following package-level variables.

Variable Name: "Date"
Data Type: "DateTime"

Variable Name: "DateList"
Data Type: "Object"

As shown in the video, we have the following task

Execute SQL Task: In the "Execute SQL task", we are executing the following SQL

Code

SELECT DISTINCT RecordDate FROM dbo.tblCovid19Cases

The above SQL will return all the distinct dates for the column "RecordDate". We are storing the "Result Set" in the "DateList" variable.

Execute SQL Task 1 - SSIS Table Data into Multiple CSV

Execute SQL Task 1 - SSIS Table Data into Multiple CSV

Execute SQL Task 2 Result Set - SSIS Table Data into Multiple CSV

Execute SQL Task 2 Result Set - SSIS Table Data into Multiple CSV

Foreach Loop Container: After the "Execute SQL Task" the next is "Foreach Loop Container". In it, we are looping through all the rows stored in the "DateList" variable. We are also changing the "Date" variable for each iteration which help us to filter the "tblCovid19Cases" data based on the "RecordDate" column. We are using the same "Date" variable to create a dynamic flat file connection which we need inside the "Data Flow Task". Based on the "Date" and the dynamic Flat File Connection expression help us to create the different files.

Foreach Loop Container Details - SSIS Table Data into Multiple CSV

Foreach Loop Container Details - SSIS Table Data into Multiple CSV

Flat File Connection Manager - SSIS Table Data into Multiple CSV

Flat File Connection Manager - SSIS Table Data into Multiple CSV

Code

"F:\\POC\\SSIS\\Data\\Export\\"+ RIGHT("0" + (DT_STR,2,1252)DATEPART( "dd" , @[User::Date] ),2) + "_" + RIGHT("0" + (DT_STR,2,1252)DATEPART( "mm" , @[User::Date] ),2) +"_"+ RIGHT("0" + (DT_STR,4,1252)DATEPART( "yyyy" , @[User::Date] ),4)  +"_COVID19.csv"

Data Flow Task: At last, we have the "Data Flow Task". With the help of the "Data Flow Task", we are fetching data from the database table "tblCovid19Cases" and storing it into the Flat Files. As we know, the "Date" variable and the Flat File connection is updating within the "Foreach Loop Container" we will get a new file for each "Foreach Loop Container".

Data Flow Task - SSIS Table Data into Multiple CSV

Data Flow Task - SSIS Table Data into Multiple CSV

Finally, Save the SSIS package and project one more time and execute the newly created package. Following is the image for our SSIS Package.

Package - SSIS Table Data into Multiple CSV

Package - SSIS Table Data into Multiple CSV

Demo SSIS Table to Multiple CSV Export

Demo SSIS Table to Multiple CSV Export

We can make the SSIS package more dynamic. But for the time being, it is more than enough for this article. Please let us know How you like this article. If you are looking for the SSIS, SSAS, or SSRS tutorial you can always drop me an email at "[email protected]".

0 Comments
Leave A Comment

Please login to post your valuable comments.

Join the newsletter

Get the latest vLemonn news first

share