> 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:\Testing\TestRanking.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).----------------------------------------------------------------
> ----------------------------