Generate Multiple Files from a SQL Server Table in SSIS Package

We have to generate multiple CSV files based on the region code. The table has information about customers of all-region. The goal of this package to extract the data from the table and based on the region create a CSV file.

I am using the “AdventureWorksDW2012” database for the demo.

·         DimCustomer

·         DimGeography

DimCustomer table having the information of the customer and DimGeoreaphy table having the information of the region code.

So our package will create 6 six different files for each region code.

Select ISNULL(dc.FirstName,'  ')+ISNULL(dc.MiddleName,'  ') +ISNULL(dc.LastName,'') as Full_Name, dc.EmailAddress,dc.Phone from [dbo].[DimCustomer] dc JOIN [dbo].[DimGeography] dg on dg.GeographyKey=dc.GeographyKey Where CountryRegionCode=?

 

Let’s see how we will achieve it.

Take execute the task to get them all distinct region code.

 

Taking an object type variable to set store the full result set of the execute SQL task.

Now I am taking Foreach loop container and taking Foreach ADO Enumerator and configuring the Enumerator.

Taking a variable to store the region code and mapping this variable.

Inside the Foreach loop container, I am taking data flow task.

Taking source as OLEDB source.

For the mapping the parameter we need to click on the Parameters button and configure the parameter.

Now I am taking Flat file destination for creating the CSV files.

Here I am creating a dynamic file connection.

Right-click on the flat file connection and go to the properties.

 

Select ConnectionString property and click on …..

Here I am writing the expression.

 

@[User::File_Path]+ @[User::File_Name]+"_" +@[User::CountryRegionCode]+"_" + (DT_WSTR,4)YEAR(GETDATE()) + "_" + (DT_WSTR,4)MONTH(GETDATE()) + "_" + (DT_WSTR,4)DAY(GETDATE()) +".csv"

Now my package is ready to run.

Before running this package see the destination table.

Now running this package.

The package executed successfully.