About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Friday, March 29, 2013

Excel Error in SSIS - 64bit runtime error

Running a very simple SSIS 2012; which pulls data from an excel file. I've done this a million times, but all of a sudden I ran into this tidbit when running on a new machine:

Errors:



  • [Excel Source [39]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
  • [SSIS.Pipeline] Error: Excel Source failed validation and returned error code 0xC020801C.
  • [SSIS.Pipeline] Error: One or more component failed validation.
  • Error: There were errors during task validation.
  • [Connection manager "Excel Connection Manager"] Error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
    An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".


On the blogs, people say to download the 64-bit version of the drivers at: http://www.microsoft.com/en-us/download/details.aspx?id=13255
I still had trouble getting it to work, so I changed it to run "not" in 64-bit. Just go to Project/[Name of Project] Properties/Configuration Properties/Debugging under Debug Options and change "Run64BitRuntime" from default value of true to false.




This also means, if you are going to run it in 32-bit, you also have to tell the SQL Agent to run in 32-bit as well. Edit your job and then edit the right job step. Go to the Execution Options pane and check "Use 32 bit runtime". This property will only effect the package (+child packages) called in this job step.

No comments: