Hi Experts

 Can some one explain me or provide me an example of the topic "variables in SSIS for setting any connection string". I tried to Google it but cannot find any specific link. Thanks

Regards

Muz

Hi Muz,

I cannot understand exactly what your requirement is, can you provide more details?

If you are trying to parameterized a connection string using variables is an easy task, but what I cannot understand and I think is the key in your question is the "any"

Kind Regards,

Paul

Hi Paul

Thanks for the reply, you are right, I mean to say "How to Parameterized a connection string using variables". Please ignore any in my post. Thanks

Regards

Muz 

Hi Muz,

If I have correctly understood that's what you need to do:

1. Determine which attributes of the connection string you want to parameterized, i.e., server name, initial catalog, username, password, etc.

2. Create a SSIS Variable for every attribute that you want to parameterized. i.e., ServerName, InitialCatalog:

3. Create a connection (OLEDB, ADO.net or what you want to create), and then using the expresion editor (in the properties windows open the expression editor in the Expression properties elipsis button) and assign the variable to the corerspondent property:

4. You may want to create a package configuration to assing the value of these variables outside the package, there are several option to accomplish this task.

Please also note that you can configure the connection string directly in the expression editor (selection the property ConnectionString instead of ServerName and InitialCatalog), that is, you can with write with a single variable the value of the whole connection string.

Please don't forget to mark my reply as the answer if you consider it.

Kind Regards,

Paul

• Marked as answer by muzokh Thursday, March 20, 2014 5:27 PM

What happens when your InitialCatalog property disagrees with InitialCatalog within the ConnectionString property? It took me awhile to find out that the value in ConnectionString takes precedence. I suppose the same is true for ServerName too, that it's overridden by Data Source in ConnectionString. I'm talking about configuring a project connection manager within a deployed project in SSISDB. Not sure that's exactly the same thing. 

Ken