SSIS如何从配置文件动态设置连接字符串
我在 SQL Server Business Intelligence Development Studio 中使用 SQL Server Integration Services (SSIS)。
我需要执行如下任务。我必须从源数据库中读取数据并将其放入目标平面文件中。但同时源数据库应该是可配置的。
这意味着在 OLEDB 连接管理器中，连接字符串应该动态更改。该连接字符串应取自配置/XML/平面文件。
我读到可以使用变量和表达式来动态更改连接字符串。但是如何从配置/XML/平面文件中读取连接字符串值并设置变量呢？
这部分我无法做到。这是实现这一目标的正确方法吗？我们可以将 web.config 文件添加到 SSIS 项目吗？
I am using SQL Server Integration Services (SSIS) in SQL Server Business Intelligent Development Studio.
I need to do a task that is as follows. I have to read from a source database and put it into a destination flat file. But at the same time the source database should be configurable.
That means in the OLEDB Connection Manager, the connection string should change dynamically. This connection string should be taken from a configuration/XML/flat file.
I read that I can use variables and expressions to change the connection string dynamically. But how do I read the connection string value from a config/XML/flat file and set the variable?
This part I am unable to do. Is this the right way to achieve this? Can we add web.config files to an SSIS project?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
首先向您的 SSIS 包（包范围）添加一个变量 - 我使用了 FileName、OleRootFilePath、OleProperties、OleProvider。每个变量的类型都是“字符串”。然后我创建一个配置文件（选择每个变量 - 值） - 填充配置文件中的值 - 例如：对于 OleProperties - Microsoft.ACE.OLEDB.12.0；对于 OleProperties - Excel 8.0;HDR=、OleRootFilePath - 您的 Excel 文件路径、FileName - 文件名
在连接管理器中 - 然后我设置属性 ->表达式->动态连接字符串表达式，例如：
这样，一旦您设置变量值并在配置文件中更改它 - 连接字符串将动态更改 - 这尤其有助于从开发环境转移到生产环境。
First add a variable to your SSIS package (Package Scope) - I used FileName, OleRootFilePath, OleProperties, OleProvider. The type for each variable is "string". Then I create a Configuration file (Select each variable - value) - populate the values in the configuration file - Eg: for OleProperties - Microsoft.ACE.OLEDB.12.0; for OleProperties - Excel 8.0;HDR=, OleRootFilePath - Your Excel file path, FileName - FileName
In the Connection manager - I then set the Properties-> Expressions-> Connection string expression dynamically eg:
This way once you set the variables values and change it in your configuration file - the connection string will change dynamically - this helps especially in moving from development to production environments.
一些选项：
您可以使用执行包实用程序来更改您的数据源，在运行包之前。
您可以使用 DTEXEC 运行包，并更改您的连接通过传入 /CONNECTION 参数。可能会将其保存为批处理，这样下次您就不需要输入整个内容，只需根据需要更改数据源即可。
您可以使用 SSIS XML 包配置 文件。以下是演练。
您可以将配置保存在数据库表中。
Some options:
You can use the Execute Package Utility to change your datasource, before running the package.
You can run your package using DTEXEC, and change your connection by passing in a /CONNECTION parameter. Probably save it as a batch so next time you don't need to type the whole thing and just change the datasource as required.
You can use the SSIS XML package configuration file. Here is a walk through.
You can save your configrations in a database table.
以下是您应该使用的机制的一些背景知识，称为包配置：了解集成服务包配置。
本文介绍了 5 种配置类型：
以下是在连接管理器上设置配置的演练： SQL Server Integration Services SSIS 包配置 - 我确实意识到这是使用环境变量作为连接字符串（不是一个好主意），但基础知识是相同的使用 XML 文件。在该演练中您必须更改的唯一步骤是配置类型，然后是路径。
Here's some background on the mechanism you should use, called Package Configurations: Understanding Integration Services Package Configurations.
The article describes 5 types of configurations:
Here's a walkthrough of setting up a configuration on a Connection Manager: SQL Server Integration Services SSIS Package Configuration - I do realize this is using an environment variable for the connection string (not a great idea), but the basics are identical to using an XML file. The only step(s) you have to change in that walkthrough are the configuration type, and then a path.
转到包属性->配置->启用包配置->添加->xml配置文件->指定dtsconfig文件->单击下一步->在OLEDB属性中勾选连接字符串->连接字符串值将显示 -> 单击“下一步”，完成包的配置。
您也可以在此过程中添加环境变量
Goto Package properties->Configurations->Enable Package Configurations->Add->xml configuration file->Specify dtsconfig file->click next->In OLEDB Properties tick the connection string->connection string value will be displayed->click next and finish package is hence configured.
You can add Environment variable also in this process
这些答案是正确的，但很旧并且适用于部署包模型。
我实际上需要的是更改连接管理器的服务器名称、数据库名称，我发现这非常有帮助：
https://www.youtube.com/watch?v=_yLAwTHH_GA
更适合使用 SQL Server 2012-2014-2016 的用户...通过
部署项目模型These answers are right, but old and works for
Depoloyement Package Model.What I Actually needed is to change the server name, database name of a connection manager and i found this very helpful:
https://www.youtube.com/watch?v=_yLAwTHH_GA
Better for people using SQL Server 2012-2014-2016 ... with
Deployment Project Model