SSIS 从表中选择 VALUE 而不进行查找
我对 SSIS 相当陌生，
我正在从 XLS 电子表格导入数据库表。 在此过程中，我想从表中选择一条记录，但它不是查找，即：不与输入源连接的直接 SELECT。 然后我想将其与 XLS 中的其他行合并。
做这个的最好方式是什么？ 变量？ OLE DB 命令？
谢谢
I'm fairly new to SSIS,
I'm importing from an XLS spreadsheet into a database table. Along the way I want to select a record from a table, but it is NOT a lookup, ie: a straight SELECT with no join from input source. Then I want to merge this along with the other rows from the XLS.
What is the best way to do this? Variables? OLE DB commands?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以使用 OLE DB 命令，但要记住的重要一点是它是按行触发的，并且可能会很慢。 您仍然可以使用查找来实现此目的，但请确保在查找转换不包含您要查找的匹配项的值时，使用设置错误输出来忽略查找错误。
您还可以使用具有外连接条件而不是内连接的合并转换。
You could use an OLE DB command but the important thing to remember about this is that it is fired on a per-row basis and could potentially be slow. You can still use a lookup for this purpose, but make sure that you use set the error output to ignore lookup errors for the cases when the lookup transformation does not contain an value for the match you are looking for.
You could also use a merge transformation with an outer join condition rather than an inner join.
如果您从数据库表中检索的记录不依赖于电子表格中的行中的数据，那么每行的记录可能都是相同的 - 这是您所希望的吗？
在这种情况下，我会考虑在控制流中使用执行 SQL 任务来检索记录并将其保存到变量中。 您可以使用数据流中的脚本组件将记录中的值从变量复制到每行中的相应字段。 这意味着查找数据仅检索一次，而不是每行检索一次，这会像上面 jn29098 所说的那样慢。
如果数据流的目标与从中提取“查找”记录的数据库相同，那么您还可以考虑使用执行 SQL 任务（在控制流中）在电子表格数据完成后添加查找值。到达数据库（数据流完成后）。 这样效率会高很多。
If the record that you are retrieving from the database table is not dependent on the data within the row from the spreadsheet then it will probably be the same for each row - is that what you are hoping for?
In this case, I would consider using an Execute SQL Task in the Control Flow to retrieve the record and save it to a variable. You can use a Script Component in the Data Flow to copy the values in the record from the variable to the appropriate fields in each row. This will mean that the lookup data is retrieved only once and not once per row which is slow as jn29098 said above.
If the target for your Data Flow is the same database as the one from which you are extracting the 'lookup' record then you could also consider using an Execute SQL Task (in the Control Flow) to add the lookup values once the spreadsheet data has arrived in the database (once the Data Flow has completed). This would be much more efficient.