How to Create Multiple Flat File Destinations for each Region Data from Sales Table Source of SQL Server using SSIS Package

SSIS Package to Create and Save Each Region Data to a different Flat File Destination from Sales Table in SQL Server
Scenario :
Suppose we have the Sales Data in the SQL Server Table "[Tbl_Sales]" as follows..
SELECT [Order_Id]
      ,[Sales_Region]
      ,[Month]
      ,[Quarter]
      ,[Cust_Id]
      ,[Prod_Id]
      ,[Net_Sales]
  FROM [dbo].[Tbl_Sales]
GO

Now we want to Save each Sales_Region (Eg: India, America,Japan..) Data to a different Flat File destination. This File should Save as with that Region Name with Date Time Suffix as follows...

T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Desti\Output\

Eg : SalesByRegion_America_20171118_105454.txt

Now lets Design the Package as follows...

------------------------------------------------------------------------------------------------------

Phase - 1 : Create the required Variables :

------------------------------------------------------------------------------------------------------

Here we create the below variables which we further use in the Package...

vDistRegionNamesObj : This is Dynamic System.Object type variable used to Store the SQL Full Result Set of the distinct Region names in run time, by Execute SQL Task.

vRegionName : This is a Dynamic String variable where we store the each Region Name in run time by ForEach Loop Container.

vOutputFolder : In this Static Variable we store the Output Folder Path where we save the each Region output data in text files..

T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Desti\Output\

vOutputFileName : In this Static variable we store the File Naming convention "SalesByRegion", which we use it final Output file Naming .

vDateTimeSuffix : This Variable is used to store the Current Date Tim suffix which we use in final Output file naming. The value for this Variable will calculate using the below expression..

REPLACE(REPLACE( REPLACE(SUBSTRING((DT_WSTR,50)GETDATE(),1,19),"-",""),":","")," ","_")

Eg : 20171118_114209

vOutputFileFullName : This is a very important Dynamic Variable used to store the final Output File Name. This Variable will also use as a Connecting String to the Flat File Destination.

Example Value : 

T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Desti\Output\

SalesByRegion_America_20171118_105454.txt

------------------------------------------------------------------------------------------------------
Phase - 2 : Add the Execute SQL Task to Control Flow and Set the required Properties:

------------------------------------------------------------------------------------------------------

A) Add the Execute SQL Task to Control Flow

B) In Execute SQL Task Editor > General Tab , Set the Properties

Here I set the Properties as follows...

Connection = db_SalesDB  ; It is the OLED Connection to the Source Sales Database.

SQL Statement = Select Distinct Sales_Region from dbo.[Tbl_Sales] ; This will extract the Distinct Sales_Region names from the source table.

ResultSet= Full Result Set

Here, Full Result Set will allows you to read the Distinct Region Names extracted in the above SQL Statement.

Notes :

ResultSet = Full Result Set : This option we will use when we want to read Multiple Rows with a Single Column.

ResultSet = Single Row : This option we will use when we want to read Single Row with Multiple Columns.

C) In Execute SQL Task Editor > Result Set Tab, Set the Properties

Here we map the Result Set to an Object Variable to store the SQL Statement Result as follows..

Result Name = 0 ( The Index of the Variable , here we used only one variable so its index position is "0" )

Variable Name = User::vDistiRegionNamesObj

------------------------------------------------------------------------------------------------------

Phase - 3 : Add the ForEach Loop Container and Connect to Execute SQL Task then Set the required Properties:
------------------------------------------------------------------------------------------------------

A) Add the ForEach Loop Container and Connect to Execute SQL Task

B) In ForEach Loop Editor > Collection Tab , Set the properties

Here we need to Set the properties as follows...

Enumerator = ForEach ADO Enumerator ; We chosen this to read the rows from the Result Set, Passed by the Execute SQL Task

ADO Object Source Variable= User::vDistiRegionNamesObj

Enermerator Mode = Rows in the First Table

C) In ForEach Loop Editor > Variable Mapping Tab , Set the properties

Variable = User::vRegionName ;

Here the each Region Name read from the ADO Object Source Variable will be stored in this Variable.

Index = 0

------------------------------------------------------------------------------------------------------
Phase - 4 : Add the DataFlow Task inside ForEach Loop Container then Design the Flow:

------------------------------------------------------------------------------------------------------

A) Add the DataFlow Task inside the ForEach Loop Container

B) Add the OLEDB Source Inside the Data Flow

C) In the OLEDB Source Editor>Connection Manager, Set the required Properties

Here, We need to connect to the OLED Connection , then Write the SQL Query with the Parameter ( ? ) in Where clause as follows..

SELECT [Order_Id]

      ,[Sales_Region]
      ,[Month]
      ,[Quarter]
      ,[Cust_Id]
      ,[Prod_Id]
      ,[Net_Sales]
  FROM [dbo].[Tbl_Sales]
  WHERE [Sales_Region] = ?

D) In the OLEDB Source Editor>Connection Manager, Set the Parameters.. Mapping.

Parameters= Paramter0

Variables=User::vRegionName

Parameter Direction = Input

Next Veiw the Columns from Source are Loading are or not..

Finally say OK.

E) Add the Flat File Destination and Connect to OLD DB Source

F) From Flat File Destination Editor > New , Create a Connection to Flat File Output location

Here select the Flat File Format as Delimited, as we want to save the data in Flat File as Tab Delimited Format..

Next from Flat File Connection Manger Editor > General Tab , click "Browse".

Next select the Output Folder and give some name like "SalesByRegion" , this name will change in the Run Time. 

Next click Open that will Establish a connection to the Destination.

G) From Flat File Connection Manger Editor> Columns Tab, Select you Column Delimiter

Here by Default it will select the Column Delimiter as Comma{,}. I chosen here as Tab{t}.

Next from the Advanced Tab, you can Modify the Columns Names and Data Types if you want to display them in different way in Flat File Destination.

Finally say OK.

H) Next in Flat File Destination Editor > Mappings, Do the Column mapping between Source and Editor.

Now the Flat File Connection is Ready. This Connection String we need make dynamic in Next Step.

F) Next Make the Flat File Destination Connection String Dynamic.

Go to "SalesByRegion_FlatFileDest" > Properties > Connection String and assign the Variable @[User::vOutputFileFullName] , to Dynamically pass the Output Destination File name.

I) Finally Run the Package..

Control Flow :

 Data Flow :

------------------------------------------------------------------------------------------------------

Output :

Now the Output looks like as follows..

Now open any Region File, say France , we will see only that Region Data.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------