Thursday, March 18, 2010

SQL 2008 x64 Integration Services and Excel

Brief Description:
On x64 SQL 2008 Integration Services Jobs importing Excel files can be created but fail once imported into Integration Services.

Symptom:
The dtsx job can be created, and if you're creating the job with the import data wizard "Execute Immediately" will also work. Once the job is imported into Integration Services and executed as a job it will fail with the error

"One or more component failed validation. End Error Error: 2010-03-18
12:33:23.93 Code: 0xC0024107 "
This also happens on 2005 x64 with the error
"SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009."
Solution:

On 2008 the solution is so simple I cursed the time I spent trying to follow some of the solutions offered on messageboards.
1. Make sure you have the complete management pack installed (you should have an x86 mssql home location in addition to the default x64, this is specified on install).
2. In your job when adding the SSIS package select "Use 32-bit runtime" on the "Execution Options" tab for the step.
See Microsoft's KBase article on the subject, it took me a while to figure out that the above 2 steps are essentially what the article says to do.

On 2005, it's just a little bit more complicated.
1. Again, make sure you have the complete management pack installed
2. Instead of adding your package as an SSIS job add it as "Operating System(CmdExec)"
3. In the Command box, specify the location of the 32 bit dtexec.exe.
The default path is "\Microsoft SQL Server\90\DTS\Binn\dtexec.exe".
For an SSIS package that lives in Integration services the command should be similar to

"\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /SQL "\"
/SERVER /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
For a file based .dtsx package the command should be similar to
"\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /FILE
"\" /SERVER /MAXCONCURRENT " -1 "
/CHECKPOINTING OFF /REPORTING EW

No comments:

Post a Comment