SSIS软件包配置使用SQL Server Ado.net连接
我在SSIS软件包中使用OLEDB连接面临问题。我的SSIS软件包仅适用于Ado.net连接，如果我开始使用OLEDB连接，则无法解析cname和cluster名称。因此，我使用ADO.NET连接迁移了SSIS软件包。但是现在我意识到来自数据库的软件包配置正在使用OLEDB连接。软件包配置具有SQL Server，XML，Environment变量等选项。
我需要从SQL Server读取我的软件包配置，但是我无法使用OLEDB连接来连接SQL Server。 VS 2017中的用户界面并未为我提供选择连接的ADO.NET选项。因此，我可以以任何方式绕过OLEDB连接并使用SSIS的ADO.NET连接来读取软件包配置。如果有人遇到这个问题或遇到任何解决方法，请帮助我。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您不能将ADO.NET（或ODBC）连接管理器与软件包部署模型的软件包配置一起使用。该产品不支持它。
您可以在“软件包配置向导”中看到，选择连接的SQL Server配置类型提示。单击新...启动配置OLE DB Connection Manager屏幕和该菜单中的新菜单将带有本机Ole db \ sql Server本机客户端11.0（或您想使用的任何OLE DB的驱动程序）的提供商来提出连接管理器
。 a href =“ https://i.sstatic.net/5na85.png” rel =“ nofollow noreferrer”>
鉴于您正在重新加工包装以使用ado.net驱动程序来解决群集/cname问题，因此您可能会验证您拥有SQL Server的Ole DB驱动程序的最新，最出色的版本（并应用也在服务器上）。
另外，与我的屏幕截图不同，MS倡导者使用
Microsoft Ole db驱动程序进行SQL Server（SQLOLEDB），而不是sql Server ole db（sqlncli）鉴于此，您可以尝试MSOLEDBSQL，看看是否会更好。
如果这些都没有工作，并且您无法升级到项目部署模型以及它提供的所有优点，并且我没有通过[SSIS配置]表设置太多属性...我会评估建立一个动态的值列表，以便包装程序运行。我长期以来已经超出了SSIS游戏，但是
如果我想更改名为“。\ dev2017”的连接管理器的servername属性，我可以使用/滥用
/setdtexec的属性是这样做的，\ package.connections [。\ dev2017] .properties [servername]，应该是配置表中存储的内容。因此，不用运行SSIS软件包的SQL代理任务，而是运行SQL命令，该命令将使用XP_CMDShell使用表中配置的值来构建DTEXEC。这不是很漂亮，很可能会遇到XP_CMDShell和Double Exing值的问题，但是如果您无法解决OLE DB连接问题并仍然会产生动态，表托管的软件包参数，这是我能想到的唯一方法。
https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with- sql-server-native-client？view = sql-server-ver16
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 ofSQL Server Native Client OLE DB(SQLNCLI)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
If I wanted to change the ServerName property for a connection manager named ".\dev2017", I could use/abuse the
/SETproperty of dtexec to do soThat 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.
https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client?view=sql-server-ver16
在“连接”窗口中，右键单击并选择要构建的连接类型时，
您可以创建多个类型的连接，因此创建一个以将您的软件包读取为OLEDB，然后将其他连接创建为Ado.net。
如果您的软件包配置连接字符串是ado.net字符串，则需要创建一个与存储的软件包配置名称相同名称的ado.net连接
，如果您的软件包配置返回OLEDB连接字符串，则可以在ADO.NET连接中使用它，在这种情况下，您需要使用ADO.NET连接字符串更新软件包配置表
When in the connections window right click and choose what type of connection do you want to build
You can create multiple connections of different types, so create one to read your package config as OLEDB, then create the other connections as ADO.Net.
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