SSIS读取一个Excel文件中不同Sheet页的几种方案

一、Sheet页固定进入同一个表中：

1.执行SQL任务；（防止重复传入）

2.数据流；

    OLE DB源的任务管理器配置；

    源配置；（将随便一个Sheet页的名称设置成变量）

    OLE DB 目标设置；（创建一个新表）

3.Foreach循环容器；

    创建一个.NET连接管理器：

     将新创建的.NET连接管理器配置到Foreach容器的枚举器设置连接

    配置变量

二、Sheet页进入不同表中：

方案一：通过Foreach循环容器遍历所有Sheet页

业务流程：里面包含了循环文件夹和文件备份

1.在SQL Server数据库中添加一个 Mapping 表（用来将 Sheet Name 进行归类）

CREATE TABLE [dbo].[TableSheetMapping](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[DestTableName] [nvarchar](50) NULL,
	[SheetName] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

2.添加变量：

3.执行sql任务：（从数据库映射出取出每个sheet页对应的表）

SELECT [DestTableName] FROM [dbo].[TableSheetMapping] WHERE [SheetName] = ?

    参数映射：

    结果集：

4.脚本任务：

脚本代码：

public void Main()
		{
            // ReadOnlyVariables User::varSheetName,User::varTableName
            // ReadWriteVariables User::varAreaSheet,User::varParentSheet,User::varPositionSheet

            string tableName = Dts.Variables["User::varTableName"].Value.ToString();
            string sheetName = Dts.Variables["User::varSheetName"].Value.ToString();

            if (tableName == "areaExcel")
                Dts.Variables["User::varAreaSheet"].Value = sheetName;

            if (tableName == "parentExcel")
                Dts.Variables["User::varParentSheet"].Value = sheetName;

            if (tableName == "positionExcel")
                Dts.Variables["User::varPositionSheet"].Value = sheetName;



            Dts.TaskResult = (int)ScriptResults.Success;
		}

5.数据流任务：

     OLE DB源的任务管理器配置；

    根据不同的目标设置不同变量：

    OLE DB目标对应配置：

    脚本任务到数据流添加约束：

方案二：通过脚本任务循环所有Sheet页

    业务流程：

    Get All Sheet 脚本任务：

     脚本代码：

public void Main()
		{
            
        // ReadOnlyVariables User::varFilePath
        // ReadWriteVariables User::varSheets,User::varSheetsCnt

            string excelFile = Dts.Variables["User::varFilePath"].Value.ToString();
            string connectionStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFile + "Extended Properties=Excel 12.0";

            DataTable dataTable = null;
            List<string> tables = new List<string>(); // 表列表

            using (OleDbConnection conn = new OleDbConnection(connectionStr))
            {
                conn.Open();
                dataTable = conn.GetSchema("TABLES");

            }

            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    tables.Add(row["TABLE_NAME"].ToString());
                }

                Dts.Variables["User::varSheets"].Value = tables;
                Dts.Variables["User::varSheetsCnt"].Value = tables.Count;

            }


                Dts.TaskResult = (int)ScriptResults.Success;
		}