SQL SSIS - 在另一个表上执行查找以获取相关列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1155804/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SSIS - Performing a Lookup on another Table to get Related Column
提问by Saobi
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.
我想在 SSIS 中执行一个 select 语句，但是这个 select 语句从另一个组件中获取一个参数，并且这个 select 语句的列必须用作其他组件的输入。
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.
而上面select语句的“id”内容应该是以后组件可以使用的列。
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?
如果我添加一个“OLE DB 命令”组件，它允许我将其他组件称为输入，但我无法从中生成输出。似乎OLE DB 命令组件仅用于更新/插入语句？
Any ideas on how to do it?
关于如何做到这一点的任何想法？
回答by John Saunders
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:
实际上，这是Lookup 的一个案例。看来您想按名称进行查找并返回 id。很简单。下面是我如何创建一个这样的例子：
- Drag a Data Flow Task onto the design surface. Double-click it to switch to it.
- Create a Connection Manager for my database
- Drag onto the design surface:
- an OLE DB Source
- A Lookup Transform
- An OLE DB Destination
- Connect the Source to the Lookup to the Destination. It's the "Lookup Match Output" we want going to the destination. See figure 1.
- Configure the source. My source table just had id and name columns.
- 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
- Configure the output to take all three columns. See figure 3.
- 将数据流任务拖到设计图面上。双击它以切换到它。
- 为我的数据库创建一个连接管理器
- 拖到设计图面上：
- OLE DB 源
- 查找转换
- OLE DB 目标
- 将源连接到查找到目标。这是我们要去目的地的“查找匹配输出”。见图 1。
- 配置源。我的源表只有 id 和 name 列。
- 配置查找
- 常规选项卡：使用 OLE DB 连接
- 连接选项卡：指定相同的连接，但使用查找表。我的查找表只是 id 和 name，但 name 是唯一的，因此作为查找列更有意义。
- 在列选项卡上，将名称配置为映射到名称，以“id”作为输出。将查找操作配置为“添加新列”，并将该列命名为“lookupId”。见图 2。
- 忽略其他两个选项卡
- 将输出配置为采用所有三列。见图 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.
就这样。对于源中的每一行，名称列将用于匹配查找表的名称列。每个匹配项都会贡献其 id 列作为新的 lookupId 列。所有三列都将前往目的地。
Figure 1:
图1：
Figure 2:
图 2：
Figure 3:
图 3：
