Using SQL Server Configurations to configure Flat File Connection Manager

The following is an example on how to use SQL Server based configurations to configure a Flat File Connection Manager’s ConnectionString property. We will store in an SSIS variable the directory that the Flat File Connection Manager will store/read its file to/from. The file name in this example will be based on another variable. We will use SQL Server configurations to dynamically manage the directory in this case, though you could/should do both.

• Create a new package.

• Create a new string variable, “Directory” and give it a default value of “C:\”

•  Create another new string variable, “FileName” and give it a default value of “test.txt”

NOTE: You don’t need the quotes when typing in the values for the above variables

•    Right click on the Control Flow, select “Package Configurations…”

• Check the box to “Enable package configurations”

• Click “Add…”

• Change “Configuration type:” to “SQL Server”

• Click on “New…” on the right of the “Connection:” line to create a new connection.
• This will bring up a new window, “Configure OLE DB Connection Manager”. Click “New…” here.
• Enter in the required information on this screen such as server (“localhost” in my case) and database name (“AdventureWorksDW” in my case). I am using “Windows Authentication”
• Click “OK”
• Click “OK” again

• Now we should be back to the “Package Configuration Wizard” screen. Click “New…” on the right of the “Configuration table:” line to create a new configuration table (if you don’t already have one created.)
• This brings up a “Create Table” screen. Leave the SQL alone, unless you want to change the name of the table it is trying to create. (Which I do — I replace the space with an underscore: “SSIS_Configurations”) Click “OK”
• Back on the “Package Configuration Wizard” screen, you can type in a “Configuration filter:” value. This is basically a WHERE clause when SSIS tries to query the table from above. On the next screen, we’ll get to choose which properties we’d like to configure via this method, so give this a unique name for this test package. I used “test_package”.
•   Click “Next >”

• Collapse all branches on this next window in the “Objects” pane. Make sure that nothing is checked. This is easy to do by checking the top level object (the name of your package) and unchecking it again. Expand the “Variables” sub-tree. Here you’ll see the two variables we created from before. Expand the “Directory” variable. Expand its “Properties” branch. Check the “Value” property under the “Directory” branch. At this point, you should only have one item checked.
• Click “Next >”
• Give this configuration a name. This is only used to help you identify which configuration is which in this package, as you can have more than one at any given time. I used “Directory Configuration” in this case. Click on “Finish”
• Click on “Close”

•           From here, we have the configuration setup, which will dynamically set the value of the “Directory” variable based on whatever value is in the configuration table. Now let’s set it up on a Flat File Connection Manager object.

·          

•  In the “Connection Managers” pane at the bottom of the window, right click and select “New Flat File Connection…”
•  Give it a “Connection manager name” of something descriptive. I used “My Test Flat File” and left the “Description” property blank.
• Type in the name of the file, fully qualified. I used “C:\test_placeholder.txt”
•  I used the default options of “Format: Delimited” and “Text qualifier: ” and “Header row delimiter: {CR}{LF}” and “Headers rows to skip: 0″
• Click on the “Advanced” tab on the left.
• Click on the “New” button at the bottom to add a new column. Click on it again to add a second column.
• Click “OK”

Now the Flat File Connection Manager has been setup, though it is not configured to use the variables from before. Let’s do that now.

•  Right click on the Flat File Connection Manager object in the “Connection Managers” pane and select “Properties”
•    Click on the “Expressions” property. If you have installed SQL Server 2005 Service Pack 1 or greater, you should now see some ellipses (…) on the right. Click on them.
• You should now have the “Property Expression Editor” showing. In the left side, click and select the “ConnectionString” property.
•   Click on the ellipses here as well to bring up the expression editor for this property.
• Use the following for your expression: @[User::Directory] + @[User::FileName]
• Click “OK”
• Close the properties window if you want.
• Now we have the Flat File Connection Manager object configured to use our variables, which will get their values from our database table. Let’s put it all together in a package we can run.

• Add a Data Flow task to the Control Flow
• Double click on the Data Flow task to edit it.
• Add an “OLE DB Source” component by dragging it to the Data Flow surface.
• Double click on the “OLE DB Source” component to edit it. The “OLE DB Connection Manager” property should already be populated for you and should be the “AdventureWorksDW” connection manager object we created from before.
• Change the “Data access mode” to “SQL command” as this is always good practice to do. This allows us to select only the columns from the database that we need and also allows us to add a WHERE clause if necessary. This is the only practice I recommend.
• Use the following SQL:
• SELECT DISTINCT FirstName, LastName FROM     DimEmployee
• Click on the “Columns” tab on the left. This populates the list of columns automatically that we want to expose to the Data Flow.
• Click “OK”
• Drag a “Flat File Destination” component to the Data Flow.
• Connect the two components using the green line from the “OLE DB Source” component.
• Double click on the “Flat File Destination” component to edit it.
• Again, just like the previous component, the “Flat File Connection Manager” property has been automatically selected for you.
• Click on the “Mappings” tab on the left to populate the mappings between the source and destination. Drag “FirstName” from the left to “Column 0″ on the right. Drag “LastName” from the left to “Column 1″ on the right.
• Click “OK”

Now, we’re ready to execute the package, but let’s understand a few things first. The value currently stored in the SSIS_Configurations table currently reflect the default value you established when you created the “Directory” variable. So, to show that this works, let’s change the value of the variable in our SSIS_Configurations table to something like “C:\TEMP\”. Also, make sure that you actually have a “C:\TEMP\” directory and that you have permissions to write to it. So the package has “C:\” as the value for “Directory,” but the SSIS_Configurations table has “C:\TEMP\”. You can update this value by running an UPDATE statement in Management Studio, or by opening the table directly by right-clicking on it in Management Studio. Either way, I assume you know how to do this.

Run the package! Note that when we initially setup the “Flat File Connection Manager” object, we gave it a file name of “test_placeholder.txt”. After the package executes, you should find a file, “test.txt” in the “C:\TEMP” directory, not the “C:\” directory as you have in your variable.

What’s going on here? Well, the package configurations are applied at run-time and configure the “Directory” variable to be the value stored in the table. We then use that variable along with the “FileName” variable to dynamically configure the “ConnectionString” property of the “Flat File Connection Manager” by using expressions.

Courtesy:www.ssistalk.com