SSIS - 动态列映射 [英] SSIS - dynamic column mappings

问题描述

我正在使用 SSIS 进行从 excel 到 OLEDB SQL 的数据转换.我在一个文件夹中有一组工作表，我必须循环遍历并将这些工作表中的每一个中的数据插入到表格中.我有一个场景，我必须遍历一组具有不同列结构的 Excel 工作表.我可以通过 foreach 循环枚举器遍历每个工作表，找到文件名并将它们传递给 Excel 源.

I am using SSIS to do data transformation from excel to OLEDB SQL. I have a set of sheets in a folder that i ll have to loop thru and insert the data in each of these sheets to a table. I have a scenario where i have to loop thru a set of Excel sheets that has different column structures. I can loop thru each sheet thru foreach loop enumerator find the filename and pass them on to the Excel source.

我想知道是否有办法在目标组件中转义此列映射，在我的情况下将是 OLEDB SQL 表.因为这些映射对于每个文件都是不同的.有没有办法动态地做到这一点?

I want to know if there is way to escape this column mappings in the destination component which will be a OLEDB SQL table in my case. Because these mappings are different for each file. Is there way to do this dynamically?

推荐答案

虽然您可以在循环中添加脚本任务来修改映射，但这并不是最简单的事情，因为您必须在代码中显式创建每个映射.一个更简单的解决方案是用带分隔符的文本文件替换 Excel 工作表，并使用批量插入任务将它们导入循环内.只要文件和表中的列顺序相同，您就不必提供任何映射信息.

While you can add a script task inside the loop to modify the mappings, it is not the easiest thing to do as you have to explicitly create each mapping in code. A simpler solution would be to replace excel sheets with delimited text files and import them inside the loop using the Bulk Insert Task. You will not have to provide any mapping info as long as the column order is the same in both files and tables.

如果您不能这样做，您将不得不将映射元数据存储在某处，例如数据库中的一个表并使用它在脚本任务中创建动态映射.

If you can't do that, you will hove to store the mapping metadata somewhere, e.g. a table in your database and use this to create the dynamic mappings in a Script task.

一种稍微简单的方法是仅使用一个脚本任务，该任务使用元数据来配置 SqlBulkCopy 对象.您失去了完整数据流任务的灵活性，但如果您只想在临时表中加载一些文件，那就足够了.此外，配置 SqlBulkCopy 对象比配置数据流任务要容易得多.

A slightly easier way is to use just one script task that uses the metadata to configure an SqlBulkCopy object. You lose the flexibility of a full Data Flow task but if all you want is to load some files in temp talbes, it is good enough. Plus, it's a whole lot easier to configure a SqlBulkCopy object than a Data Flow task.

这篇关于SSIS - 动态列映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！