英文:
How to do a lookup for the four primary keys columns yet output 6 columns to OLE DB Command
问题
我正在从SQL Server数据库迁移到DB2数据库。我尝试比较SQL Server数据库的主键与DB2数据库的主键，如果它们相同，则执行更新，如果不同，则执行插入。
我的问题是，当我使用主键查找（前四列）时，它只返回前四列，我需要一种方法来获取其他两列，这样我就可以运行OLE DB命令并执行更新。
我想比较四个主键列（FISCAL_YR, LOC_CODE, SYSTEM_ID, SYSTEM_CODE），并查看它们是否存在于目标表中。如果存在，我想更新另外两列（LOC_NAME, ALIAS_NAME），如果不匹配，我想执行插入。
这是我目前用于查找的内容，但它匹配了所有列，我只想使用前四列（FISCAL_YR, LOC_CODE, SYSTEM_ID, SYSTEM_CODE），但将6列（FISCAL_YR, LOC_CODE, SYSTEM_ID, SYSTEM_CODE, LOC_NAME, ALIAS_NAME）输出到OLE DB命令，以便我可以执行更新：
SELECT
FISCAL_YR, LOC_CODE, SYSTEM_ID,
SYSTEM_CODE, LOC_NAME, ALIAS_NAME
FROM
LC1U1.Location_Supertbl1;
这是我想要执行的更新命令：
UPDATE LC1U1.Location_Supertbl1
SET ALIAS_NAME = UPPER('test')
WHERE FISCAL_YR = ?
AND LOC_CODE = ?
AND SYSTEM_ID = ?
AND SYSTEM_CODE = ?
我要如何在SSIS中使用查找转换来实现这一点？
谢谢。
英文:
I'm moving from a SQL Server database to a DB2 database. I'm trying to compare the primary key of the SQL Server database with the DB2 database and if its the same primary key values then do an update if they are not the same do an insert.
My problem is when I use a lookup for the primary key (first four columns) it only returns the first four columns, I need someway of getting the other two columns so I can run a OLE DB command and do the update.
I want to compare the four primary keys columns (FISCAL_YR, LOC_CODE, SYSTEM_ID, SYSTEM_CODE) and see if they exist in the destination table if they do I want to update two other columns (LOC_NAME, ALIAS_NAME) and if they don't match I want to do an insert.
This is what I have for the lookup currently but it matches all the columns I just want to do the first four (FISCAL_YR, LOC_CODE, SYSTEM_ID, SYSTEM_CODE) but output 6 columns (FISCAL_YR, LOC_CODE, SYSTEM_ID, SYSTEM_CODE, LOC_NAME, ALIAS_NAME) to OLE DB Command so I can do an update:
SELECT
FISCAL_YR, LOC_CODE, SYSTEM_ID,
SYSTEM_CODE, LOC_NAME, ALIAS_NAME
FROM
LC1U1.Location_Supertbl1;
This is the update command I want to execute:
UPDATE LC1U1.Location_Supertbl1
SET ALIAS_NAME = UPPER('test')
WHERE FISCAL_YR = ?
AND LOC_CODE = ?
AND SYSTEM_ID = ?
AND SYSTEM_CODE = ?
How do I do this with a Lookup transformation in SSIS?
Thank you.
答案1
得分: 0
我认为问题是您没有从查找组件中请求列。
在用户界面中，您可以在左侧（源）和右侧（查找）之间拖动线条。这定义了查找的相等匹配条件。
您想要做的是在该菜单中检查另外的两列，类似于以下图像。这将在查找组件之后，将LOC_NAME和ALIAS_NAME作为新列添加到我的数据流中。
英文:
I think the issue you're not asking for the columns from the lookup component.
In the UI, you drag lines between the left (Source) and right (Lookup) side. This defines the equality match for the lookup.
What you want to do is check the 2 additional columns in that menu, something like the following image. This will add LOC_NAME and ALIAS_NAME as new columns, after the Lookup Component, into my data flow
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论