Dynamic Flat File Source – SQL Server Integration Services 2012

No Comments

Written by Luis Miguel – Originally Published on Jan. 13, 2015

Requiremts: Import/load data from a CSV file into SQL Server on a daily basis, the CSV file has a file name pattern that consists of the name of the target table and a date stamp with the format of yyyymmdd, for example “product_20150111.csv”.

The CSV file

product_yyyymmdd.csv

Step by step instructions

1 – Create an OLE DB connection to the destination table

OLE DB connection

2 – Add a string variable that will hold Flat File Source name

String variable to build the dynamic file name

3 – Create a Flat File Connection to the source CSV file

Flat file connection

3a – Select the Flat File Connection to retrieve its properties
3b – Click the ellipses button next to Expressions
3c – Set the property Connection String to the expression @[User::product_yyyymmdd]

Set the Connection String property to an expression

4 – Add an Execute SQL Task that will build the Flat File Source name

4a – Set its properties as follows
4ai – General – SQL Statement: SELECT ‘c:\data\product_’ + CONVERT(VARCHAR(10), GETDATE(), 112) + ‘.csv’
4aii – General – Result Set – Single Row
4aiii – Result Set – Result Name: 0 – Variable name: User::product_yyyymmdd

Execute SQL task

5 – Add a Data Flow Task

Add a Data Flow task

5a – Add a Flat File Source to the data flow task and point its connection manager to the flat file source created previously

Flat File source

6 – Add an OLEDB Destination

OLE DB destination

The Data Flow task

The Data Flow task

A successful execution

Package execution result

The table after the data from the CSV file was imported

SELECT * FROM products

Powered by Netfirms