I'm facing issue with using OLEDB connection in my SSIS Packages. My SSIS package works only with ADO.Net connection, if I start using OLEDB connection it is unable to resolve the CNAME and cluster name. So I migrated my SSIS package with ADO.Net connection. But Now I realized the Package configuration which is coming from database is using OLEDB connection. The package configuration has option like SQL Server, XML, Environment Variable, etc.

I need to read my package configuration from SQL Server, but I cannot use the OLEDB connection to connect the SQL server. The User Interface in VS 2017 is not providing me the ADO.Net option to select the connection. So s there any way I can bypass OLEDB connection and use the ADO.Net connection from SSIS to read the package configuration. Please help me if any one faced this issue or came across any workaround.

CodePudding user response：

You cannot use an ADO.NET (or ODBC) connection manager with the Package Deployment Model's Package Configuration. The product does not support it.

You can see in the Package Configuration Wizard, selecting the SQL Server configuration type prompts for a connection. Clicking New... launches the Configure OLE DB Connection Manager screen and New in that menu brings up the Connection Manager with a provider of Native OLE DB\SQL Server Native Client 11.0 (or whatever OLE DB compliant driver you want to fiddle with)

Given that you're reworking your packages to use ADO.NET drivers to address a cluster/CNAME issue, you might verify you have the latest and greatest version of the OLE DB Drivers for SQL Server (and apply that on the server as well).

Also, unlike my screenshot, MS advocates for using the Microsoft OLE DB Driver for SQL Server (SQLOLEDB) instead of SQL Server Native Client OLE DB (SQLNCLI)

The SQL Server Native Client OLE DB (SQLNCLI) remains deprecated and it is not recommended to use it for new development work. Instead, use the new Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) which will be updated with the most recent server features.

Given that, you might try MSOLEDBSQL and see if that fares any better.

If none of those work, and you're unable to upgrade to the Project Deployment Model and all the goodness it delivers, and I didn't have too many properties being set via the [SSIS Configuration] table...I'd evaluate building a dynamic list of values for the package to run. I am long out of the SSIS game, but running a package boils down to

dtexec.exe /file C:\MyPackage.dtsx

If I wanted to change the ServerName property for a connection manager named ".\dev2017", I could use/abuse the /SET property of dtexec to do so

dtexec.exe /file C:\MyPackage.dtsx /SET \Package.Connections[.\dev2017].Properties[ServerName];SomeOtherServer\NamedInstance

That path, \Package.Connections[.\dev2017].Properties[ServerName], ought to be what's stored in the configuration table. So, instead of a SQL Agent task running an SSIS package, run a SQL Command which will use xp_cmdshell to build out a dtexec using the values configured in your table. Ain't pretty, likely to run into issues with xp_cmdshell and double escaping values but it's about the only approach I can think of if you can't resolve the OLE DB connection issue and still get dynamic, table managed package parameters.

If your package config connection string is a ADO.Net string then you need to create a ADO.Net connection with the same name as the stored package config name

If your package config return a OLEDB connection string you wont be able to use it in a ADO.Net connection, in this case you need to update your package config table with ADO.Net connection strings