If you are working with Microsoft SQL Server Integration Services or SSIS, you may encounter a scenario where you need to export the tabular data in flat files. We have a similar kind of requirement. In our database, we have a COVID 19 tables with information such as cases, recovery, and deaths count for each country and based on date. As per the SSIS project requirement, we need to dump the database table data into multiple smaller flat files with extension “.csv”. The main element is to generate the files dynamically. For our project, we are splitting data based on date, and we are using the same date for the file names. It is much better if we first have a look at the demo video what the final output is.
Let’s try to complete the requirement, as shown in the above video. In the demo package, we have the following items.
Variables: We need variables to store the date and then use it to generate dynamic files. The variables scope is at the package level.
Variable Name: “Date”
Data Type: “DateTime”
Usage: Store a single date.
Variable Name: “DateList”
Data Type: Object
Usage: It stores, all the dates from the COVID 19 database table which we need to use for data splitting and file names.
Execute SQL Task: The “Execute SQL Task” is querying all the different dates and storing it inside the “DateList”.
SQL:
SELECT DISTINCT RecordDate FROM dbo.tblCovid19Cases
Foreach Loop Container: Once we have all the dates in our “DateList” SSIS Package Variable, we are looping with all the dates. With each iteration, we are also changing the “Date” variable value, which we need to filter the data in the “Data Flow Task” and generate the dynamic expression for the Flat File Connection.
"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: Finally, the “Data Flow Task” to export data from the database table into flat files.
Source Table: tblCovid19Cases
Source SQL:
Source Filter:
Destination:
Once we add all the required elements within the SSIS package for CSV the export, we need to execute it. If everything is correct, we may see the following output.
We can add more functionality in the same SSIS package. But for the time being, it is more than enough for this article. Feel feel to provide your feedback for this article. If you are looking for the SSIS, SSAS, or SSRS training, you can always drop me an email at “[email protected]”.