SQL Server Performance Forum – Threads Archive

Problem with ForEach container for Excel file impo

What I’m trying to achieve is a SSIS package that will pick up 1 or more excel files, process the data in them via the conditional splitter, pushing the good data into a table, and all other rows into an error table. I’m having some issues using the ForEach container to process multiple excel spreadsheets into tables. The excel import into the tables is more or less working (imports data for good cases, but uses a null if the Excel Source when it gets an unexpected value – but that’s a seperate problem). I found something related to this when searching, but it related to CTPs (June and September) and trying to reuse the connection strings they built up there (using my own variable names, naturally) causes a ‘Property Value failure’:
——————————————————————————–
The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.
——————————————————————————– I attemtpted to use this:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::RankingFileFullPath] + ";Extended Properties="Excel 8.0;HDR=YES";" The excel importer works fine as a stand-alone component. Trying to use the process defined in ‘Profession SQL Server Integration Services’ pp140, I tried to use an expression to assign the variable value to the connection string. I get a validation error:
——————————————————————————–
Error at Import TPNB Ranking Excel spreadsheets [Excel Source [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
Error at Import TPNB Ranking Excel spreadsheets [DTS.Pipeline]: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
Error at Import TPNB Ranking Excel spreadsheets [DTS.Pipeline]: One or more component failed validation.
Error at Import TPNB Ranking Excel spreadsheets: There were errors during task validation.
Error at Excel Importer [Connection manager "Excel Connection Manager"]: An OLE DB error has occurred. Error code: 0x80040E4D.
——————————————————————————– Any advice? ….
…. in addition …. I attempted to change the package – I set the Data Flow validation to Delay Validation, and changed the expression to change from:
ConnectionString @[User::RankingFileFullPath]
to
ExcelFilePath @[User::RankingFileFullPath] This allowed the package to start debugging, and gave more information in the failure:
——————————————————————————————– SSIS package "Excel Importer.dtsx" starting.
SSIS breakpoint ‘Break when the container receives the OnPreExecute event’ at executable ‘Excel Importer’ has been hit
SSIS breakpoint ‘Break when the container receives the OnPreExecute event’ at executable ‘Foreach Loop Container’ has been hit
SSIS breakpoint ‘Break when the container receives the OnQueryCancel event’ at executable ‘Excel Importer’ has been hit
Information: 0x4004300A at Import TPNB Ranking Excel spreadsheets, DTS.Pipeline: Validation phase is beginning.
Warning: 0x802092A7 at Import TPNB Ranking Excel spreadsheets, ProductSalesRank Table [278]: Truncation may occur due to inserting data from data flow column "Rank" with a length of 1000 to database column "SalesRank" with a length of 50.
Error: 0xC0202009 at Excel Importer, 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: "Unrecognized database format ‘D:TestingTestRanking.xls’.".
Error: 0xC020801C at Import TPNB Ranking Excel spreadsheets, Excel Source [1]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
Error: 0xC0047017 at Import TPNB Ranking Excel spreadsheets, DTS.Pipeline: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Import TPNB Ranking Excel spreadsheets, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Import TPNB Ranking Excel spreadsheets: There were errors during task validation.
SSIS breakpoint ‘Break when the container receives the OnQueryCancel event’ at executable ‘Excel Importer’ has been hit
Warning: 0x80019002 at Foreach Loop Container: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS breakpoint ‘Break when the container receives the OnQueryCancel event’ at executable ‘Excel Importer’ has been hit
SSIS breakpoint ‘Break when the container receives the OnQueryCancel event’ at executable ‘Excel Importer’ has been hit
SSIS breakpoint ‘Break when the container receives the OnWarning event’ at executable ‘Excel Importer’ has been hit
Warning: 0x80019002 at Excel Importer: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS breakpoint ‘Break when the container receives the OnPostExecute event’ at executable ‘Excel Importer’ has been hit
SSIS package "Excel Importer.dtsx" finished: Failure.
The program ‘[2460] Excel Importer.dtsx: DTS’ has exited with code 0 (0x0).——————————————————————————————– Panic, Chaos, Disorder … my work here is done –unknown

Apparently, I added too much detail, and poeple are avoiding the question <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> I’ve tested doing exactly the same thing, but using CSV’s as the source (i.e. text files and not excel files). And it works fine. So there’s nothing wrong with the environment, DB etc. …aaarg Panic, Chaos, Disorder … my work here is done –unknown

Hi there! I don’t know if you’re reading this, but I came across your message in my quest for my own answers. I was getting the same error, but in a SSIS package run from the IDE. So I hope this helps someone (all for one, one for all, right?). So I had a package that runs about twenty others. When each is run in isolation I have no problems: they open Excel files, import the data, transform, and spit it out.
But when the mega-package(tm) is run, then many of the sub-packages start giving this "AcquireConnection method" error. So I tried to do the Delay Validation trick, and some of it worked. But still many of the packages were failing. My packages are pretty simple: At the package Control Flow level I might have a SQL Task that deletes from a target SQL Server table. Then I will have the Data Flow after that. In the flow I will have the first step as the Excel Source, then a Script Component that changes some things around, and then an OLEDB destination that puts the data into the table. What I was finding was that despite the Delay Validation = True, the package was still having problems. I reasoned that the multiple packages were falling over each other in opening and closing the Excel files, and possibly in executing the Excel.exe process. I don’t know much about process management, and it’s not something I would care to test, so I decided to let them wait. So before EVERY Data Flow task (in the Control Flow) I put a Script Task Control Flow Item with the following code it it: Public Sub Main() System.Threading.Thread.Sleep(5000) Dts.TaskResult = Dts.Results.Success End Sub And things seem to work without fail. I hope this helps.
Ken

Hi Ken, I’ll try and work through this soon-ish (next day or 2) – things are pretty hectic at the moment at work (only positive is I can now official turn away every ‘wander-over-to-the-desk’ requester to my manager, who then sends them on their way <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> ) Panic, Chaos, Disorder … my work here is done –unknown

]]>