使用 SSIS 提取 excel 文件 [英] Extracting excel files with SSIS

问题描述

我正在尝试创建一个 ForEachLoop 容器，用于在源文件夹中提取 excel 文件.我在 ForEachLoop 容器中创建了一个执行 sql 任务，该容器将我的 excel 文件的完整路径存储在 sql server 表中

i am trying to create a ForEachLoop container that extracts excel files within a source folder. i have created an execute sql task within a ForEachLoop container that stores my excel files full paths in an sql server table

现在我不知道如何让它通过该列表并将每个文件提取到一个 ole db 目标表中

and now i can't figure how to make it go through that list and extract each file into an ole db destination table

ps:excel文件有不同类型的数据，列几乎从一个文件变化到另一个(28个文件)

ps: the excel files have different types of data, columns change almost from one file to another (28 files)

你能帮我吗?提前致谢.

can you please help me ? thank you in advance.

推荐答案

它在 for each 循环中不起作用，因为每个电子表格的目标必须是一个与进入的列匹配的表.如果它是 25 个不同的具有相同列类型和列数的电子表格，您可以将所有行插入到一个表中，但听起来您需要为每一行创建单独的数据流.然后，您可以将数据源--> 转换--> Ole Destination 合并到一个数据流(可以并行运行)，并且您将(对于 26 个导入)每个电子表格三个步骤.

It won't work within a for each loop because your destination for each spreadsheet has to be a table that matches the columns coming in. If it was 25 different spreadsheets with the same columns types and number of columns you could insert all the rows into one table but it sounds like you need to create separate data flows for each one. You can then combine the datasource--> transform--> Ole Destination onto one data flow (which could run in parallel) and you would have (for 26 imports) three steps for each spreadsheet.

这篇关于使用 SSIS 提取 excel 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！