locked
ADO.Net Connection RRS feed

  • Question

  • Question:I am trying to use the ADO.Net as a data flow source to connect to informix database.
    every thing work fine the connection the query and everything. BUT I have a simple select statment Number, Name.
    the query work fine and i can c the data when i run the package i lose all the information from the name column.
    I have number and empty name, I add a dataviewr to c whats come out from the datasource and that what I have, Any help?

    Thanks
       
    Sql resault
    Sql resault
       
       
    Data viewer
    Data viewer
       
    Wednesday, September 21, 2011 6:29 PM

Answers

All replies

  • Place the dataviewer after the Date Conversion, what do you see?
    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, September 21, 2011 6:56 PM
  • same things, nothing
    Thursday, September 22, 2011 3:12 PM
  • Hi Naelodeh,

    SSIS is a .NET application, probably it helps to install the Informix .NET provider:
    http://uda.openlinksw.com/dotnet-informix-mt/

    Hope it helps,

    Thanks,
    Eileen


    • Edited by Eileen Zhao Wednesday, September 28, 2011 8:26 AM
    • Marked as answer by Eileen Zhao Thursday, October 6, 2011 6:04 AM
    Wednesday, September 28, 2011 8:26 AM
  • Eileen,

     

    that is not the answer, what you have provided is a workaround for the effect and not addressed the cause. We have three development servers and a live server and the informix ODBC connection works on ALL servers bar for our latest development server which is configured (or should be) the same but manifests the same issue as described by the OP.

    All columns are imported fine with the exception of 'char' fields - we are still investigating this but we believe it is a UNICODE issue. The interesting this about this is that the BIDS application can see the data fine in the 'viewer' but the SSIS engine that executes the package on the server is unable to see the char data.

    All the servers use IBM Informix SDK 3.5 that provides the relevant Informix ODBC driver. All applications that use this driver can display it fine (including BIDS as mentioned above), it is only the SSIS engine that seems to have this problem.

    Regards,

    Mark


    • Edited by Razor777 Friday, October 21, 2011 1:35 PM
    Friday, October 21, 2011 1:34 PM
  • Mark,

    Did you ever find a solution to this issue?  I am experiencing the exact same thing and am baffled by the fact that the BIDS viewer is able to see the data just fine, but the SSIS engine seems to lose all strings.  I too have an application that uses the same ODBC connection and the same version of .NET and has no issues pulling the data there.  I'm on the verge of abandoning SSIS altogether and writing a console application using the ODBC to transfer the Informix data to a SQL server.  Please tell me you've found another way!? 

    -Jon Parker

    Wednesday, November 30, 2011 9:23 PM
  • Is it a codepage issue?

    Try to convert using Derived Column Transformation.

    I suspect it could also be a version of the driver issue, can you check if in both cases the drivers are the same?


    Arthur My Blog
    Wednesday, November 30, 2011 9:32 PM
  • How would I determine if it's a codepage issue?

    By the time I can use a Derived Column Transformation, it's too late.  The strings are lost as soon as they come out of the ADO NET Source task.

    I have also verified that the driver is the same in both cases.

    Thanks for your insight ... any other ideas?

    Wednesday, November 30, 2011 10:19 PM
  • Ask the Informix DBA what coepage it is on.

    I looked back now into my records to see how I managed to connect to Informix 7 (it was successful from the 1 st try) and I can see that I did not use the ADO (did not feel natural to me), instead I opted for the OLEDB.

    I suggest you do too.

    An article that may help is http://dineshpathirana.blogspot.com/2011/08/connecting-informix-databases-through.html


    Arthur My Blog
    Wednesday, November 30, 2011 10:28 PM