May 28, 2009 at 2:26 pm
Ok. It seems I'm mere inches from the finish line on this one.
I've got a ForEach File container that is referencing all (12 or so) .xls files in a given directory. If I hard code the connection string thusly...
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Z:\\03-31-2009\\BONY\\my_awesome_excel_file.XLS;Extended Properties=\"Excel 8.0;HDR=YES\";"
... it works (albeit for the same file 12 times).
When I try to insert the User Variable that contains that unwieldy path\filename.xls in the hopes of getting all 12 distinct files imported, it fails.
Here's the string as it looks after I insert the User Variable which is being fed in the Index 0 spot of the ForEach container:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::xls_FileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"
As soon as I click off the connection string object, my XLS source object is invalidated with the following error:
"Error at XLS Data Flow Task [Excel Source[66780]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009
...
"Description: "Invalid argument.".
Is the order of events critical? Do I need to delay validation or something like that?
THANK YOU!
May 28, 2009 at 2:36 pm
Yes its trying to validate. set Delay Validation to True for the dataflow where you are using the Excel file.
One more point, you can set the expression for ExcelFilePath property rather than the whole connection string.
HTH
Mukti
May 28, 2009 at 2:52 pm
Thanks Mukti, but I tried both suggestions (DelayValidation = True, and setting the ExcelFilePath property to my variable), but got the following message (basically the same one, but abridged):
Error at myPkg [Connection manager "Excel Connection Manager"]: 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 XLS Data Flow Task [Excel Source [7844]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC020202009
Additional information:
-> Exception from HRESULT: 0xC0s0801C (Microsoft.SqlServer.DTSPipelineWrap)
May 28, 2009 at 3:07 pm
Where are you setting the ExcelFilepath?
And just to doublecheck you are setting the delay validation trur for DataFlow task where this xls file is going to be used as a source?
May 28, 2009 at 3:48 pm
Yeah, the DelayValidation is set to true for ALL of the data flow, ForEach looper, and the Excel Connection Manager.
I found a similar post here that I've been working off:
http://www.sqlservercentral.com/Forums/Topic295072-148-1.aspx
And, I'm pretty sure I've got everything configured correctly, but now I'm getting a message that mentions the worksheet name of the initial .xls I seed the Excel Connection Manager with.
None of the files have worksheets of the same name, but all files only contain 1 sheet. I'm beginning to think that the worksheet name is persisting somehow, from the file I use to see the mapping, into the subsequent files that get looped through at runtime.
I tried taking out the reference to the worksheet name but I get "A destination table name has not been provided."
UNGH!
May 28, 2009 at 3:50 pm
sorry... I didn't answer your question...
I'm not currently setting the ExcelFilePath, rather, I'm setting the ConnectionString object. I've tried both though with the same deflating results.
May 28, 2009 at 8:06 pm
Greg J (5/28/2009)
Yeah, the DelayValidation is set to true for ALL of the data flow, ForEach looper, and the Excel Connection Manager.I found a similar post here that I've been working off:
http://www.sqlservercentral.com/Forums/Topic295072-148-1.aspx
And, I'm pretty sure I've got everything configured correctly, but now I'm getting a message that mentions the worksheet name of the initial .xls I seed the Excel Connection Manager with.
None of the files have worksheets of the same name, but all files only contain 1 sheet. I'm beginning to think that the worksheet name is persisting somehow, from the file I use to see the mapping, into the subsequent files that get looped through at runtime.
I tried taking out the reference to the worksheet name but I get "A destination table name has not been provided."
UNGH!
This article might help you with the worksheet naming problem:
http://technet.microsoft.com/en-us/library/ms345182.aspx
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 29, 2009 at 2:59 pm
I've tried every permutation of every suggestion given to me and it just won't work. If someone's got a .dtsx they could send me that imports all .xls files in a particular directory which contain different named sheets, I'll be amazed and obliged, but for now we've dodged the issue by converting the source .xls's to flat files.
It's laughable to me that a Microsoft source (Excel), going into a Microsoft database (SQL Server) using a Microsoft ETL tool (SSIS) should be so unintuitive.
December 23, 2010 at 7:48 am
I am experiencing exactly the same problem with my import. Please help.
December 23, 2010 at 8:39 am
Might be a red herring but... does the last comment from sql.techo in this post help?
http://www.sqlservercentral.com/Forums/Topic793585-147-1.aspx#bm821697
December 26, 2010 at 7:46 am
Greg J (5/29/2009)
I've tried every permutation of every suggestion given to me and it just won't work. If someone's got a .dtsx they could send me that imports all .xls files in a particular directory which contain different named sheets, I'll be amazed and obliged, but for now we've dodged the issue by converting the source .xls's to flat files.It's laughable to me that a Microsoft source (Excel), going into a Microsoft database (SQL Server) using a Microsoft ETL tool (SSIS) should be so unintuitive.
A bit late to comment on this, but anyway...
You can compare an Excel file with a database and the various sheets in the Excel file with tables.
What you are essentially trying to do is loop over the databases and read the data, but without specifying the table. That won't work. So of course it won't work with Excel and SSIS. You must specify the sheet name.
To solve your issue, make sure all the sheets have the same name. Or follow the suggestion from Martin Gleeson.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply