什么是SSIS软件包部署和更改跟踪最佳实践？ [英] What are SSIS packages deployment and change-tracking best practices?

问题描述

我有一个关于我的SQL数据库模式和数据（一切都是源代码控制，部署自动化等）的强大的开发环境，但是当涉及到SSIS包时，这个过程是相当乏味且完全手动的。 / p>

• 如何自动化SSIS包升级过程（现在我将手动将我的软件包复制到每个服务器，然后我使用管理工作室的SSIS软件包升级向导）




• 如何部署我的计划表（现在我正在手动）




• 如何部署我的SQL代理作业（现在我正在手动）




• 我可以在我的TeamSystem存储库中获取我的日程表和工作表（现在我没有使用源代码控制）




• 还有其他的东西我可以/应该要提高SSIS包的工作方式吗？

我正在使用MS SQL Server 2008 SP1，但我想像一些建议在其他版本上也可能有用

解决方案

点1（自动部署）：

可以使用 dtutil.exe 从命令行部署SSIS包。

以下示例将部署本地文件 c：\test.dtsx 到 server1 上的MSDB存储，名称为 test_package

  dtutil.exe / FILE C：\test.dtsx / dests server1 / COPY SQL; test_package 
  

它将对所有各种SSIS存储选项进行导入/导出的任意组合，如果您可以工作出来参数的组合使用。

这可能与您无关，但这不会做（向导将）将复制配置文件;你需要一个单独的批处理步骤。

点2和3（SQL代理的源代码控制）：

$ b $如其他人所说，SQL代理作业和计划可以被编写为T-SQL（在Management Studio中，右键单击作业并选择脚本作业...

我们的数据库源控件树设置为每个生产服务器包含一个文件夹，其中包含一个文件夹每个用户数据库的子文件夹加上一个名为 _config 的额外子文件夹，其中包含服务器级对象（如登录，链接服务器和SQL代理作业）的脚本。

（与您的问题无关，但我们将SSIS包存储在自己的树中，因为它们可以应用于多个服务器）

我们不使用TFS，所以我无法评论让脚本进入的细节。

I've got quite a robust development environment regarding my SQL database Schema and Data (everything is source controlled, deployment is automated, etc), but when it comes to SSIS packages, the process is quite tedious and totally manual.

• How can I automate the SSIS package upgrade process (right now, I copy my packages manually to each server, and then I use Management Studio's "SSIS Package Upgrade Wizard")

• How can I deploy my Schedules (right now, I'm doing it manually)

• How can I deploy my SQL Agent Jobs (right now, I'm doing it manually)

• How can I get my Schedules and Jobs int my TeamSystem repository (right now, I use no source-control for this)

• Are there other things I can/should do to enhance the way I work regarding SSIS packages ?

I'm using MS SQL Server 2008 SP1, but I imagine some advices could be useful on other versions as well.

解决方案

On point 1 (automating deployment):

It's possible to deploy SSIS packages from the command line using dtutil.exe.

The following example would deploy the local file c:\test.dtsx to MSDB storage on server1 with the name test_package:

dtutil.exe /FILE C:\test.dtsx /dests server1 /COPY SQL;test_package

It will do any combination of import/export for all the various SSIS storage options, if you can work out the combination of parameters to use.

This may not be relevant to you, but what this won't do (that the wizard will) is copy configuration files; you'd need a separate batch step for that.

On points 2 and 3 (source control of SQL Agent):

As others have said, SQL Agent Jobs and schedules can be scripted as T-SQL (in Management Studio, right-click the Job and select Script job to...), held in source control, and deployed like any other T-SQL script.

Our DB source control tree is set up with one folder per production server containing one subfolder per user database plus an extra subfolder called _config which contains scripts for server-level objects such as logins, linked servers and SQL Agent Jobs.

(It's not really relevant to your question but we store SSIS packages in their own tree, since they can be applied to multiple servers)

We don't use TFS, so I can't comment on the specifics of getting the scripts into it.

这篇关于什么是SSIS软件包部署和更改跟踪最佳实践？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！