SSIS Dynamic Flat File Connection

Introduction

if someone tells you "How create a SSIS package which extract data from flat file source and load it into a SQL server table".

This is pretty simple tasks if we take the static flat file connection. But here is a twist wearing for us.  To understand it please read the case scenario carefully.

Case Scenario

Think about a situation where we have a folder and a text file is copied in that folder every day and we have to load the text file in our SQL Server database Table. The text file contains coma separated records like Student ID, Student Name, Subject, City, State, Country. The name of the txt file is [ LoadedRecord ] + [ Day of Month ] + [ Month of Year ] + [ Year ]. Example of the file name is "LoadedRecord38042013.txt" and it is loaded in the fixed path "F:\Practice_SQL\SSIS\SSIS Examples\Dynamic Connection" in our case.

The Sample of Txt File

Here our motto of demonstrating the Connection string expression builders of a flat file connection in the connection manager only.

How to Do that

Step-1 [ Creating SSIS Package level variables ]

In the SSIS Package first we create a variable describe bellow.

Name        : v_Path

Scope       : Package level

Data Type : String  

Value        :  F:\Practice_SQL\SSIS\SSIS Examples\Dynamic Connection

Step-2  [ Configuring the Flat file Source  ]

Drag a Data Flow Tasks in control flow containers. Right click and Edit the control flow tasks. In the data flow tab drag a flat file source and right click it and select edit, the flat file source editor window appears.

Step-3 [ Configure the Flat file Connection Manager Editor ]

Now we configure the Flat file connection manager editor.

Step-3 [ Configure the Property expression of the Flat file connection ]

Now we configure the Property expression of the Flat file connection within connection manager. To do this right click the Flat file connection on the connection manager then selects the property. In the property window select the Expression and click the ellipse button. The property expression editor window appears.

In the Property Expression window there are two sections. For the Property select the ConnectionString and click the expression the Expression builder windows appear.

Step-4 [ Expression Builder ]

In the Expression Builder window type the following expression in the Expression section and click the Evaluate Expression Button to evaluate it.

The expression is

@[User::v_Path] + "\\LoadedRecord"+(DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() ))+(DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate() ))+(DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() ))+".txt"

To save the Expression, click on OK and this expression will now be saved with your connectionString property and be resolved automatically each time it is run.

Step-5 [ Understand the Expression ]

The fixed path of the text file is "F:\Practice_SQL\SSIS\SSIS Examples\Dynamic Connection" and we put it into the variable named @[User::v_Path]

The file name start with a common string LoadedRecord so + \\LoadedRecord

Next the file name contains the Day+Month+Year

For day : (DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() ))

For month: (DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate() ))

For Year:  (DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() ))

Last the extension of text file +".txt"

Hope you like it.