Modifying the Flat File Connection Manager
In this task, you will modify the Flat File connection manager that you created and configured in Lesson 1. When originally created, the Flat File connection manager was configured to statically load a single file. To enable the Flat File connection manager to iteratively load files, you must modify the ConnectionString property of the connection manager to accept the user-defined variable User:varFileName, which contains the path of the file to be loaded at run time.
By modifying the connection manager to use the value of the user-defined variable, User::varFileName, to populate the ConnectionString property of the connection manager, the connection manager will be able to connect to different flat files. At run time, each iteration of the Foreach Loop container will dynamically update the User::varFileName variable. Updating the variable, in turn, causes the connection manager to connect to a different flat file, and the data flow task to process a different set of data.
To configure the Flat File connection manager to use a variable for the connection string
In the Connection Managers pane, right-click Sample Flat File Source Data, and select Properties.
In the Properties window, for Expressions, click in the empty cell, and then click the ellipsis button (…).
In the Properties Expressions Editor dialog box, in the Property column, type or select ConnectionString.
In the Expression column, click the ellipsis button (…) to open the Expression Builder dialog box.
In the Expression Builder dialog box, expand the Variables node.
Drag the variable, User::varFileName, into the Expression box.
Click OK to close the Expression Builder dialog box.
Click OK again to close the Property Expressions Editor dialog box.