4

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!

Zach
  • 640
  • 1
  • 6
  • 16
  • So you basically want to do [this](http://www.sqlchick.com/entries/2015/1/4/parameterizing-connections-and-values-at-runtime-using-ssis-environment-variables)? – Mike G Aug 10 '16 at 15:55
  • I have that exact set up in my local environment. However, in Dev, Qa, Prod, etc... we have to jump through a bunch of hoops to get the Integration Service setup in SQL server. Currently, all of my company's SSIS pkgs are copied out to a directory, and executed using xp_cmdshell. The issue is, none of the current ones need dynamic connection strings so this has not been an issue until now. So, I am having to figure out a way to make it work with the current setup. This is on a tight time frame, and the approval process of getting changes to the SQL servers will take too long... Make sense? – Zach Aug 10 '16 at 16:02
  • If you just have to specify connection strings on a per-environment basis you can do that through SQL Server once the package has been deployed. (i.e., it's an configuration setting not something that's set in the code) – Mike G Aug 10 '16 at 16:05
  • This is executed from a users local environment. They will be specifying a target 'Staging' environment to copy some DBs to. So, having set values in place based on where the package is deployed will not work. It has to be dynamic so the user can pass in a connection string. – Zach Aug 10 '16 at 16:08
  • What version of SSIS are you using? – Tab Alleman Aug 10 '16 at 17:37

2 Answers2

6

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.

enter image description here

Matt
  • 13,833
  • 2
  • 16
  • 28
  • When I tried to go this route, I was using the "Parameterize" option in the connection manager. There was no option to use a variable, only package parameters. Doing it your way allowed me to do exactly what i needed to do. Thanks! – Zach Aug 24 '16 at 13:31
1

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.

Paras Sharma
  • 55
  • 2