Hi all, 

In my project source is Oracle and I am using ODBC to connect oracle for lading.

I have create 2 project parameter for connection string one for connection and another for password

when I am making expression on ODBC connection it is showing error like below

I can't establish a connection because our legacy driver doesn't support 'Password' as a connection string attribute.

when I am passing expression like @[$Package::V_Constring]+ "PWD=faster1" on odbc connection it working fine.

When I use just the ConnectionString property on the ODBC connection manager and use a 'pwd' attribute; all is well. E.g., "uid=<user>;pwd=<password>;Dsn=<dsn name>;". But as soon as I flip the sensitive attribute, I'm getting the classic error:

The expression will not be evaluated because it contains sensitive parameter variable...

The sensitive parameter is desired, of course. I don't want the password in the clear.

Any ideas?

T

Hi Vipin,

I believe the issue may be the "Protection Level" in the properties of the SSIS package.  If you have chosen the "Don't Save Sensitive" option, it will not save your password because that is considered sensitive information.  You would have to choose a different "Protection Level" or pass the password information for you connection string in a configuration.

actualy in my case I have to run the same package with multiple dsn.

like FROM dsn1 and dsn2 having same table emp in both

I want to passs the dsn name na dpassword from the project parameter.

Hi Vipin,

If I understand correctly, you get the error message that The expression will not be evaluated because it contains sensitive parameter variable when you are using project parameter which with Sensitive = True in expression.

Based on my research, this issue is caused by design. We couldnt use Expressions with sensitive parameters. To work around this issue, we can create two project parameters, one is ConString Project Parameter which contains "uid=<user>;Dsn=<dsn name>;", another is Pwd Sensitive Project Parameter which contains the password. Then set the two project parameters map to the ODBC ConnectionString property and Password property.

The following similar thread is for your reference:
https://social.msdn.microsoft.com/Forums/en-US/77e199fe-ef0c-406c-ad61-99b1077f5b30/ssis-2012-project-deployment-model-sensitive-password-parameter-into-odbc-connection-manger?forum=sqlintegrationservices

Thanks,
Katherine Xiong

Hi ,

Thanks for your reply, as you can see in screen shot I have created 2 Project parameter 1 for constring and another for passwsord.

but when I am  mapping to odbc connection I am getting red signal on ODBC  conection and unable to run package from BIDS.

I want to pass thses t value from outside when I will create job.

when executing the package I am getting belwo error

[ODBC_SRC_D_REST_LEVEL_XREF [15]] Error: The AcquireConnection method call to the connection manager
 CANDM failed with error code 0xC0014009.  There may be error messages posted before this with more
 information on why the AcquireConnection method 
call failed.

Please change the Run64BitRuntime property to FALSE to test the issue again.