如何使用SSIS 2008遍历Excel文件并获取工作表名称 [英] How to loop through excel file and get sheetname using ssis 2008

问题描述

我正在尝试从具有非静态工作表名的Excel文件(工作表名包含yyyymmdd，该数据会随每个文件而变化)加载数据到SQL数据库表中.我遵循了

I'm trying to load data from an excel file with a sheetname which is not static (sheetname contains yyyymmdd which would change with each file) into SQL database table. I followed the solution provided on How to loop through Excel files and load them into a database using SSIS package? but could only manage to get the first for loop working. When I'm trying to assign the user variable 'Sheetname' to Excel Source under the Data Flow task, I'm getting the error -

CSSN_Invoice上的错误[连接管理器测试MKBS连接"]:SSIS错误代码DTS_E_OLEDBERROR.发生OLE DB错误.错误代码:0x80004005. OLE DB记录可用.来源:"Microsoft Access数据库引擎"结果:0x80004005说明:无效的参数.".

Error at CSSN_Invoice [Connection manager "TEST MKBS CONNECTION"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Invalid argument.".

数据流任务[MKBS工作表名称[1]]出错:SSIS错误代码DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.对连接管理器"TEST MKBS CONNECTION"的AcquireConnection方法调用失败，错误代码为0xC0202009.在此之前可能会发布错误消息，并详细说明为何AcquireConnection方法调用失败的原因

Error at Data Flow Task [MKBS Sheetname [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "TEST MKBS CONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed

将工作表名称选择为表或视图"而不是表名称或视图名称变量"时，数据流任务运行正常

The data flow task is working fine when a sheetname is picked as 'Table or View' and not as 'Table Name or View Name Variable'

请帮助！

推荐答案

创建一个数据流任务，以将工作表名称读取到ADO对象中.

Create a data flow task to read sheet names into ADO object.

第一项是作为源的脚本组件. 我有一个用于Excel电子表格的连接字符串的变量

First item is a script component as a source. I have a variable for connection string to the Excel Spreadsheet

创建SheetName的输出

Created an Output of SheetName

以下是读取标签页名称的代码:

Here's the code to read tab names:

您基本上是使用oleDB打开电子表格. 将表名称放入数据表中

You are basically opening the spreadsheet with oleDB. Putting the table names into a data table

遍历数据表并写出要输出的行.

Looping through the data table and writing out the rows to output.

确保关闭连接！！！如果不这样做，以后可能会导致错误.

Make sure to close the Connection!!! This may cause errors later if you don't.

下一步是有条件的拆分，由于某种原因，结果中的选项卡名称重复，并且它们都以'_'结尾.

The next step is a conditional split as for some reason the result has duplicates of tab names and they all end in an '_'.

下一步是派生一列，以清除exta'"的工作表名称

Next step is deriving a column to clean the sheet name of exta "'"

创建对象类型的变量:我将其命名为ADO_Sheets

Create a Variable of type Object: I named mine ADO_Sheets

插入记录集目标对象: 1.将变量设置为刚创建的变量 2.映射列以获得干净的工作表

Insert a recordset destination object: 1. Set the variable to the variable you just created 2. Map the columns for clean Sheet

现在回到控制流并设置一个foreach循环控件:

Now back to the Control Flow and set up a foreach loop control:

配置foreach ... 枚举器:Foreach ADO枚举器 资料来源:ADO_Sheets 变量映射:设置为一个名为SheetName的变量

Configure the foreach... Enumerator: Foreach ADO Enumerator Source: ADO_Sheets Variable Mapping: Set to a variable called SheetName

我在循环中有一个功能任务，但是为了便于理解，它可能在变量中:

I have a Function Task inside the loop but it is more for ease of understanding, it could have been down in the variables:

现在，您可以选择此变量以从该页面提取数据.

This variable is now your select for extracting the data off that page.

最后一个是您要运行的数据流任务.

Last is the data flow task you want to run.

很多工作，但是我经常使用，以为可以分享！！！

这篇关于如何使用SSIS 2008遍历Excel文件并获取工作表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！