[SQL] Connection Manager in SSIS task: Keyword not supported: 'ProviderName'

When I'm creating an ADO.NET connection from connection managers of the solution explorer in VS2022, it's all fine. But when I wanna create a connection in the data profiling task of SSIS toolbox, I get this error:

TITLE: Connection Manager Test connection failed because of an error in initializing provider. Keyword not supported: 'ProviderName'.
​

As you can see in the picture (right hand side) I have managed to create this same exact connection in connection managers with similar parameters, however with .Net Providers\Microsoft SqlClient Data Provider As Provider name and if I choose .Net Providers\SqlClient Data Provider I get the above error again. The problem is in the connection manager of SSIS task, I cant change the provider as it's greyed out. Can you help me fix this issue?

Continue reading...