Data source name not found

Hello, I am trying to retrieve data from an Informix UniData database to SQL
Server. I am doing all the setup and work from the SQL Server. I have
created the package in SSIS and the Connection Manager item (which is setup
as a System DSN on the server) tests ok when manually tested. However, when
the package is executed, it seems it can't make the connection to the
Informix database. This is what is reported:
SSIS package "Test1.dtsx" starting.
Information: 0x4004300A at Sample Data, DTS.Pipeline: Validation phase is
beginning.
Error: 0xC0047062 at Sample Data, M2K [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.IDTSConnec tionManager90.AcquireConnection(Object
pTransaction) at
Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceA dapter.AcquireConnections(Object
transaction) at
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentH ost.HostAcquireConnections(IDTSManagedComponentWra pper90 wrapper, Object transaction)
Error: 0xC0047017 at Sample Data, DTS.Pipeline: component "M2K" (1) failed
validation and returned error code 0x80131937.
Error: 0xC004700C at Sample Data, DTS.Pipeline: One or more component failed
validation.
Error: 0xC0024107 at Sample Data: There were errors during task validation.
SSIS package "Test1.dtsx" finished: Failure.
The program '[3896] Test1.dtsx: DTS' has exited with code 0 (0x0).
The Informix ODBC driver is 32 Bit and Windows Server 2003 and SQL Server
2005 are 64 Bit installs.
Any suggestions on why everything is ok and SSIS can connect to the Informix
database while configuring the column mappings etc, but fails upon execution?
Scott
I've found the issue. I needed to set the Run64BitRuntime to False in the
Properties of the Project. The default is for the project to use 64 Bit
drivers and my Informix driver is 32 Bit only.
"Scott" wrote:

> Hello, I am trying to retrieve data from an Informix UniData database to SQL
> Server. I am doing all the setup and work from the SQL Server. I have
> created the package in SSIS and the Connection Manager item (which is setup
> as a System DSN on the server) tests ok when manually tested. However, when
> the package is executed, it seems it can't make the connection to the
> Informix database. This is what is reported:
> SSIS package "Test1.dtsx" starting.
> Information: 0x4004300A at Sample Data, DTS.Pipeline: Validation phase is
> beginning.
> Error: 0xC0047062 at Sample Data, M2K [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.IDTSConnec tionManager90.AcquireConnection(Object
> pTransaction) at
> Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceA dapter.AcquireConnections(Object
> transaction) at
> Microsoft.SqlServer.Dts.Pipeline.ManagedComponentH ost.HostAcquireConnections(IDTSManagedComponentWra pper90 wrapper, Object transaction)
> Error: 0xC0047017 at Sample Data, DTS.Pipeline: component "M2K" (1) failed
> validation and returned error code 0x80131937.
> Error: 0xC004700C at Sample Data, DTS.Pipeline: One or more component failed
> validation.
> Error: 0xC0024107 at Sample Data: There were errors during task validation.
> SSIS package "Test1.dtsx" finished: Failure.
> The program '[3896] Test1.dtsx: DTS' has exited with code 0 (0x0).
> The Informix ODBC driver is 32 Bit and Windows Server 2003 and SQL Server
> 2005 are 64 Bit installs.
> Any suggestions on why everything is ok and SSIS can connect to the Informix
> database while configuring the column mappings etc, but fails upon execution?
> Scott
>

Data source name not found

Hello, I am trying to retrieve data from an Informix UniData database to SQL
Server. I am doing all the setup and work from the SQL Server. I have
created the package in SSIS and the Connection Manager item (which is setup
as a System DSN on the server) tests ok when manually tested. However, when
the package is executed, it seems it can't make the connection to the
Informix database. This is what is reported:
SSIS package "Test1.dtsx" starting.
Information: 0x4004300A at Sample Data, DTS.Pipeline: Validation phase is
beginning.
Error: 0xC0047062 at Sample Data, M2K [1]: System.Data.Odbc.OdbcExceptio
n:
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name
not found and
no default driver specified at
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConne
ction(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 Sample Data, DTS.Pipeline: component "M2K" (1) failed
validation and returned error code 0x80131937.
Error: 0xC004700C at Sample Data, DTS.Pipeline: One or more component failed
validation.
Error: 0xC0024107 at Sample Data: There were errors during task validation.
SSIS package "Test1.dtsx" finished: Failure.
The program '[3896] Test1.dtsx: DTS' has exited with code 0 (0x0).
The Informix ODBC driver is 32 Bit and Windows Server 2003 and SQL Server
2005 are 64 Bit installs.
Any suggestions on why everything is ok and SSIS can connect to the Informix
database while configuring the column mappings etc, but fails upon execution
?
ScottI've found the issue. I needed to set the Run64BitRuntime to False in the
Properties of the Project. The default is for the project to use 64 Bit
drivers and my Informix driver is 32 Bit only.
"Scott" wrote:

> Hello, I am trying to retrieve data from an Informix UniData database to S
QL
> Server. I am doing all the setup and work from the SQL Server. I have
> created the package in SSIS and the Connection Manager item (which is setu
p
> as a System DSN on the server) tests ok when manually tested. However, wh
en
> the package is executed, it seems it can't make the connection to the
> Informix database. This is what is reported:
> SSIS package "Test1.dtsx" starting.
> Information: 0x4004300A at Sample Data, DTS.Pipeline: Validation phase is
> beginning.
> Error: 0xC0047062 at Sample Data, M2K [1]: System.Data.Odbc.OdbcExcept
ion:
> ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source nam
e not found and
> no default driver specified at
> Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireCon
nection(Object
> pTransaction) at
> Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnection
s(Object
> transaction) at
> Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnectio
ns(IDTSManagedComponentWrapper90 wrapper, Object transaction)
> Error: 0xC0047017 at Sample Data, DTS.Pipeline: component "M2K" (1) failed
> validation and returned error code 0x80131937.
> Error: 0xC004700C at Sample Data, DTS.Pipeline: One or more component fail
ed
> validation.
> Error: 0xC0024107 at Sample Data: There were errors during task validation
.
> SSIS package "Test1.dtsx" finished: Failure.
> The program '[3896] Test1.dtsx: DTS' has exited with code 0 (0x0).
> The Informix ODBC driver is 32 Bit and Windows Server 2003 and SQL Server
> 2005 are 64 Bit installs.
> Any suggestions on why everything is ok and SSIS can connect to the Inform
ix
> database while configuring the column mappings etc, but fails upon executi
on?
> Scott
>

Data Flow Task Stuck

I have a simple data flow task setup...

2 ADO.NET connection managers, each referencing a DSN pointed to a Unidata database.

2 DataReader sources, each using a single ADO.NET connection managers, running a simple SELECT statement from a table.

I have a Union All transform setup to merge the data and write to a OLE DB Destination (SQL05 database)

When I run the package, each source will validate, but only one will execute. The other source will do nothing. The data source will be colored yellow, and will just sit there. The package will just sit, almost like it is waiting for input.

This behavior is not consistent, however. It varies which data source will hang, pretty much 50-50. About 25% of the time, both sources will execute, and all rows will be written to the destination.

Any help is appreciated.

thanks

Are the source connections pointing to the same database? I'm not familiar with Unidata, but it sounds like there might be some type of blocking issue, either in the database or in the ODBC driver. If you use two separate data flows, one per DataReader Source, does it consistently run successfully?|||No, they are not pointing to the same database, and I have tried independent data flows with the same results.

|||

When you tried independent data flows, did you add a precedence constraint so that they were executing sequentially rather than in parallel?

If the problem shows up when you are only running a single DataReader Source, I'd see if there are any known issues with the ODBC driver.

|||When I try them sequentially, it works correctly every time.

This is an acceptable workaround, but I am still curious to determine the reason for this strange behavio

|||

Corey M. wrote:

When I try them sequentially, it works correctly every time.

This is an acceptable workaround, but I am still curious to determine the reason for this strange behavio

My guess is that it's an ODBC driver issue. Perhaps table locking or something like that. Who knows. You'll have to check for any known issues with the ODBC driver provider.

|||

You may wish to try the flowsync transform, placing one FlowSync transform between each source adapter and the union all transform (for a total of two FlowSync transforms).

The FlowSync transform is a speed governor, so a particular source flow doesn't get too far ahead of the other one. For something like union all, one source outpacing another should not be an issue, but you could certainly give it a shot.

To install the transform, you'll need to copy the assembly (FlowSync.dll) into two places and add it to the toolbar.

1. For runtime purposes, copy the FlowSync.dll into the global assembly cache, which is "%windir%\assembly" or typically the directory c:\windows\assembly. For design-time purposes, copy the same FlowSync.dll into the Integration Service PipelineComponents directory, which is typically located at "%ProgramFiles%\Microsoft SQL Server\90\DTS\PipelineComponents", If you're running 64 bit, the design-time installation directory is typically located at "%ProgramFiles(x86)%\Microsoft SQL Server\90\DTS\PipelineComponents".

2. To add flowsync transform to the toolbar, open up BIDS, right click on the toolbox, select "Choose Items...", go to the tab SSIS Data Flow Items, and Select the FlowSync transform, which will then show up appear in the toolbox as an available transform.