Scenario:
I have
Novelist names and Country information in my table which I have to split country
specific files to respective destinations.
Ex: I have
India and Argentina data in my table in which I will split India data into one
file and Argentina data into another file dynamically.
Solution:
SQL Table data:
Step 1: Create a New
package under that
Step 2: Create two variables "Countries",Data type = Object and "Country" , Data type = String.
Step 3: Drag Execute
SQL Task to control flow pane, rename it to "Get
distinct countries"
Step 4: configure the Execute SQL Task, Connection type = OLEDB, set result type as Full result type and use below query into variable countries as shown in below image.
select Country
from
[DEMO].[dbo].[SpilittingCountries]
Group by Country
Step 5: See the
Result set pane, all values from database query should save into user variable Countries
Step 6:Drag Foreach Loop container and connect to Execute
SQL Task, configure Foreach Loop Container
Step 7: Edit For each Loop Editor, Cjoose "For each
ADO Enumerator". Set object source as variable "User::Countries" and select Rows in the first table
Step 8: Create new variable Country,
this variable gets Country data from each
row of Countries variable
Step 9: Drag Data
Flow Task add into for each loop container.
Step 10: Double click on data flow task, drag and drop "OLEDB Source" from tool box
Step 11: Edit OLEDB
Source as shown in below image (Note: Add an OLE DB Connection manager), Use below SQL query to load data which has single parameter.
select *
from
[DEMO].[dbo].[SpilittingCountries]
where Country=?
In every
iteration of loop string variable Country will get a value from object variable Countries,
Country variable will pass value to parameter in above query.
(Note: ? is representation for parameter)
Step 13: Drag a Flat
File destination from tool box and connect it with OLEDB Source as shown in below image.
Step 14: Edit Flat
File Connection manger Click new button to create connection manager.
Step 15: select
Delimited format and click ok.
Step 16: Configure
the Flat File destination Connection manger as follows, File name as Country.CSV. (Note: This
file doesn't exist), Create one file as Country.CSV. This file is just to configure flat file destination once, while loop is running ,package will generate multiple files with respect to number of countries data in source.
Step 17: Go to
flat file connection manger to set dynamic connection. select flat
file connection manager properties and under Expressions follow below steps as shown in screen shots.
Step 19: Use Country variable as follows and evaluate expression, evaluated value should be valid for connection manager as follows:
Step 20: Execute the
Package--
Step 21: Open destination folder in which two files will be generated as follows which is India & Argentina because we have two countries data in source table.
Step 22: Open "Country-Argentina" file to check whether it has only Argentina data or not.
Happy Business Intelligence :-)
