SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

[le1234]

I have a DTS package that has been migrated to SSIS (basically it pulls data from a table in and access db and dumps into a sql db table) - I have updated the connections as required and tested that they work correctly (I can preview the data within the access table that I want to copy over).
However when I run the SSIS package I get the following error:
SSIS package "LLPG_FILE_IMPORT into Live APPS LLPG.dtsx" starting.
Information: 0x4004300A at Copy Data from Pd_Addresses to APPS dbo LLPG Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at LLPG_FILE_IMPORT into Live APPS LLPG, Connection manager "Connection 1": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
Error: 0xC020801C at Copy Data from Pd_Addresses to APPS dbo LLPG Task, OLE DB Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Connection 1" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Copy Data from Pd_Addresses to APPS dbo LLPG Task, DTS.Pipeline: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Copy Data from Pd_Addresses to APPS dbo LLPG Task, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Copy Data from Pd_Addresses to APPS dbo LLPG Task: There were errors during task validation.
SSIS package "LLPG_FILE_IMPORT into Live APPS LLPG.dtsx" finished: Failure.

Is anyone able to give any advice??

 

[mrdenny]

Which connection is Connection 1, the SQL Server or the Access database?

Is this happening on your workstation or the server?

Is the server 32bit or 64bit? What about the workstation that you developed the package on?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[le1234]

connection 1 is the connection to the access db (if I open that connection I can see the database I am wanting to import).
all work carried out on the server
server is 64bit

Thanks

 

[mrdenny]

It gets tricky connecting to an Access database when the SQL Server is 64bit.

This thread should have the info you need thread1555-1375902.

You want to set the Run64BitRunTime = False.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[MDXer]

DOn't you have to run the package in 32 bit since the jet driver is only 32bit. I believe I read this somewhere.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mrdenny]

That's what the flag is suppose to do.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[le1234]

Thanks for you help so far - I set the flag to Run64BitRunTime = False and I can now run the SSIS package successfully in VS and Management Studio however when I schedule as a job I still get the SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER error.
any further ideas?
I have read so many posts on this and people seem to be getting it to work but I have tried their solutions and still no luck!!!