Microsoft Business Intelligence: Split SQL Table Data Into Multiple Flat Files

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=?

Step 12: Click Parameters set user variable Country as a parameter in above query .

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 18: Set property as Connection string, In Expression follow below steps

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.