SSIS部署策略(SSIS Deployment Strategies)

SSIS部署策略(SSIS Deployment Strategies)

我一直在研究SSIS的几种部署策略，并想知道哪种策略最容易维护。 我一直倾向于使用SQL Server配置来容纳连接字符串，然后在从开发服务器导入包以更改连接字符串后运行proc。 但是，我有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.

最满意答案

我们正在为我的团队构建的大多数软件包使用SQL配置。 我们为解决迁移问题所做的是基于环境变量添加第二个配置，该环境变量告诉包使用哪个配置数据库。 对于每个人来说这可能不是一个好选择，但它适用于我们的设置。

细节：

我们的包始终从代理作业运行。 我们的每个环境都在一个单独的机器上（我们不使用除沙盒之外的命名实例。） 我们在每台计算机的默认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:

Our packages are always run from agent jobs. Each of our environments is on a seperate machine (we don't use named instances except for sandboxing.) We have a copy of our configurations DB in the default SQL instance of each machine and it uses the same db name and schema in each environment. The package looks at the machine name environment variable to tell which machine is executing the package. The package then looks for the configurations database on the executing machine to get the connection strings for the actual work to be done.

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.