Hi Team,
I have created SSIS project with some packages inside the project. Project has "project.params" parameters where i have parameterized the whole connection string.
My connection manager is using the parameterized connection string from variable.
Project.params
variable name : MY_DB_CONN_STRING
value : Data Source=MYSERVER\MYINSTANCE ; Initial Catalog=MYDB ; PROVIDER=SQLNCI11.1 ; User Id=testuser; Password=testpassword ; Auto Translate=False;
Problem: In above connection string if i am giving the password as plain text, the connection manager test connection is successful and the package is executing fine.
But when i am giving the encrypted password as below, the connection manager is not able to make the connection.
variable name : MY_DB_CONN_STRING
value : Data Source=MYSERVER\MYINSTANCE ; Initial Catalog=MYDB ; PROVIDER=SQLNCI11.1 ; User Id=testuser; Password= iCeE6v2qsdhksjdsjdgusGgUYuU/Utfreknjksdhksgdhsg; Auto Translate=False;
I am calling the .ispac file from a .bat file as below from command line and passing the project param connection string.
--------TestJob.bat--------------------------------------------
dtexec.exe /Project "d:\applications\mytestproject.ispac" /Package TestMain.dtsx
/SET \Package.Variables[$Project::MY_DB_CONN_STRING];\"" Data Source=MYSERVER\MYINSTANCE ; Initial Catalog=MYDB ; PROVIDER=SQLNCI11.1 ; User
Id=testuser; Password= iCeE6v2qsdhksjdsjdgusGgUYuU/Utfreknjksdhksgdhsg; Auto Translate=False;"\"
----------------------------------------------------
When passing the encrypted password in above command, the connectionmanager is not able to make the connection.
Please advice how to pass encrypted password from .bat file as a project param.