SQL 如何在 ADO.NET Source SSIS 中传递参数

How to Pass parameter in ADO.NET Source SSIS

提问by CMinor

Hello I need some help This will be my first SSIS package and I am learning as I go. So far this is what I have.

您好，我需要一些帮助 这将是我的第一个 SSIS 包，我正在学习。到目前为止，这就是我所拥有的。

I created a Control Flow. Then I created three ADO.Net connections, twofor source and one for destination. Then I created data flow tasks it copies data from a table in one database into a corresponding table in another database on the same server. data flow task has an 2 ADO NET Source and ADO NET Destination. The destination simply maps the fields together.

我创建了一个控制流。然后我创建了三个 ADO.Net 连接，两个用于源，一个用于目标。然后我创建了数据流任务，它将数据从一个数据库中的表复制到同一服务器上另一个数据库中的相应表中。数据流任务有 2 个 ADO NET Source 和 ADO NET Destination。目的地只是将字段映射在一起。

Okay, so far so good. This is my problem. Some of the source queries have date criteria. An example would be:

好的，到目前为止一切顺利。这是我的问题。一些源查询具有日期条件。一个例子是：

SELECT --Code Here 
WHERE CONVERT(varchar, call_date, 112) BETWEEN '6/1/2013' AND  '7/1/2013'

I want to replace these hard-coded dates with variables. Something like:

我想用变量替换这些硬编码的日期。就像是：

WHERE CONVERT(varchar, call_date, 112) BETWEEN STARTDATE AND ENDATE

I've read several posts and tried to do what is being described, but it's not sinking in. So please use my example to tell me how to do this. It would be nice if I could have the package prompt me for the Date when I run it, but I'd be very happy just to learn how to pass a variable into the query.

我已经阅读了几篇文章并尝试做所描述的事情，但它并没有陷入困境。所以请用我的例子来告诉我如何做到这一点。如果我能在运行它时让包提示我输入日期，那就太好了，但我很高兴能学习如何将变量传递到查询中。

This is the only solution I know because I just a beginner here in SSIS package I hope someone can help me

这是我知道的唯一解决方案，因为我只是 SSIS 包的初学者，希望有人能帮助我

回答by OzrenTkalcecKrznaric

Since none of the answers here actually answer the question (pay attention to the ADO.NET source, not OLE DB!), here's the real answer.

由于这里的答案都没有真正回答问题（注意ADO.NET 源，而不是 OLE DB！），这是真正的答案。

In SSIS you can't parametrize ADO.NET source. You have to use a workaround.

在 SSIS 中，您不能参数化 ADO.NET 源。您必须使用一种解决方法。

Luckily, there are few workarounds. One would be creating Script Component that acts like source and code it. However, one can't always easily convert the existing resource into script, especially when he lacks ADO.NET programming knowledge.

幸运的是，解决方法很少。一种是创建像源代码一样的脚本组件并对其进行编码。然而，人们不能总是轻易地将现有资源转换成脚本，尤其是当他缺乏 ADO.NET 编程知识时。

There is another workaround, and that would be creating the SQL Query beforethe ADO.NET Source takes action. However, when you open ADO.NET source, you will notice that Data access mode doesn't allow variable input. So, how do you proceed?

还有另一种解决方法，那就是在 ADO.NET 源执行操作之前创建 SQL 查询。但是，当您打开 ADO.NET 源代码时，您会注意到数据访问模式不允许变量输入。那么，你如何进行？

You want to dynamically set the SQL expression of the ADO.NET source, so you have to tell your data flow taskto configure the SSIS ADO.NET source component by using Expression.

您希望动态设置 ADO.NET 源的 SQL 表达式，因此您必须告诉您的数据流任务使用表达式配置 SSIS ADO.NET 源组件。

To make the long story short (or not-quite-so-short :), do this:

为了使长话短说（或不那么短:)，请执行以下操作：

• in your package, enter your data flow task with source/destination components
• click anywhere on the background, to have Task properties shown in Property panel
• in Property panel find Expressions property, that can configure various data source/destination properties, and open it using ellipsis button (...)
• under Property, select SQL Command property of your source (e.g. [ADO.NET source].[SqlCommand]) to add one row
• click ellipsis button for the row to open Expression Builder
• build your dynamic query in the Expression Builder

• 在您的包中，输入带有源/目标组件的数据流任务
• 单击背景上的任意位置，以在“属性”面板中显示“任务”属性
• 在属性面板中找到 Expressions 属性，该属性可以配置各种数据源/目标属性，并使用省略号按钮（...）
• 在属性下，选择源的 SQL 命令属性（例如 [ADO.NET 源].[SqlCommand]）以添加一行
• 单击该行的省略号按钮以打开表达式生成器
• 在表达式生成器中构建动态查询

The last step could be somewhat cumbersome for date/datetime parameter. However, here's the example, for your convenience:

