question

Mrs_Fatherjack avatar image
Mrs_Fatherjack asked

SSIS - Variable driven connection string

I wonder if anyone can help. I need to create a connection string that will get it's database and server details from a table. I have created variables, populated them, returned the details to check they were being created correctly and then passed them to my connection string as below: > "Data > Source="+@[User::UserServer]+";Initial > Catalog="+ @[User::UserDatabase] + > ";Provider=SQLNCLI10.1;Integrated > Security=SSPI;" However I am getting the error message: ![alt text][1] [1]: /storage/temp/1120-connectionstringerror.jpg However, if I hard code the database name into the variable it works fine. Can anyone see what I've done wrong because I'm pretty sure it's something really stupid. Many thanks.
ssisvariablesconnection-string
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Koen_Verbeeck avatar image
Koen_Verbeeck answered
Did you set DelayValidation to true on the package? Why don't you use the Server and Database variables on the individual properties instead of the entire connection string? Much easier :)
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The DelayValidation fixed it, many thanks. I haven't used the Server and Database properties on the individual properties as they will change during the package, would it still work?
0 Likes 0 ·
Never tried with a database connection (usually Excel or Flat File connections), but I assume it would.
0 Likes 0 ·
@Mrs_Fatherjack it will work the same. I normally do the server and database properties now.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.