动态值传递到SSIS包(转)
使用SQL server Integration Services (SSIS)最好是把代码设计得尽可能可以重复使用,这样就可以利用同样的代码集来处理多种情况。我知道如何在SSIS包里利用可变的设置创建动态文件源,但是我怎么样进一步把动态值传递到SSIS包里呢?
回答:
跟SQL server 2000的Data Transformation Services (DTS)一样,SSIS拥有在运行时向SSIS包直接传递参数值的功能。SSIS使用的句法跟DTS不太一样,但是比DTS提供更多的选择。
为了举例说明,我们现在要创建一个平坦式文件源来向SQL Server表里导入数据。第一个方法是使用硬代码,在命令行里调用SSIS包然后向里面的各个地方传递参数值。
我们首先要创建一个新的SSIS包,使用"Data Flow Task"。
然后添加"Flat File Source"和"OLE DB Destination"。平坦式文件源是CSV文件,包含以下列:ID、Name、Address、City、State和Zip。SQL Server表里的列跟它完全一样。
把值硬代码后,文件包可以顺利执行。
为了更好地利用SSIS包动态方面的功能,我们可以创建一个新的变量,命名为"fileName',这个变量会使用我们传递到SSIS包的文件的通道和名称。
首先我们创建一个新的名为"fileName"的变量。
创建好变量之后,为平坦式文件连接创建一个Expression,如下图所示:
现在我们要导入的文件名是"c:\temp\test2.csv"(这是一个不存在的文件),这是建立在我们刚才设置的变量的基础上的,如果我们现在执行文件包,它会出现以下错误信息:
变量设置到这个实际不存在的文件,只是为了说明尽管这个运行包会运行失败,但是当我们传递进去合适的文件名的时候,它的确会运行。下面是执行SSIS包和为fileName变量传递变量值的语法。
这个句法创建了运行SSIS包的命令,但是它用的文件名是'C:\temp\test.csv",而非"C:\temp\test2.csv。
可以把下面的代码粘贴到查询窗口里执行。(注意:你必须要先启用 xp_cmdshell才能运行以下代码)
以下是引用片段: declare @cmd varchar(1000) declare @ssispath varchar(1000) declare @fileName varchar(1000) set @ssispath = 'C:\temp\Package.dtsx' set @fileName = 'C:\temp\test.csv' select @cmd = 'dtexec /F "' + @ssispath + '"' select @cmd = @cmd + ' /SET \Package.Variables[User::fileName].Properties[Value];"' + @fileName + '"' exec master..xp_cmdshell @cmd |
代码运行之后,查询结果的最后会显示运行成功,如下所示:
如果我们要向SSIS包里传递两个变量,步骤跟上面也差不多。
这里有两个变量,一个是fileName,另一个是filePath。
如果我们现在看一下expression,会发现它工作原理跟上面一样,只是现在需要用到两个变量。
下面是向SSIS包传递两个变量的代码。编码的句法大部分跟上面是一样的,但现在需要有两个/SET命令来传递两个变量。
(注意:我们必须在 filePath结尾出用\\。如果我们只写成"C:\temp\",那么最后就会出现运行错误。
这时你可以执行SSIS包和已经传递进去的两个变量了。
以下是引用片段: declare @cmd varchar(1000) declare @ssispath varchar(1000) declare @filePath varchar(1000) declare @fileName varchar(1000) set @ssispath = 'C:\temp\Package2.dtsx' set @filePath = 'C:\temp\\' set @fileName = 'test.csv' select @cmd = 'dtexec /F "' + @ssispath + '"' select @cmd = @cmd + ' /SET \Package.Variables[User::filePath].Properties[Value];"' + @filePath + '"' select @cmd = @cmd + ' /SET \Package.Variables[User::fileName].Properties[Value];"' + @fileName + '"' exec master..xp_cmdshell @cmd |
这里只举例说了传递用户定义的变量,但是其实你可以传递任何形式的动态值来控制你的SSIS包