Cache ODBC dsn problem in Connection Manager

I have created a simple SSIS package using the Import / Export wizard. I have, in Connection Manager, created a DataReaderSrc connection using .Net Providers\ODBC Provider to connect to my exising DSN name of AMPFM which is an ODBC connection to a InterSystems Cache Database. It test successfully but fails when I run the package with the error at the bottom of this text. I have also tried the following connection string which also tests successfully but then fails when I run the package:

Dsn=AMPFM;server=10.11.1.34;uid=_system;port=1972;database=ALL;query timeout=1

SSIS package "Package1.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Error: 0xC0047062 at Data Flow Task, Source - Query [1]: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)

at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "Source - Query" (1) failed validation and returned error code 0x80131937.

Can someone help me with this problem? I need to create several packages to pull data from this Cache database on a regular basis and find myself stuck at this step.

Thanks!

A few more details might help point me/others in the right direction. Specifically:

* Do I understand correctly that when you click "Test Connection" from inside the designer, it's succeeding?

* Can you try running the package in the debugger (i.e. Debug | Start Debugging), and report on whether the connection manager is able to connect there?

* Is it possible that the "Source - Query [1]" component on your Data Flow Task is configured to use a different connection manager than the one you're configuring? (Unlikely, but possible.)

* How are you executing the package (dtexec, dtexecui, designer, etc.) when it fails? Are you running as the same user as you're designing with? Also if you're running under Vista, are you running the package as an Administrator?

* Is your DSN a User or System DSN?

Thanks, -David

|||

Here is additional information David:

This SSIS 2005 package is using a 32-bit ODBC system dsn that worked successfully with SQL Server 2000 DTS packages.

I have a SQL Server 2005 x64 Enterprise database system on Windows 2003 x64 Enterprise.

The source query is using the DataReaderSrc connection manager which connects sucessfully when clicking on "Test Connection"

The package is only being tested in BIDS using Debug with my userid which is also the userid that created the package

Thanks!

|||

Maybe you are running 32-bit ODBC driver in 64-bit mode. Is your driver 32-bit only?

To check this quickly: right-click on your IS project in VS, choose Properties, select the Debugging node and switch the Run64BitRuntime flag to false.

HTH.

|||

That is exactly what was happening, the driver is only 32-bit! I had heard of the Run64BitRuntime flag but could not find it before receiving your instructions above. The job is running correctly now. Thanks so much Bob!