我是 SSIS 新手，所以请耐心等待。
我为 SQL Server 2008 创建了一个 Integration Services 项目，用于将数据从旧数据库导入到新数据库。我需要做的一件事是将数据从两个旧的源表导入到一个新的目标表中。
最好的方法是什么？
我可以使用 tsql 通过简单的内连接查询轻松查看我想要的结果，但使用 SSIS 包却没有任何运气。我当前的方法是一个三步过程：
- 添加 OLE DB 源组件，从我的第一个源表中提取所有列
- 添加查找组件，这是 OLE DB 源组件之后的下一步。在此，我“使用 sql 查询的结果”查询不返回空值的第二个源表，然后将外键 id 从“可用输入列”拖动到可用查找列中的主键。我还选中“可用输入列”中的复选框以添加另外 2 列。
- 添加OLE DB Destination，指向我的目标表。
此过程在第一步失败，而不是在查找步骤失败，并失败并显示错误“查找期间行产生不匹配”。外键不能为空，显然主键也不能为空。我在步骤 中使用了 SQL 语句，这样我可以确保列中没有任何空日期值（有一些），但我仍然收到错误。如果我将第一步失败路径输出到平面文件目标，我会得到一个空的 CSV（在调试模式下观察显示 ~600k 记录进入平面文件）。
在这一点上我很困惑，这似乎应该是一个超级简单的任务。我在网上搜索答案，发现这个 链接 听起来和我遇到的问题完全相同，但更改缓存设置没有帮助。
帮助表示感谢！
I am new to SSIS, so please bear with me.
I created an Integration Services Project for SQL Server 2008 to import data from an old db to a new one. One of the things I need to do is import data from two old source tables into one new destination table.
What is the best way to do this?
I can easily see the results I want with a simple inner join query using tsql, but am not having any luck using the SSIS package. My current approach is a three step process:
- Add OLE DB Source component that pulls all columns from my first source table
- Add a Lookup component, which is the next step after my OLE DB Source component. In this I query the second source table 'using the results of a sql query' that returns no nulls, then drag the foreign key id from the 'available input columns' to the primary key in the available lookup columns. I also check the checkboxes in 'available input columns' to add 2 more columns.
- Add OLE DB Destination, pointed to my destination table.
This process fails at the first step, not at the lookup step, and fails with the error "Row yielded no match during lookup". The foreign key cannot be null, and obviously the primary key can't either. I used a SQL statement in step to so I could make sure I don't get any null date values in the columns (there were a few) but I am still getting the error. If I output the first step failure path to a Flat File Destination, I get an empty CSV (watching in debug mode says ~600k records go into the flat file).
I am pretty stumped at this point and this seems like it should be super easy task. I have scoured the web for answers, and found this link that sounds like the same exact problem I am having, but changing the cache setting didn't help.
Help appreciated!
发布评论
评论（1）
听起来您的查找不匹配。我会手动运行查询并验证两个 OLE DB 源没有空的外键；并且每个外键都与查找表中的某些内容匹配。
这里有一个更简单的方法。使用您在 OLE DB Source 中提到的内连接查询。不要使用表选择，而是通过连接提供 SQL 查询。这让 SQL Server 完成所有繁重的连接工作，然后 SSIS 可以完成传输。
It sounds like you have a mismatch in the lookup. I'd hand run the queries and verify that tha both OLE DB SOurce has no null foriegn keys; and that each foreign-key matches something in the lookup table.
There is a simpler approach here. Use your inner join query you mentioned in the OLE DB SOurce. Don't use the table select, provide your SQL query with the join. This let's the SQL Server do all of the heavy lifting of the join and then SSIS can do the transferring.