CODEWITHSUNDEEP
ssisconnection-string
EricR
EricR

Reputation: 135

SSIS Configuration connection strings

I ran into a problem with some of our packages. The basic layout is the connection strings are stored in a SSIS Configuration table in the database.

I've noticed lately that the server name for my oledb connections seem to remain static. I have seen where I move the package from one environment to another and the validation fails. If I change the password to what is used in the first environment it validates.

Is there some other property besides the connection string that I need to store as well? I'm not using variables or expressions.

Has anyone seen this before? The server version in question is 2008 R2.

Upvotes: 2

Views: 2660

Answers (1)

agt
agt

Reputation: 301

This is likely because it's trying to validate using the connection string stored in the package itself and not the one provided by the configuration file. I know of two ways to get around this problem:

  1. Each connection manager and data flow task has a property called DelayValidation. When set to true, that property will prevent SSIS from trying to validate the connections and data flow tasks until after the configuration has changed the connection string. Trouble is, the default value is false, and you have to go through and set it on every single connection manager and data flow task. You'll also need to remember to to flip the setting every time you create a new one.
  2. You can either manually or programmatically change the actual value of the connection string in the SSIS package to match the configuration file before you deploy it. That admittedly does seem to defeat the purpose of having a configuration file in the first place, but it does ensure that it will work. This is the option I often end up taking. Before I move from my test environment to production, I pop open the package in a text editor and do a find/replace of the connection string. I've determined that to be safe for my packages, but, as with any hacky solution like that, your mileage may vary.

Upvotes: 2

Related Questions