Dynamically set Flat File Connection Manager

Hi,

In the SSIS forum very often there are questions on dynamic name for a flat file connection manager

I have split it in two parts:

• A single file name needs to be set up dynamically
• Multiple file names need to be set up dynamically per execution.

In this post I will talk about the first scenario.

Scenario:

For simplicity sake I will be using a case where the file name is saved in a DB table.

Need to fetch the file name from a database table.

Need to use a Data Flow Task which has a Flat File Source.

This flat file source needs the file name to be set up dynamically at run time.

The folder for the file is fixed.

Create a string Variable: FileName with the scope as the package(Practice in my case). Set the value of the variable to a file name that exists (I set it to Demo.txt) just while development.

Now set up the Flat File Connection Manager for the above file that exists and has the same meta data as the file that will be used later.

File name: Click on the Browse button and select the file.

I am using the other default settings.

Click on the Columns tab to verify the columns.

Then click OK.

To set up the Execute SQL task to fetch the file name and save it to a user variable FileName.

In the Execute SQL task Editor window go to the General tab.

Do the following settings:
ResultSet : Single row.
ConnectionType : OLEDB(that’s what I have used you could also use ADO.NET)
Connection : Select the appropriate one.
Put the query to get the single file name from the table.

Now select the Result Set tab

Click the Add Button.

You will see the below screen with a row for the result details.

In the Result Name set it to : 0 (Numeric zero)

In the Variable Name select the string type variable which has the scope set to the package.

Once this is done click OK.

Now select the Flat File Connection Manager 1 and click "F4" or right click and select “Properties”.

In the properties pane for the above connection manager:

Set the DelayValidation to True

Expand the Expressions by clicking the ellipse.

On the Property Expressions Editor window:

In the Property column select the ConnectionString property from the drop down list.

Now expand the Expression column by clicking the ellipse. An Expression Builder window will pop up.

On the Expression Builder window:

Set the Expression as: “C:\\Data\\” + @[User::FileName]

Click the Evaluate expression button at the left hand bottom to see if the expression is creating the desired output.

Click OK.

Click OK on the Property Expression Editor as well.

Now set up the Data Flow Task as below:

Double click the Flat File Source.

In the Flat File Source Editor:

In the Connection Manager tab, select the appropriate Flat file connection manager from the drop down list.

Now in the OLEDB Task:

Select the OLEDB Connection needed to connect to the destination Data base.

Select the table where the columns need to be inserted.

Goto the mapping

Go the mapping and do the mapping as required and click OK.

Save and build the package.
Now when you execute the package the package will take the filename from the DB table.

This could also be modified by having the file name stored directly to a variable and make this variable configurable.

Very soon I will be posting on setting up file names dynamically for multiple files.

Please provide your valuable feedback.