How can I use a Variable for a OLE DB Connection i

Question

Is there a way to use a user defined variable to define a connection string for an SSIS OLE DB Connection?

1) I had originally set it up to use a package parameter.

2) I execute the SSIS Package from a file using xp_cmdshell in a SQL stored proc, but found out that package Parameters are read only.

• I got this error ... "Changing the Value of a variable failed because it is a parameter variable. Parameter variables are read-only"

3) So it looks like I need to use variables... However, I don't see a way to use a variable for the connection string. When I use the 'Parameterize' option for the connection, it only gives me the option to select a parameter, not a variable.

Any thoughts? Thanks!

Answer 1

I am sure from the comments that you are going to get a lot of advice but you asked how do you use a variable as a connection string so to answer that part. It is fairly easy,

• add the Package Variable (right click in control flow on empty space and choose variables will bring you where you can add one)
• Choose the Connection Manager and then go to properties (I use right click properties)
• click in Expressions and then the ... button
• select "ConnectionString" in Property and set your expression to your variable or whatever you desire.

Answer 2

Yes, Matt's answer is correct. You can use a user defined variable in expression of the connection manager.

This has a limitation. This variable will work only for this package only.

You should make a project parameter instead of a user defined variable So that you can configure any of your package of the project in future also.

You can move your connection manager at project level if the same connection is being used for multiple packages.