8

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?

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
CodeKingPlusPlus
  • 15,383
  • 51
  • 135
  • 216

2 Answers2

16

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)

enter image description here

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

stb
  • 3,405
  • 2
  • 17
  • 24
  • Could you elaborate on what is possible with expressions? You also might want to check out my next question. – CodeKingPlusPlus Jul 30 '12 at 13:47
  • What if I used ExcelFilePath instead of the connection string? What is the high level process? For example, I had to set my connection managers to a default file. Once the files are dropped into the folder, a script sets the values of my file path variables. My excel connection managers each have an expression that associates the ExcelFilePath with the respective variable. **How does the connection manager work with the ExcelFilePath**? It is currently not working correctly. – CodeKingPlusPlus Jul 30 '12 at 19:59
  • See this: http://bidn.com/blogs/kylewalker/ssis/997/setting-up-an-ssis-package-with-a-dymanic-excel-source You basically just do the same and add the script you told above to set the ExcelFilePath. This way you should be fine. – stb Jul 31 '12 at 06:58
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.

enter image description here

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

cfrag
  • 726
  • 6
  • 12