SSIS 部署策略
我一直在研究 SSIS 的几种部署策略，并想知道哪一种最容易维护。我一直倾向于使用 SQL Server 配置来容纳连接字符串，然后在从开发服务器导入包以更改连接字符串后运行一个过程。然而，我有 75 个包，这似乎有点乏味。谁能提出一个好的部署策略？
我有一个 Dev、Stage 和几个要部署到的实时服务器。
I have been looking at several deployment strategies for SSIS and wondering which one would be easiest to maintain. I have been leaning toward using SQL Server Configurations to house the connection strings and then run a proc after I have imported a package from the development server to change the connection strings. However, I have 75 packages and this seems somewhat tedious. Can anyone suggest a good deployment strategy?
I have a Dev, Stage, and several live servers to deploy to.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我们团队构建的大多数包都使用 SQL 配置。我们为解决迁移问题所做的就是根据环境变量添加第二个配置，该变量告诉包要使用哪些配置数据库。这可能不是每个人的好选择，但它对我们的设置很有效。
详细信息：
当我们构建新包时，我们必须将 SQL 配置迁移到每个环境并根据需要进行调整。但从那时起，如果我们更改包使用的连接或执行它的服务器，我们只需要担心它们。
通过这种方式，包始终知道哪个服务器正在执行它，并始终使用与该服务器关联的配置。因此，各个软件包的持续维护和部署通常是直接的。我们通常需要担心的移动是包本身以及与更新相关的任何底层架构更改。
We are using SQL configurations for most of the packages built by my team. What we did to resolve the migration issue was to add a second configuration based on an environment variable which tells the package which configurations database to use. This may not be a good option for everyone but it works well for our setup.
Details:
When we build a new package we have to migrate the SQL configurations to each environment and adjust them as necessary. But from that point on we only need to worry about them if we make a change to which connections the package uses or which server is executing it.
Doing it this way the package always knows which server is executing it and always uses the configurations associated with that server. So the ongoing maintenance and deployment of the individual packages is usually straight forward. All we generally have to worry about moving is the package itself and any underlying schema changes associated with the update.
我通常发现在 XML 文件中包含包配置更容易，其中包含所有必需的数据库连接。当包部署到每个环境时，这会根据需要进行修改（这可能作为部署清单安装的一部分发生）。 75 个包中的每一个都可以共享相同的配置文件，这使得它的管理非常简单。
I've generally found it easier to have a Package Configuration in an XML file, which contains all the required database connections. This gets amended as required when the packages are deployed to each environment (this can happen as part of the Deployment Manifest install). Each of your 75 packages can share the same configuration file which makes the management of it very straightforward.
我们在每台机器上都有一个指向目录的环境变量。在该目录中，我们有一个 SSIS 配置文件。配置文件有一个条目 - 它配置我们所有包中找到的连接管理器的连接字符串属性 - 称为 SSIS_CONFIG。该连接字符串指向具有该环境的配置表的数据库。
配置表包含连接管理器的配置以及各种其他配置。连接管理器行的 ConfigurationFilter 设置为数据库名称，ConfiguredValue 具有该数据库的连接字符串。
每个包都有 SSIS_CONFIG 连接管理器。所有其他连接管理器都以它们连接的数据库的名称命名（而不是 SSIS 服务器和数据库的默认命名）。
SSIS_CONFIG 连接管理器由具有间接 XML 配置类型的包配置进行配置，其中配置位置存储在环境变量中。其他所有连接管理器都使用配置类型 SQL Server、连接 SSIS_CONFIG 以及它们连接的数据库名称的配置筛选器。
如果一个新包需要连接到数据库，则另一个包也必须连接到数据库，因此该连接管理器所需的配置已经在配置表中，因此我们在构建该包的包配置时重用该值。
每个环境都有环境变量以及自己的数据库版本和配置表。环境之间的配置表中的唯一区别是ConfiguredValue 列中的连接字符串。例如，DEV 环境中的连接字符串指向数据库的 DEV 版本，QA 环境条目指向数据库的 QA 版本。
在环境之间升级包时编辑包会使测试失效。这种方法使我们能够在不接触软件包的情况下推广它们。该设计也非常灵活，这使得开发和测试变得更加容易。
我们可以将此方法用于同一台计算机上运行的多个实例，并以此为指导：
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS ）/69739/
We have an environment variable on each machine that points to a directory. In that directory we have one SSIS config file. The config file has one entry - it configures the connection string property of a connection manager found in all of our packages - called SSIS_CONFIG. This connection string points to the database that has our config table for that environment.
The config table has configurations for connection managers, as well as various other configurations. The ConfigurationFilter for the connection manager rows are set to the database name and the ConfiguredValue has the connection string for that database.
Each package has the SSIS_CONFIG connection manager. All other connection managers are named the name of the database to which they connect (instead of the SSIS default naming of the server and database).
The SSIS_CONFIG connection manager is configured by package configurations with a configuration type of indirect XML where the configuration location is stored in an environment variable. Every other connection manager uses configuration type SQL Server, the connection SSIS_CONFIG, and the configuration filter of the name of the database to which they connect.
If a new package needs to connect to a database, the odds are that another package has had to also, so the configuration needed for that Connection Manager is already in the config table, so we reuse that value when we build that package's package configurations.
Each environment has the environment variable and its own version of the database and config table. The only difference in the config table between environments is the connection strings in the ConfiguredValue column. For example, the connection strings in the DEV environment point to the DEV versions of the databases, and the QA environment entries point to the QA versions of the databases.
Editing packages as you promote them between environments invalidates testing. This approach allows us to promote packages without touching them. The design is also extrememly flexible which makes development and testing much easier.
We are able to use this approach with multiple instances running on the same machine using this as a guideline:
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/69739/