如何在 ADO.NET Source SSIS 中传递参数 [英] How to Pass parameter in ADO.NET Source SSIS

问题描述

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

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.

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

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.

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

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.

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

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

推荐答案

因为这里的答案都没有真正回答问题(注意 ADO.NET 源代码，而不是 OLE DB！)，这是真正的答案.

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.

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

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

幸运的是，解决方法很少.一种是创建像源代码一样的脚本组件并对其进行编码.然而，一个人不能总是轻易地将现有资源转换成脚本，尤其是当他缺乏 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 源执行操作之前创建 SQL 查询.但是，当您打开 ADO.NET 源代码时，您会注意到数据访问模式不允许变量输入.那么，您将如何进行?

There is another workaround, and that would be creating the SQL Query before the 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 表达式，因此您必须告诉您的数据流任务使用表达式配置 SSIS ADO.NET 源组件.

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

要长话短说(或不那么短:)，请执行以下操作:

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

• 在您的包中，输入包含源/目标组件的数据流任务
• 单击背景上的任意位置，即可在属性"面板中显示任务属性
• 在属性"面板中找到表达式"属性，该属性可以配置各种数据源/目标属性，然后使用省略号按钮 (...) 将其打开
• 在属性下，选择源的 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

这篇关于如何在 ADO.NET Source SSIS 中传递参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！