SSIS Paramaterized ADO.net Connection Manager

Joshua West 1 Reputation point
2021-01-22T15:24:17.06+00:00

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.

.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,438 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,462 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Monalv-MSFT 5,896 Reputation points
    2021-01-25T10:01:49.887+00:00

    Hi @Joshua West ,

    1.We should put username and password in Connection Manager and test connection successfully in Connection Manager.
    60223-adonetcm.png

    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.
    60164-configurewithenvironment.png
    60165-executepackagewithenvironment.png

    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.