SSIS with variable excel connection manager

Question

I am trying to do automatic package execution with a WMI Event Watcher Task within SSIS. The functionality I want is automatic package execution when excel files are dropped into a certain folder. However, these excel files will be the connection managers for populating a database.

Currently SSIS will not allow me to do this because my excel connection manager does not have a path when I run the program, and only exists once the files are dropped in the folder.

Is there a way for variable excel connection managers or the value of the connection string to be a variable?

Also, how do I implement the usage of this variable in an expression?

Answer 1

You can use a variable for the connection string of you excel source:

1. Click on your Connection manager of your excel source
2. In properties window, add an expression(1) ConnectionString(2) and assign a variable(3)

There are alot of different things you can do with variables. They are used alot in combination with for each loop containers and file system tasks. Your normally do something like this

1. create a variable in variable window
2. Set a static value or one that gets changed during the package flow
3. Map the variable to an expression

There are alot howtos on the web, maybe have a look at this to get warm with it:

http://www.simple-talk.com/sql/ssis/working-with-variables-in-sql-server-integration-services/

http://www.rafael-salas.com/2007/03/ssis-file-system-task-move-and-rename.html

Answer 2

The fastest way i know to achieve this is by creating an excel connection manager and setting its connection string through a variable. In order to do so you will need to make the connector first by pointing it to an excel file. It doesn't matter which, since you will be dynamically setting the new file in runtime. Then, select your excel connection manager and check its properties. You have a ConnectionString property, which you can set through an expression.

However, you must make sure that your package will only use the Excel Connector after it has been filled with the correct connection string! For further information on SSIS variables check this link: Variables in SSIS