data synchronization

I have one Informix in OpenVMS machine and another MS SQL in Windows machine. I cannot touch the Informix server at all.

How to do the extract/update the data periodically from Informix to SQL? I was thinking of writing a service. But I don't know how to do the incremental update.

I was thinking of doing a pull subscription from MS SQL. Is it feasible? Is it a replication or integration service? I dunno. Please help.

Thank you.You don't need to write a service. There already is a service. Use DTS.

I do a nightly tranfer of data from Informix to SQL Server for one of my steady clients.

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
>