Loop container for Excel files

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Loop container for Excel files

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-09-16 : 14:38:28

Dear All,

I am using loop container for Text files and excel files as a source.
For Text files source i am able to give the connection manager(FLAT FILE CONNECTION) and loop through all the files and it works fine for me.

But with the excel files i am not able to loop through.
Which source should i need to select to loop through for excel(I mean excel files are in folder).

Please help me in this regard.
Thanks,
Gangadhar

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-17 : 04:40:32

The Excel source extracts data from worksheets or ranges in Microsoft Excel workbooks.
http://msdn.microsoft.com/en-us/library/ms141683(SQL.90).aspx

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-09-17 : 06:05:46

Actually i am using the List of files to iterate through loop container
http://www.sqllion.com/2009/06/programming-foreach-loop-container-%E2%80%93-enumerating-excel-files/

and
http://msdn.microsoft.com/en-us/library/ms345182(SQL.90).aspx
I did as said in the above link it is throwing an error:

TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at Excel Iteration: The connection "{DEA19AB2-35A8-47BC-A37A-C1EDD3B490FE}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Error at Data Flow Task [DTS.Pipeline]: Cannot find the connection manager with ID "{DEA19AB2-35A8-47BC-A37A-C1EDD3B490FE}" in the connection manager collection due to error code 0xC0010009. That connection manager is needed by "runtime connection "OleDbConnection" (90)" in the connection manager collection of "component "Excel Source" (83)". Verify that a connection manager in the connection manager collection, Connections, has been created with that ID.

Error at Data Flow Task [DTS.Pipeline]: component "Excel Source" (83) failed validation and returned error code 0xC004800B.

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

Pls help me if anybody has faced this kind problem before.
Thanks in advance
Gangadhar

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-09-17 : 12:09:51

Guys can any one help me out in this regard.

When i execute i am getting this error
TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

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

Error at Data Flow Task [DTS.Pipeline]: component "Excel Source" (585) failed validation and returned error code 0xC020801C.

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

Error at Excel Iteration [Connection manager "Excel Connection Manager"]: 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.".

(Microsoft.DataTransformationServices.VsIntegration)

------------------------------
BUTTONS:

OK
------------------------------

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-09-17 : 12:39:09

I am using the following connection string to connect to excel:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"

Is there something related to Microsoft.Jet.OLEDB.4.0 ?

I have used the @[User::varFileName] with a flat file connection successfully.

Please help me out in this regard.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-09-17 : 12:54:12

Dear All.I got the root coause for this issue,can somebody help me in resolving the same.
As i am using expression to get the file name:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"

When i click evaluate Expression i am getting
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties="Excel 8.0;HDR=YES";
with data Source as Empty.It is not picking the Data Source filename
Can u pls somebody help me out in this regard.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-09-17 : 13:23:21

All,
Now i am able to make the source is comign with Full Excel sheet path.
But when i execute the package i am getting the error:
TITLE: Microsoft Visual Studio
------------------------------

Error at Excel Iteration [Connection manager "Excel Connection Manager"]: 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: "The Microsoft Jet database engine cannot open the file 'E:\SampleExcelFiles'. It is already opened exclusively by another user, or you need permission to view its data.".

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

------------------------------
ADDITIONAL INFORMATION:

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

------------------------------
BUTTONS:

OK
------------------------------

Pls help me out

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-17 : 13:31:29

I see no file name in "'E:\SampleExcelFiles'"

No, you're never too old to Yak'n'Roll if you're too young to die.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-09-17 : 13:35:59

Hey WebFred thanks a lot It worked for mee...
I missed the giving the file name within the folder.Thanks a million

Thanks a lot for u ...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-17 : 18:10:26