SSIS - How to Create Multiple Files Dynamically From a SQL Server Table

Scenario:

Let's say we have data sale data in dbo.CustomerData table for different countries. We are asked to generate file for each of the country. The file should have data only related to that country and File Name should be CountryName.text. 

Solution:

We will create SSIS Package to perform this,Let's proceed with step by step approach.

Step 1: 

Create dbo.CustomerData with some data so we can use as our source by using below script.
USE TestDB
GO
CREATE TABLE dbo.CustomerData
  (
     Customerid   INT,
     CustomerName VARCHAR(100),
     Sale         INT,
     CountryName  VARCHAR(100)
  )
GO
INSERT INTO dbo.CustomerData
VALUES     ( 1,
             'Aamir',
             100,
             'USA'),
            (2,
             'Raza',
             400,
             'USA'),
            (3,
             'Singh',
             1000,
             'India'),
            (4,
             'Robert',
             500,
             'Germany') 

Step 2: 

Create SQL Server Integration Services Project and add new SSIS Package to it. 

Step 3:

Create two variables 
CountryNameObj: Object Type variable to hold all the distinct Countries
CountryName: We will use this variable to hold the country name inside For-each loop.
Fig 1: Package Variables

Step 4:

Bring Execute SQL Task to Control Flow Surface and configure as shown below. We are getting distinct countries from table so we can use them inside For-Each loop to create files.
Fig 2:Populate CountryNameObj variable by using Full Result Set in Execute SQL Task

Fig 3: Map the Result Set to CountryNameObj variable

Step 5:

Bring For-Each Loop and connect the Execute SQL Task to it and configure as shown below. We will be loop through the list of Countries (CountryNameObj variable) and then use one country at a time to get data from table and create file.
Fig 4: For-Each Loop read Country List from CountryNameObj Variable

Fig 5: Map the value to CountryName variable


Step 6:

Bring the Data Flow Task and put inside For-Each Loop Container. Inside Data Flow Task, Drag OLE DB Source and configure as shown below
This is the query I used 

Select Customerid,CustomerName,Sale,CountryName from dbo.CustomerData
WHERE CountryName=?
Fig 6:OLE DB Source to use Command with Parameter

Fig 7: Map the CountryName variable to the Query inside OLE DB Source

Step 7: 

Drag Flat File Destination and Connect OLE DB Source to it.Double click on Flat File Destination and create connection. you can create any name you like, you can have .csv or text file that is depending upon your requirement. The name will be overwritten while we will execute the package by expression that we are going to use in next step.
Fig 8: Create Flat File Connection to Test File 

Step 8: 

As we want to create file with Country Name, we need to write expression on Flat File Destination Connection Manager. Right Click on Flat File Connection Manager and go to properties. Then Click on Expressions and choose the Connection String as shown in fig
Fig 9: Write Expression on Flat File Connection Manager to generate file name by using Country Name variable

Write below expressions as shown, Change the Folder Path according to your Folder Path.
"C:\\Users\\ashahzad\\Desktop\\"+ @[User::CountryName]+".txt"
Fig 10: Expressions to Create File with Country Name

Click on Flat File Connection Manager and then go to properties and Set Delay Validation=True

Run the SSIS Package, Three files should be created to the path which you have provided in the Expressions. In my case ,Files are created on Desktop as can be seen below
Fig 11: Complete SSIS Package

As we can see three files are created and each file has only data related to that Country. 

Fig 12: SSIS Package Output




2 comments:

  1. Thanks for this - coming back to SSIS after a long break and couldn't remember how to create a file with a dynamic name - this got me going in no time!

    ReplyDelete
  2. Thanks for the information. It was very helpful. My files are generating correctly but only problem is as county name varies in size so output files generating with spaces like:
    USA.txt
    INDIA .txt
    EUROPR .txt

    How to resolve this. Thanks in advance.

    ReplyDelete