October 19, 2011 at 7:58 am
Hi,
I have a package with one dataflow task that consists of a an ADO.NET source using an Informix ODBC driver connection. The source executes a SQL statement that returns one text column from the first 100 records of a table on an informix database. These records are written to a text file destination.
When executed on my pc the file is populated with 100 rows as expected. However when executed on our new server the file is populated with 100 blank records (100 carriage returns).
If I add a data viewer to the data flow task and run the package through Visual Studio on the server I get a data grid with one column as expected however every record is blank. If I click 'Preview' on the ADO.Net data source properties I get a query result of 100 records with values exactly as I expect.
This package works perfectly as expected on my PC and our other servers yet on this new just built server it refuses to read and text values through the Informix ODBC driver. I have been through all of the obviously settings for the ODBC driver and made sure all are identical. I think this is could be a 'Locale' issue but I am stuck for what to try next.
Does anyone have any ideas?
Many thanks
Daniel
October 20, 2011 at 3:32 am
I found another post online which shows the exact same issue but provides a couple of images which show the problem.
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/af4b3294-bcaa-4d9d-944a-a8e039895d35/[/url]
Unfortunately there are no suitable suggestions on this post so I still have the issue.
October 20, 2011 at 6:28 am
Can you connect to the Informix datasource via the ODBC using another tool (e.g. MS Access or Excel) on both your machine and on the server? Are the results the same? I'd be inclined to point my finger at the ODBC driver.
Just a thought,
Rob
October 20, 2011 at 7:15 am
I have tested the ODBC connection using a ODBC test utility which enabled me to connect to my Informix ODBC dsn and run the query in the data flow task. The query returned the data as expected.
The link to the other post has 2 images which show the problem perfectly. When the data flow source query is previewed within visual studio you can see the data in the text columns (suggesting the odbc driver is functioning?) and when the package is executed with a data viewer after the data source the text columns all return blank. Interestingly the rows are returned by all the values are blank. It is quite frustrating because we have had this working for some time on several machines and only this new server has the problem.
Thanks
October 20, 2011 at 7:43 am
Sorry I hadn't looked at the graphics in the link. But you're right if the preview works on the server, then you wouldn't think it's the ODBC driver.
Is there an OLE DB provider for Informix? I'm just grasping at straws as you've got it working on other machines and don't want to switch up a the package.
Rob
October 26, 2011 at 3:15 am
After trying just about everything to fix this we finally found what causes the problem and it appears to be Windows 2008 Service pack 1. Re-installing the OS without SP1 and the issue magically disappears.
Regards
Daniel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply