CODEWITHSUNDEEP
sql-server-2008ssis
Dan
Dan

Reputation: 79

Dynamic ConnectionString in Connection Manager (OLE DB)

I have a local database table that contains 50+ external data sources from which I can build a connection string. I am currently trying to setup SSIS to dynamically build connection strings from this table and pass them to the connection manager. I have successfully set a variable and assigned this variable to the ConnectionString expression property in Connection Manager.

I then use an OLE DB source object to copy a table from this source to our local database. The problem is that if the connection string is not set at design time I get validation errors for the OLE DB object.

imgg

If I manually set the connection string property it works fine, but that defeats the perpose of creating the dynamic connectionstring variable.

The idea here is to have the dynamic partion detect what machine it is running from and build the proper string (DEV, UAT, QA). I also do not want these connection strings in a dtsConfig file.

I took a look at this: http://www.simple-talk.com/sql/ssis/working-with-property-expressions-in-sql-server-integration-services/, but it does not have any solution for my situation.

Upvotes: 0

Views: 3441

Answers (1)

Dan
Dan

Reputation: 79

I just found the solution after reading the first line of that error "Package Validation Error" I went to the package properties and changed DelayValidation to True.

I guess asking questions here really does help you find your own solution.

Upvotes: 1

Related Questions