SSIS入门执行SQL任务结果集对象 [英] SSIS Getting Execute Sql Task result set object

问题描述

我有一个执行SQL任务项目，它是从一个存储过程中获取数据的多个行。

I have an execute sql task item and it is getting multiple rows of data from a stored proc.

声明的变量 ObjShipment 变量表下，在下列信息的结果集所赋予它：

Declared a variable ObjShipment under the variable table and assigned it under the Result set with the following info:

Result Set:    Full result set
Result Name:   0
Variable Name: User::ObjShipment

我写了一个脚本任务分配给只读的想知道如何在其中检索数据ObjShipment变量？

I have written a script task with the ObjShipment variable assigned to ReadOnly an am wondering how to retrieve the data within it?

该存储过程返回类似标识，ITEMID，dateCreated会.. ，但我怎么找回他们说，如果我只在ITEMID感兴趣吗？而且由于它返回多行可能有不止一个ITEMID。

The stored proc returns multiple rows like Id, ItemId, DateCreated.., but how do I retrieve them say if I am only interested in the ItemId? And since it returns multiple rows there could be more than one ItemId.

我是新的SSIS的任何帮助，将不胜感激！

I am new with ssis any help would be appreciated!

推荐答案

所以一般来讲，​​你使用SSIS包作为一个枚举器每个容器内的对象变量。

So generally speaking you use Object variables within an SSIS package as the Enumerator of a For Each container.

1. 创建一个For Each循环容器。


2. 将枚举为对于每个ADO枚举器。


3. 将源变量为User :: ObjShipment。


4. 从分配你的对象每一列中的变量映射选项卡自身的变量。


5. 内的For Each循环容器，使用这些变量做任何你想要的：它们插入到数据库，请查询和审计等

1. Create a For Each Loop Container.
2. Set the Enumerator to a "For Each ADO Enumerator".
3. Set the source variable to User::ObjShipment.
4. Assign each column from your object to its own variable in the Variable Mappings tab.
5. Within the For Each Loop container, use those variables to do whatever you want: insert them into a database, do lookups and audits, etc.

如果您要使用脚本任务，那么你所有需要做的就是

If you are going to use a Script Task, then you all need to do is

DataTable dt = new DataTable();
OleDbDataAdapter oleDa = new OleDbDataAdapter();
oleDa.Fill(dt, Dts.Variables["User::objShipment"].Value);

然后用DT像任何旧的数据表。

And then use dt like any old DataTable.

这篇关于SSIS入门执行SQL任务结果集对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！