How to create flat files per partition from a SQL Server Partitioned Table in SSIS Package

Scenario:

I created a youtube video in which I was showing how to create multiple files from a single SQL Server table depending upon RegionCD. In the comment I got this request how to create multiple flat files from a partitioned table. There should be one file created per partition. So here we go. 

Solution:

First of all we have to have a partitioned table to perform this requirement. I have created a sample database with partitioned table by using below code. You can use the same code for your test or learn and then you can change with your actual table name.

USE master

go

--Create Database for our post
CREATE DATABASE sales

go

--Add Multiple file groups 
ALTER DATABASE sales ADD filegroup sales_data_1

go

ALTER DATABASE sales ADD filegroup sales_data_2

go

ALTER DATABASE sales ADD filegroup sales_data_3

go

ALTER DATABASE sales ADD FILE ( NAME = sales1, filename =
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008\MSSQL\DATA\Sales_1.ndf'
, size = 50mb, maxsize = 50mb, filegrowth = 50mb) TO filegroup sales_data_1

go

ALTER DATABASE sales ADD FILE ( NAME = sales2, filename =
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008\MSSQL\DATA\Sales_2.ndf'
, size = 50mb, maxsize = 50mb, filegrowth = 50mb) TO filegroup sales_data_2

go

ALTER DATABASE sales ADD FILE ( NAME = sales3, filename =
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008\MSSQL\DATA\Sales_3.ndf'
, size = 50mb, maxsize = 50mb, filegrowth = 50mb) TO filegroup sales_data_3

go

--Create Partitioned Function, I have created per year
USE sales

go

CREATE partition FUNCTION parfunction_sale (datetime) AS range RIGHT FOR VALUES
('2013-01-01', '2014-01-01')

--Create Partition Scheme
CREATE partition scheme schema_sale AS partition parfunction_sale TO (
sales_data_1, sales_data_2, sales_data_3)

--Create our Table
CREATE TABLE dbo.customersale
  (
     customerid      INT IDENTITY(1, 1)NOT NULL,
     salepersonname  VARCHAR(100),
     saleproductname VARCHAR(100),
     saledate        DATETIME NOT NULL
  )
ON schema_sale(saledate)

--Insert some Sample Data
INSERT INTO dbo.customersale
            (salepersonname,
             saleproductname,
             saledate)
VALUES      ('Aamir',
             'TV',
             '2013-02-01'),
            ('Aamir',
             'TV1',
             '2013-02-02'),
            ('Aamir',
             'CellPhone',
             '2013-07-20'),
            ('Raza',
             'Computer',
             '2014-02-01'),
            ('Raza',
             'Ipad',
             '2014-11-11'),
            ('John',
             'TV',
             '2013-05-05') 

SSIS Package Part:

Now we have the sample data in our partitioned table and we are all set to start developing our SSIS Package that can create multiple flat files from our partitioned table.

Step 1:

Open BIDS or SSDT and then create SSIS Project. Inside SSIS Project create an SSIS Package.

Create variables as shown below

 Fig 1: Create variables in SSIS Package

FolderPath:

This variable will have folder path where do you want to create flat files.

PartitionList_Obj: 

This variable will be used to save the partition numbers from partition table which has records.

PartitionNumber:

This variable will be holding the value of a partition number in foreach loop container and we will pass this to OLE DB query parameter.

Step 2:

Bring the Execute SQL Task to Control Flow Pane and configure as shown below to read all the partition numbers from Partitioned Table which has records.

We will be using below query to get the partitions from table

 SELECT partition_number
FROM   sys.partitions
WHERE  Object_name(object_id) = 'CustomerSale'
       AND rows > 0

 Fig 2: Configure Execute SQL Task to read partition numbers into Object Type variable

Map the result set to Object Type variable

 

Fig 3: Map the results to PartitionList_Obj variable

Step 3:

 Bring the Foreach Loop Container.We are going to read the records from Object type variable by using ADO Enumerator and then save the value in PartitionNumber variable on each iteration and then use PartitionNumber variable in OLE DB source parameter.

Fig 4: Read from Object Type variable in Foreach Loop Container

Map the Partition Number to PartitionNumber variable in Foreach Loop Container.

 

Fig 5: Map the variable in Foreach Loop 

Step 4:

Bring the Data Flow Task inside the Foreach Loop. Open the Data Flow Task and then bring OLE DB Source and configure as shown below.

 We will be using this query inside OLE DB Source and map the PartitionNumber variable to it.

SELECT *
FROM   dbo.customersale
WHERE  $partition.Parfunction_sale(saledate) = ? 

 Fig 6: Read data from Partitioned table per partition at a time by using Parameter

Bring the Flat File Destination and then create Connection manager for it and map the source columns to it. You can name anything you like as we are going to overwrite the connection string in next step.

 Fig 7: Flat file destination connection manager with column mapping

Step 5: 

As we want to generate file for each partition, we need to build expressions to create unique name for each file created per partition. I am going to have file name = "Customer_PartitionNumber.txt".

Right click on Flat File Connection Manager and go to properties. Then go to expressions and choose ConnectionString under property and provide expressions as shown below.

Expression used in fig.

@[User::FolderPath]+"Customer_"+ (DT_STR,1,1252)@[User::PartitionsNumber]+".txt"

Fig 8: Write Expression on Flat File Connection Manager to generate new file name with partition

Run the SSIS Package and see if files are create per partition in our given folder path.

 

Fig 9: Complete SSIS Package to read file per partition from Partitioned Table 

As we can see that two files are created and they have partition number added to them. Also the data belong to the partition.

Fig 10: Flat files created according to the partition number by SSIS Package

Summary or Items in this post we learn:

• How to create Database with multiple Data files in SQL Server
• How to create Partitioned Table in SQL Server
• How to create Partition Function and Partition Scheme in SQL Server
• How to use Execute SQL Task to load data into Object Type variable
• How to read data from Object Type variable by using Foreach Loop Container
• How to map variables in Foreach loop Container in SSIS Package
• How to use Data Flow Task to read data from OLE DB Source with Parameter and create flat file.
• How to write expressions on Flat file connection manager to generate files with dynamic name
• How to create and use variables in SSIS Package