使用SSIS将多个不同的Excel工作表ETL导入SQL Server 2008的最佳方法 [英] Best Way to ETL Multiple Different Excel Sheets Into SQL Server 2008 Using SSIS

问题描述

我已经看到了许多示例，这些示例说明了如何使用Foreach循环容器枚举Excel工作簿或工作表的集合，并假设所有源文件的数据结构都是相同的，并且数据将进入单个目标表.

I've seen plenty of examples of how to enumerate through a collection of Excel workbooks or sheets using the Foreach Loop Container, with the assumption that the data structure of all of the source files are identical and the data is going to a single destination table.

处理以下情况的最佳方法是什么: -具有10-20张纸的单个Excel工作簿或具有1张纸的10-20个Excel工作簿. -每个工作簿/工作表都有不同的架构 -每个源工作表都有1:1匹配的目标表. -标准清理:将创建工作簿并将其放置在加载"文件夹中，SSIS程序包将在作业上运行，该作业将读取加载文件夹中的文件，并在成功完成后将其移动到存档文件夹中

What would be the best way to handle the following scenario: - A single Excel workbook with 10 - 20 sheets OR 10 - 20 Excel workbooks with 1 Sheet. - Each workbook/ sheet has a different schema - There is a 1:1 matching destination tables for each source sheet. - Standard cleanup: workbooks would be created and placed in a "loading" folder, SSIS package runs on a job that reads the files in the loading folder and moves them to an archive folder upon successful completion

我知道我可以为每个工作簿创建一个单独的SSIS包，但是维护起来确实很痛苦.任何帮助，我们将不胜感激！

I know that I can create a seperate SSIS package for each workbook, but that seems really painful to maintain. Any help is greatly appreciated!

推荐答案

很久以前，我们也面临同样的问题.我将向您总结我们所做的事情. 我们已经使用C#编程编写了一个SSIS程序包.维护一个MetaTable，其中保存了平面文件的信息(表名称，列，这些列在平面文件中的位置.).我们提取平面文件名，然后将其查询到有关该平面文件所属的表，其具有的列以及该平面文件中的列位置的元表.

We faced the same issue long back. I will just summarize you what we have done. We have written an SSIS-package pro-grammatically using C#. A MetaTable is maintained which holds the information (table name, columns, positions of these columns in the flat file.) of the flat files. We extract the flat file name and then query it to the meta-table about the table this flat file belongs to and the columns it is having and the column positions in the flat file.

我们通过将每个平面文件作为命令行参数传递给PackageExe，在SQLSERVER中执行程序包.因此，它将读取并处理每个平面文件.

We execute the package in the SQLSERVER by passing each flat file as a command line argument to the PackageExe. So it reads and processes each flat file.

示例假设我们有一个平面文件 FF ，我们首先提取平面文件的名称，然后通过查询数据库来获取表名称，可以这样说是 TT ，其中包含 COL-1 ， COL-2 列，位置分别为 1至10 和 11分别为20 .现在，通过从MetaTable中读取此信息，我创建了一个派生的列转换(包).

Example Suppose say we have a flat file FF, we first extract name of the flat file and then get the table name by querying to the DB, lets say it is TT which contains columns COL-1, COL-2 with positions 1 to 10 and 11 to 20 respectively. By reading this information from the MetaTable now I have created a derived column transformation (package.)

我们的应用程序在一个文件夹中有一组平面文件，通过使用"For Loop Container SSIS"，我们一次获得一个平面文件并执行上述过程.

Our Application has a set of flat files in a folder and by using "For Loop Container SSIS" we get one flat file at a time and do the above process.

这篇关于使用SSIS将多个不同的Excel工作表ETL导入SQL Server 2008的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！