SSIS - 在另一个表上执行查找以获取相关列 [英] SSIS - Performing a Lookup on another Table to get Related Column

问题描述

我想在 SSIS 中执行一个 select 语句，但是这个 select 语句从另一个组件中获取一个参数，并且这个 select 语句的列必须用作其他组件的输入.

例如:

select id from myTable where name = (来自前一个组件的列).

而且上面select语句的id"内容应该是以后组件可以使用的列.

如果我添加一个OLE DB 命令"组件，它允许我将其他组件称为输入，但我无法从中生成输出.OLE DB 命令组件似乎只用于更新/插入语句?

关于如何做到这一点的任何想法?

解决方案

实际上，这是 Lookup 的一个案例.看来您想按名称进行查找并返回 id.很简单.下面是我如何创建这样的示例:

1. 将数据流任务拖到设计图面上.双击它以切换到它.
2. 为我的数据库创建一个连接管理器
3. 拖到设计图面上:
   • 一个 OLE DB 源
   • 查找转换
   • OLE DB 目标
4. 将源连接到目标查找.这是我们要去目的地的查找匹配输出".见图 1.
5. 配置源.我的源表只有 id 和 name 列.
6. 配置查找
   • 常规选项卡:使用 OLE DB 连接
   • Connection 选项卡:指定相同的连接，但使用查找表.我的查找表只有 id 和 name，但 name 是唯一的，因此作为查找列更有意义.
   • 在列选项卡上，配置名称以映射到名称，以id"作为输出.将查找操作配置为添加新列"，并将该列命名为lookupId".见图 2.
   • 忽略其他两个标签
7. 将输出配置为采用所有三列.见图 3.

仅此而已.对于源中的每一行，名称列将用于匹配查找表的名称列.每个匹配项都会贡献其 id 列作为新的 lookupId 列.所有三列都将前往目的地.

图一:
7alt.png"

图2:
apngt.png

图3:
textal

I want to executing a select statement in SSIS, but this select statement takes a parameter from another component, and the column of this select statement must be used as inputs to other components.

For example:

select id from myTable where name = (column from a previous component).

And the "id" content of the above select statement should be a column that future components can use.

If i add an "OLE DB Command" component, it allows me to refer to other components as inputs, but I cannot generate an output from it. It seems OLE DB Command component is only used for update/insert statements?

Any ideas on how to do it?

解决方案

Actually, this is a case for Lookup. It seems you want to do a lookup by name and return id. Pretty simple. Here's how I created an example of this:

1. Drag a Data Flow Task onto the design surface. Double-click it to switch to it.
2. Create a Connection Manager for my database
3. Drag onto the design surface:
   • an OLE DB Source
   • A Lookup Transform
   • An OLE DB Destination
4. Connect the Source to the Lookup to the Destination. It's the "Lookup Match Output" we want going to the destination. See figure 1.
5. Configure the source. My source table just had id and name columns.
6. Configure the lookup
   • General tab: Use an OLE DB Connection
   • Connection tab: specify the same connection, but use the Lookup table. My lookup table was just id and name, but name was made unique, so it makes better sense as a lookup column.
   • On the columns tab, configure name to map to name, with "id" as an output. Configure the lookup operation to be "add new column", and name that column "lookupId". See figure 2.
   • Ignore the other two tabs
7. Configure the output to take all three columns. See figure 3.

That's all. For each row from the source, the name column will be used to match the name column of the lookup table. Each match will contribute its id column as the new lookupId column. All three columns will proceed to the destination.

Figure 1:

Figure 2:

Figure 3:

这篇关于SSIS - 在另一个表上执行查找以获取相关列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！