SSIS Dynamic Connections Part 1

One of the common questions that I see is, “How do I make a connection dynamic?” Well there are two different ways I typically see the need for dynamic connections. The first is for different environments and the second is to actually programmatically change the connection to load multiple files.

So the first one is rather simple and the best way to implement different connection properties per each environment is to use Configuration Files (I’m not covering the SQL 2012 options in this post). Typically what I recommend there is:

  1. Create Variables to store ServerName, DatabaseName, Username and Password
  2. Enable Configuration files for your package
  3. Map the Variables to the Configuration Files

First to setup this example I set up package that has one connection in the connection manager called DemoSQLConnection and one package called ConnDemo.

To create Variables first make sure you can see the variables pane, you can do that by right clicking on the background of the Control Flow Tab and selecting Variables.


Alternatively you can go to the Menu bar and select View  Other Windows Variables


Once you can now see your variables window let’s now create Variables for our Connection.

  1. DemoSQLServer – Used to store the server name to connect to
  2. DemoSQLDatabase – Used to store the database name to connect to
  3. DemoSQLUserName – Used to store the User Name for the connection
  4. DemoSQLPassword – Used to store the Password for the connection

Make sure you always have your variables in the correct scope and have the proper data type, for this the scope should be at the package level and the type should be string. I have also put in some default values for the variables.


If you are using windows authentication for your connection you can skip 3 and 4. (Remember the Windows user running the package must have access to the database)

Now that I have created the Variables I can enable the Configuration Files and map the variables. To do this you can right click on the background and select Package configurations or go the Menu and select SSIS Package Configurations



Once selected a window should open and you want to select the checkbox for Enable package configurations and click Add


For this example I am going to add a XML Configuration File, name it democonfig and place it in the c: drive


Click next and then add your variables to the Configuration file. You might wonder why variables and not just put the connection string in the config, two reasons I do this. First is because sometimes you create different connection types to the same server such as an OLEDB and a SQL Destination and it’s easier to just map the variables to the connection strings and define once than it is to modify both connection strings. The second reason is if the password is in a variable you can encrypt that variable (I won’t be discussing how to do that in this post)


Click next and then Name the Configuration and click Finish.


Now one last step is to map the Variables to the connection string. I prefer to do this with a variable. Create a Variable called DemoSQLConnectionString and in the properties window change the Evaluate as Expression to true and click the icon in the Expression property.


Set the Expression to

“Data Source=”+ @[User::DemoSQLServer] +”;User ID=”+ @[User::DemoSQLUserName] +”;Provider=SQLNCLI10.1;Initial Catalog=” + @[User::DemoSQLDatabase] + “;Password=” + @[User::DemoSQLPassword]

And then press Evaluate Expression to test it.


One last step now and that is set connection string of the connection to the variable. To do this we do the following. We select the connection from the connection manager and edit the expression property of ConnectionString and set it to the @[User::DemoSQLConntionString] variable.


Now you can test this by creating a Data Flow and using this connection and if you want to test to make sure it’s all working correctly go and change the password or server in the configuration file and the connection should fail.

Now as for the second way of dynamically setting connections during run time. That will be part two of this as this has already got a little on the long side.

PART 2

SSIS Dynamic Connections Part 2

SSIS Dynamic Connections Part 1

If you didn’t see part one I suggest you click the link above as I will be continuing from there.

For Part 2 we are going to show how to create a connection and then change the properties of the connection to process multiple files. So to set this up I created a Table called FilesToProcess in a database called ConnDemo.

Create
Table FilesToProcess

(

FileName
nvarchar(50),

FileLocation nvarchar(150)

)

With the following values.

I also created a table called StgImportFiles to import the files into.

CREATE
TABLE StgImportFiles

(

Col1 NVARCHAR(50),

Col2 NVARCHAR(50),

Col3 NVARCHAR(50)

)

What we are going to do is query this table to get all the files to process and then process each File by inserting them into the same table. Now remember to do this all the files have to have the same format.

I am going to expand on the Package from Part1 and just change the database name in the configuration file to be ConnDemo and then I will doing the following tasks.

  1. Add variables to store the file location and name as well as the return values of the query.
  2. Add Execute SQL Task to get the values from the FilesToProcess Table
  3. Add Foreach Loop Container to process the files
  4. Add DataFlow task to import the data.

The variables I am going to add are

  1. FileLocation – Store the Location of the File
  2. FileName – Store the Name of the File
  3. FileListResults – note that this is a Data Type of Object as this is required

Now I am adding an Execute SQL Task and Naming it “Get Files To Process”, setting the connection to be the DemoSQLConnection, setting the ResultSet to Full result set, then adding the following sql statement.

select FileLocation,FileName

from FilesToProcess

Then add a Result set and map it to the FileListResults variable. Your Execute SQL task should look like below and You must set the Result Name to 0 as that is the position of the result set.

Now we add the Foreach Loop Container and add the collection with enumerator “Foreach ADO Enumerator” and the map to the FileListResults variable. Then add the variable Mappings of FileLocation and FileName and should look like the screen shots below.

Next we add a Data Flow inside of the Foreach Loop and Create a File Connection Manager called Importfiles we will point this initially to one of the files we want to import so that we can properly set the format of the files.

Now that we have the Source File we can modify the connection string expression of the File to be @[User::FileLocation] + @[User::FileName]. This is what will now change the path of the file as each row of the returning query goes through the Foreach Loop.

The last step is to add to the DataFlow a Source pointing the File Connection Manger Imporfile and a destination pointing to the DemoSQLConnection and selecting the StgImportFiles table. Your package should now look like the below.

If you run the package the values in the StgImportFiles table should be there.

This process can be very useful if you receive multiple files in the same format that need to be imported and don’t want to write separate packages for each file. I have used this approach where we needed to import files for over 200 customers and the layout was a standard one that we had created and I only created one package to import all 200 files.