SSIS Paramaterized ADO.net Connection Manager

Question

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.

Answer 1

Hi @Joshua West ,

1.We should put username and password in Connection Manager and test connection successfully in Connection Manager.

2.Please check if you deploy the whole ssis project( with project parameters)to ssis catalog.

3.We can create environment variable in ssis catalog to store value of parameter.
Please refer to Setup Environment Variables in SQL Server Integration Services.

Best Regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.