Loop through multiple excel files

Question

Post reply

Loop through multiple excel files

PSB

SSChampion

Points: 13195
More actions
August 31, 2010 at 8:53 am

#228509

Hi,
I want to loop through several excel files through SSIS and also through multiple sheets .
Any help appreciated .
Thanks,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ludo-ZX7R SSCrazy Points: 2882 More actions · Answer 1

Hi

Please bear in mind that this is only part of the solution you are looking for.

In the past I have used the following in order to loop through different Excel files:

- Create a Variable

- Create a "For Loop" Container

- In the Collection of your Conatiner you can specify the Folder and a mapping for the file names

- In the Variable Mapping of the Container specify your Variable

When you run this SSIS Package all the Tasks included as part of your Container will be able to access whatever file is specified in your Variable.

Ludo

PSB SSChampion Points: 13195 More actions · Answer 2

I get the error when I add a data flow task inside the for each loop container . Inside the Data flow task, i added a execl source .

In the excel Source Editor

Data access mode : Table or view named variable

Name of the excel sheet :variablename

When I hit Columns I get the below error

===================================

Error at ForEach [Connection manager "Excel Connection Manager 1"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Invalid argument.".

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

(Microsoft Visual Studio)

===================================

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

Ludo-ZX7R SSCrazy Points: 2882 More actions · Answer 3

This may be due to the fact that you do not have a file with the correct name in the directory specified in the properties of your Container.

Put an .xls file in there for test purposes and give it a go.

Please note that you'll have to specify "delay validation" at property level (don't remember which one) so each task get validated only when it is executed.

Hope this helps.

Ludo

WayneS SSC Guru Points: 95460 More actions · Answer 4

I think that you also have to deal with the spreadsheet tab names - if they are unknown, SSIS won't be able to open up your spreadsheet.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 5

As wayne has already mentioned about Excel sheets,

You may implement either an OPENROWSET query or a routine T-SQL with sheeet names

For more customization...Script Task may be handy

Raunak J