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.
