如何在 SSIS 包中设置变量? [英] How to Set Variables in an SSIS Package?

问题描述

我需要在我的 SSIS 包中使用两个变量，@extract_beg_date 和 @extract_end_date.

I need to use two variables in my SSIS package, @extract_beg_date and @extract_end_date.

如何设置它们以某种方式运行?例如，在存储过程中，它看起来像这样:

How do I set them to behave a certain way? For example, in a stored procedure, it would look like this:

SET @extract_beg_date = CASE WHEN DATEPART(weekday,GETDATE()) = '2'
                           THEN CAST(CONVERT(VARCHAR(10),GETDATE()-2,101) AS DATETIME)
                           ELSE CAST(CONVERT(VARCHAR(10),GETDATE()-1,101) AS DATETIME)
                        END

SET @extract_end_date = CAST(CONVERT(VARCHAR(10),GETDATE(),101) AS DATETIME)

如何在不使用存储过程的情况下设置 SSIS 包中的变量?我希望能够像这样在 SQL 命令文本中使用它:

How can I set the variables in the SSIS package without having to use a stored procedure? I want to be able to use it in the SQL Command text like so:

SELECT *
FROM dbo.test_tbl
WHERE [TimeStamp] >= @extract_beg_date and [TimeStamp] < @extract_end_date

推荐答案

我会这样做:

1. 在流程流上，添加一个执行 SQL 任务.

2. 打开Execute SQL Task，将ResultSet属性配置为Single row，将SQLStatement属性配置为:

1. On the process flow, add an Execute SQL Task.

2. Open the Execute SQL Task and configure the ResultSet property to Single row, and configure the SQLStatement property to:

选择CASE WHEN DATEPART(weekday,GETDATE()) = '2'然后 CAST(CONVERT(VARCHAR(10),GETDATE()-2,101) AS DATETIME)ELSE CAST(CONVERT(VARCHAR(10),GETDATE()-1,101) AS DATETIME) END AS extract_beg_date，CAST(CONVERT(VARCHAR(10),GETDATE(),101) AS DATETIME) AS extract_end_date

SELECT CASE WHEN DATEPART(weekday,GETDATE()) = '2' THEN CAST(CONVERT(VARCHAR(10),GETDATE()-2,101) AS DATETIME) ELSE CAST(CONVERT(VARCHAR(10),GETDATE()-1,101) AS DATETIME) END AS extract_beg_date, CAST(CONVERT(VARCHAR(10),GETDATE(),101) AS DATETIME) AS extract_end_date

1. 仍然在执行 SQL 任务中，转到结果集视图并添加两个结果输出.将结果名称分别设置为 0 和 1，并为 extract_beg_date 和 extract_end_date 创建 2 个变量.

1. 在执行期间，执行 SQL 任务将获取 SELECT 查询的输出并将每个值保存到您创建的两个 SSIS 变量中.这些变量可在后续步骤中用于参数化另一个查询.

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