我可以根据查询在 SSIS for 循环中设置变量吗? [英] Can I set variables in an SSIS for loop based on a query?

问题描述

我有一个正在 SSIS 中执行的 SQL 查询，用于将数据加载到如下所示的 CSV 文件中:

I have a SQL query that's being executed in SSIS to load data into a CSV file that looks something like this:

SELECT *
FROM SomeTable
WHERE SomeDate BETWEEN '1-Jan-2016' AND '31-Dec-2016'
      AND Param1 = 2 AND Param2 = 2

当这是在 QlikView 中编写时，我使用了如下参数:

When this was written in QlikView, I used parameters like so:

SELECT *
FROM SomeTable
WHERE SomeDate BETWEEN '1-Jan-2016' AND '31-Dec-2016'
      AND Param1 = $(Param1) AND Param2 = $(Param2)

现在我正在将整个任务迁移到 SSIS，我正在研究如何获取它，以便动态分配 Param1 和 Param2.例如，在 QlikView 中，我创建了一个由另一个查询填充的表:

Now that I'm migrating the entire task to SSIS, I'm figuring out how to get it such that Param1 and Param2 would be dynamically assigned. For example, in QlikView, I created a table that was populated by another query:

SELECT Param1, Param2
FROM ThisTable
WHERE SomeID = 1

类似的东西.从该查询中选择 Param1 和 Param2 为我获取 $(Param1) 和 $(Param2) 的必要值code> 在我的 QlikView 代码中.

Something like that. The selection of Param1 and Param2 from that query gets me the necessary values for $(Param1) and $(Param2) in my QlikView code.

我现在正在尝试将我的 QlikView 代码转换为 SSIS 包，因为 SSIS 是专用的 ETL 工具，而 QlikView 不是.我正在做的事情可能吗?如果是这样，我将如何去做?

I'm right now trying to convert my QlikView code into an SSIS package instead since SSIS is a dedicated ETL tool whereas QlikView isn't. Is what I'm doing possible? And if so, how would I go about doing it?

我的想法是将它全部包装在一个 for 循环容器中，并在它从这个查询中获取最后一个 Param1 和 Param2 后停止:

My idea was to wrap it all in a for loop container and have it stop after it grabs the last Param1 and Param2 from this query:

SELECT Param1, Param2
FROM ThisTable
WHERE SomeID = 1

基本上，我试图避免将我的第一个 select 语句写一千遍.

Basically, I'm trying to avoid having to write my first select statement a thousand times over.

谢谢.

如果我说的没有道理，请告诉我，以便我可以详细说明.

If what I'm saying doesn't make sense, please let me know so I can elaborate a bit more.

推荐答案

我怀疑您正在执行 SQL Task，因此，您只需在 SQL Task 组件中映射参数即可.

I'm suspecting that you're doing a SQL Task, therefore, you simply can map parameters in SQL Task component.

您需要做的是首先创建一个执行此查询的 SQL 组件:

What you'll have to do is to first create a SQL Component that executes this query:

SELECT Param1, Param2
FROM ThisTable
WHERE SomeID = 1;

我已经模拟了 SQLStatement，但其他一切都应该是这样的(不要忘记检查它是否有完整的数据集):

I've mocked SQLStatement up, but everything else should look like that (don't forget to check that it has a full dataset):

然后将结果集放入一个对象变量中(只需确保结果名称为 0):

Then put result set into a object variable (just make sure to Result Name as 0):

现在为了对上面收集的每个值运行以下查询，我们可以使用 foreach 循环并迭代我们的数据集.在这个 foreach 循环中，我们将放置一个数据流任务，您将使用 OLE DB 作为源和平面文件作为目标，以便读取数据并将其放入 csv 文件.(在实际项目中，我建议使用 ODBC 而不是 OLE DB，它会更快).

Now in order to run following query for each value, gathered above, we can use a foreach loop and iterate over our dataset. In this foreach loop we'll put a data flow task where you'll be using OLE DB as a source and flat file as destination in order to read data and put it into csv files. (In real project I'd advice to use ODBC instead of OLE DB, it's faster).

循环属性:

在 foreach 循环中分配变量:

Assign variables in foreach loop:

现在在数据流任务中创建您的数据源，添加查询并像这样参数化它:

Now in dataflow task create your data source, add query and parameterize it like that:

最后它应该看起来像这样(红色突出显示的是数据流任务的内部组件):

In the end it should look somehow like that (what's highlighted in red is inner components of Data Flow Task):

当然，您必须添加一些日志记录或其他一些组件，但这是基本的，可以让您继续前进.

Of course you'll have to add some logging or some other components, but this is basic and will get you moving.

这篇关于我可以根据查询在 SSIS for 循环中设置变量吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！