如何使用 SSIS 包中的变量值加载新表? [英] How to load a new table with the value of a variable from SSIS package?

问题描述

我在 SSIS 包 Var1 和 Var2 中有两个变量.这两个变量都有值.有什么办法可以将这两个变量的值放在新表中吗?例如，在具有 Var1 值的新表 col1 和具有 Var2 值的 col2 中.

i have two variables in a SSIS package Var1 and Var2. Both of these variables have values. is there any way i can put the values of these two variables in a new table? e.g In New table col1 having value of Var1 and col2 having value of Var2.

谢谢

推荐答案

有几种方法可以做到这一点.

There are a couple of ways to do this.

一种是使用派生列任务创建数据流.然后，您可以提取这两个变量并将其发送到 OLE DB 或 SQL Server 目标.

One is to create a data flow with a Derived Column Task. Then you can pull in both variables and send it to a OLE DB or SQL Server Destination.

另一个是使用执行 SQL 任务.其中的技巧部分是正确使用参数.查询将如下所示:

Another is to use a Execute SQL Task. The trick part of this is using the parameters correctly. The query would look like:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.[VarLog]') AND type in (N'U'))
DROP TABLE dbo.[VarLog]

Create table dbo.varlog (
    [var1] [varchar](50) NULL,
    [var2] [varchar](50) NULL
) 

Insert into varlog (var1,var2) values(?, ?)

在参数映射屏幕上添加您的两个变量.请注意，参数名称是一个基于 0 的计数器，对应于 ?.例如，请参见屏幕显示.在我的测试中，我使用了 PackageName 和 StartTime 的系统变量.

On the Parameter Mapping Screen add your two variables. Note that the Parameter Name is a 0 based counter that corresponds to the ?. See screen show for example. In my test I used the system variables for PackageName and StartTime.

注意:我选择删除并替换示例中的表格.您很可能希望拥有此静态数据，并且可能会向表中添加一个日期时间以随着时间的推移跟踪变量.

Note: I opted to drop and replace the table for the example. Most likely you will want to have this static and maybe add a datetime to the table to track the vars over time.

注意 2:我知道开始时间不是 varchar，但它符合示例的目的.

Note2: I know start time isn't a varchar but it serves the purpose of the example.

这篇关于如何使用 SSIS 包中的变量值加载新表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！