Problem extracting character/string values from an Informix database using SSIS 2008

Feb 18, 2012 at 1:53 AM

I am using SSIS 2008 and having a problem with pulling character data values from an Informix source. The etl runs fine, it pulls records from the source database and poulates them in the target, but all the fields that are char/varchar data type comes out blank in the target (no prob loading numerical values). I have tried loading data in a SQL Server table and also in a text file, the result is the same.

Here is info on diff software ver etc.:
The etl is running on a Windows Server 2008 R2 standard (64 bit OS). SSIS ver is 2008, 64 bit run time for the project is disabled (etl is running in 32 bit mode). Informix ODBC is ver 3.70.TC3 it's a 32 bit driver, and DSN has been setup under 32 bit ODBC (C:\Windows\SysWOW64\odbcad32.exe).

I am using ADO NET component to connect to the ODBC dsn. After writing my query in the ADO Net, when clicked on the "preview" button I can see all the correct data. Also, I am able to run a query against this ODBC dsn using the "Server Explorer" tool in BIDS -- query result shows all the correct values, I can copy the result and paste it in a notepad. But when the etl is run, values for all the fields that are char/varchar comes out blank in the target.

Please let me know if any additional info is required.

Any help/insight into it will be greatly appreciated.