Using ADO.Net Connection Manager with Parameterized Credentials in SSIS

1
votes

I'm working on an SSIS package to extract data from one old database and move to a SQL database. The only way to connect to the source database is using ODBC. I set up an ADO.NET connection manager and project parameters to store the username and password. Then I went to the connection and chose "Parameterize...". I selected username and password and linked to the project parameters I set up. The connection continues to fail. The only way I've been able to get it to work is to allow the package to save sensitive data and to save the password in the connection manager (going to edit, typing in username and password, then saving). If I rely on the parameters it does not work. I also tried deploying to the SSIS catalog, then executing by going to the package and entering the credentials under the Connections Managers tab. The only way I've been able to get this to work is to allow it to save sensitive data, enter credentials directly in the package and then save.

I cannot figure out why it will not pass the username and password from parameters or with the connection manager when executing in SSMS. The error I get back is that it's missing the password.

Any help would be greatly appreciated! I'm not new to SSIS, but have tried everything and can't seem to make this one work. I'm stuck using ADO.NET and ODBC.

1
sql-serverssis
"The connection continues to fail." What does fail mean? What error do you get? Do you see the connection attempt on the other instance? What does its logs say about the connection attempt? – Larnu
When I right click and choose "Test Connectivity" on the connection manager it just immediately goes to offline mode. When I try to execute I get a message that the required credentials username and pwd are missing. I'm not sure how to get any more log data out of it. – J West

1 Answers

1
votes

Few things to do:

  • Set the SSIS package ProtectionLevel to SaveNoSensitive.
  • Go to the connection and chose "Parameterize..." and select the entire connection string.
  • Assign proper values to Project level parameter that is used to parameterize the enire connection string.