如何在SSIS中动态设置执行包任务的位置 [英] How can I dynamically set the location of an Execute Package Task in SSIS

问题描述

我试图在SQL Server 2008中设置一个'master'SSIS包来运行其他'子'包。在开发child包时，我们将所有包放在磁盘上，以便我们可以轻松地对它们进行调试，因此我们在开发过程中使用文件连接器，并可以很好地监视进度。

I'm trying to set up a 'master' SSIS Package in SQL Server 2008 to run other 'child' packages. When developing the 'child' packages we have all the packages sitting on disk so we can easily debug them, so we use file connectors during development and can monitor the progress nicely.

当我们部署时，我们将子包部署到SQL Server上的SSIS，然后通过并更改所有执行包任务，以使用位置值SQL Server并设置PackageName。完成后，我们部署了master。

When we deploy, we deploy the child packages to SSIS on SQL Server and then go through and change all the Execute Package Task's to use a location value of 'SQL Server' and set the PackageName. Once done, we deploy the 'master'.

我想要做的是在执行包任务中使用一个Expression来设置连接属性，这取决于环境。我们已经使用一个视图来设置SQL Server配置数据库，该视图检查查询的主机名，并根据查询返回不同的值。

What I'd like to do is use an Expression on the Execute Package Task to set the connection properties so we can configure this dependent on the environment. We have already setup a SQL Server configuration database using a view which checks the host name of the query and returns different values dependent on the query.

推荐答案

您有选择。

要使用表达式，您需要使用脚本任务或执行SQL任务以返回要处理的文件列表。

To use expressions, you would need to use a Script Task or Execute SQL Task to return back the list of files you want to work through.

您必须将每个返回的值赋给它自己的变量，并传递到表达式，或使用FOR EACH循环并通过列表工作，每次分配子包的位置。

You would either have to assign each returned value to it's own variable that is passed into the expression, or use a FOR EACH loop and work through a list, assigning the location of the child package each time.

另一个选项是使用配置。我的首选是在SSIS中使用配置表。如果你在每个环境中有相同的软件包列表，你可以传入根目录，并有一个表达式使用：
@ [User :: RootPackagePath] +\PackageName.dtsx

The other option is to use configurations. My preference is to use a configuration table inside SSIS. If you have the same list of packages in each environment, you could either pass in the root directory and have an expression use that: @[User::RootPackagePath] + "\PackageName.dtsx"

或者，您可以在配置表中为每个子包创建一个记录，并将其传递到包中。

Or, you could simply have one record for each child package in the configuration table and that would be passed into the package.

我成功地能够配置一个包，通过配置更改，从文件系统然后SQL调用包。

I was successfully able to configure a package to change via configurations to call a package from the file system then SQL.

我只需要为每个传递Connection和PackageName。使用SQL，它需要一个来自连接管理器的名称（在我的情况下是.\SQL2008R2）和包名（\Package1）。对于文件系统，PackageName是空白的，并且连接是连接管理器中的FileConnection。

I only needed to pass the Connection and PackageName for each. With SQL, it want a name from the connection manager (.\SQL2008R2 in my case) and the package name (\Package1). For the file system, PackageName is blanked out and the connection is a FileConnection in the connection manager.

您必须在包中保留两者，两个。

You will have to keep both in the package, but you switch between the two.

这篇关于如何在SSIS中动态设置执行包任务的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！