对于日期/日期时间参数，最后一步可能有些麻烦。但是，为了您的方便，这里有一个例子：

"SELECT * FROM YOUR_SOURCE_TABLE WHERE your_date_column = '" + 
  (DT_WSTR,4)YEAR(@[User::VAR_CONTAINING_DATE]) + "-" +
  (DT_WSTR,2)MONTH(@[User::VAR_CONTAINING_DATE]) + "-" +
  (DT_WSTR,2)DAY(@[User::VAR_CONTAINING_DATE]) + "'"

HTH

HTH

回答by Kay Tsar

[ ADO.NET AS A SOURCE TYPE SOLUTION ]

[ ADO.NET 作为源类型解决方案 ]

Step 1Create variables for each parameter you would like to use.

步骤 1为要使用的每个参数创建变量。

Step 2Select the task on the Control Flow tab which executes the query

步骤 2在控制流选项卡上选择执行查询的任务

Step 3Go to the properties of this task to select the Expressions by clicking on the "..." button

步骤 3转到此任务的属性，通过单击“...”按钮选择表达式

Step 4Select the command property and click on the "..." button

步骤 4选择命令属性并单击“...”按钮

Step 5Now you can construct your query here including the variables you defined in Step 1. Save when done. Enjoy!

第 5 步现在您可以在此处构建您的查询，包括您在第 1 步中定义的变量。完成后保存。享受！

回答by DaImTo

Ok here is how i do it.

好的，这就是我的方法。

On your control flow create an Execute SQL task. Set up the DB connection. In the SQLStatment do something like.

在您的控制流上创建一个执行 SQL 任务。设置数据库连接。在 SQLStatment 中做类似的事情。

select getdate() -1 startDate , getdate() endDate

Set ResultSet on the genral tab to Single Row. Go to the Result Set tab click add Give a Result name something like StartDate then add a new Variable.

将常规选项卡上的 ResultSet 设置为 Single Row。转到“结果集”选项卡，单击“添加”给“结果名称”，例如“开始日期”，然后添加一个新变量。

Name: startDate  (must be the same name as the alius in the select)
Namespace: user 
type:  DateTime
Value:  02-09-2013

Now when that runs it should fill in a user variable with the current date and yesterdays date. Do the same thing form EndDate

现在，当它运行时，它应该用当前日期和昨天的日期填充一个用户变量。从 EndDate 做同样的事情

Create your second Execute SQL task Link it to the first. Add this SQL statment. ? will be your variables

创建您的第二个执行 SQL 任务将其链接到第一个。添加此 SQL 语句。? 将是你的变量

select stuff
WHERE CONVERT(varchar, call_date, 112) BETWEEN ? AND ?

Go to Paramater mapping. Add one

转到参数映射。添加一个

Variable Name should be something like User::StartDate 
Direction Input
Datetype: Nvarchar
Parameter name: 0 
Parameter size: 0

add the second one

添加第二个

Variable Name should be something like User::EndDate 
Direction Input
Datetype: Nvarchar
Parameter name: 1
Parameter size: 0

When you add a third one just add Parameter name 2...

当您添加第三个时，只需添加参数名称 2...

Run it.

运行。

回答by TsSkTo

Here's the OLE DB Apporach:

这是 OLE DB 方法：

Create some parameters for your package. If you make them Required then they are, well, required and your package won't run without some values. The field in the Value column will be the default and used for debugging.

为您的包创建一些参数。如果您将它们设为必需，那么它们就是必需的，如果没有某些值，您的包将无法运行。Value 列中的字段将是默认值并用于调试。

Then create a variable and add an expression:

然后创建一个变量并添加一个表达式：

The Expressions editor uses a washed out version of C#(if you were curious)

表达式编辑器使用旧版 C#（如果您好奇的话）

Also Make sure to add spaces in your string in between variablesIts hard to see but AND has a space on either side like so:

还要确保在变量之间的字符串中添加空格它很难看到，但 AND 两边都有一个空格，如下所示：

" AND "

“ 和 ”

I almost forgot. The values for yor parameters have to be in 'singleQuotes'. This can be done in one of two ways: You can either include the quotes when you give a value to the string or you can add some quotes in the expression(what I usually do).

我差点忘了。yor 参数的值必须在“singleQuotes”中。这可以通过以下两种方式之一完成：您可以在为字符串赋值时包含引号，也可以在表达式中添加一些引号（我通常这样做）。

Adding them in the expression would look something like this:

在表达式中添加它们看起来像这样：

"some stuff " + "'" + @[variable] + "'"

"some stuff " + "'" + @[variable] + "'"

Click evaluate expression to see if your variable is holding the correct string.

单击“评估表达式”以查看您的变量是否包含正确的字符串。

The last step is to add an OLE DB Source, set up the connection(if you don't have one yet) and add the query from variable.

最后一步是添加一个 OLE DB 源，设置连接（如果您还没有连接）并添加来自变量的查询。