none
ODBC conenction to Netezza

    Question

  • I'm working on an SSIS Package which has an Execte SQL Task that does a simple query from a Netezza database:

    select max(dimensionKey) from dimTable

    I use an ODBC connection to connect to the Netezza database.  If I configured the SQL Task to produce one row of record, then use a integer type variable to capture the result.  I got the following error:

    [Execute SQL Task] Error: An error occurred while assigning a value to variable "MaxDestinationKey": "Result column index 0 is not valid.".



    If I configured the SQL Task to produce a full result set, and use an object type variable to capture the result, I got the following error:

    [Execute SQL Task] Error: Executing the query "select max(ExternalKey) from admin.dim_external" failed with the following error: "Disconnected recordsets are not available from ODBC connections.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.



    Similar SQL Task that insert and update Netezza tables worked fine.  Just when i need to extract the result set, I got this problem.

    By the way, I also was able to get it to work using a script task, and odbc connection, ODBCCommand.ExecuteScalar(), etc. When the dimension table gets very big, the script task would time out.  However, when I got timeout tried to insert and delete records in Netezza using the Script Task component, I was able to get it to work by using the Execute SQL Task without encountering timeout.  Not sure why they behave differently, as both odbc conneciton strings are the same.  Thus, I want to try using the Execute SQL Task in the query situation to bypass timeout issue, but without luck extracting the result.  Also configuring Netezza timeout so far seems uneffective and Netezza hasn't resolved my support ticket.  Long story short, if you want to suggest I use Script Task it will not help me.
    Thursday, April 10, 2008 11:52 PM

Answers

  • Are you using a native ODBC connection manager (ODBC connection manager), or a managed ODBC connection manager (ADO.NET connection manager with an Odbc provider)?

     

    If you're using an native ODBC connection manager, try a managed ADO.NET Odbc provider instead, which is a ADO.NET wrapper over ODBC.  You may have better luck with that connection manager and type system in terms of mapping the result set onto a variable.

     

     

     

     

    Friday, April 11, 2008 9:30 AM

All replies

  • Are you using a native ODBC connection manager (ODBC connection manager), or a managed ODBC connection manager (ADO.NET connection manager with an Odbc provider)?

     

    If you're using an native ODBC connection manager, try a managed ADO.NET Odbc provider instead, which is a ADO.NET wrapper over ODBC.  You may have better luck with that connection manager and type system in terms of mapping the result set onto a variable.

     

     

     

     

    Friday, April 11, 2008 9:30 AM
  • It worked beautifully.  Thank you very much!
    Friday, April 11, 2008 9:46 PM
  • Would you mind explaining how to setup a connection to netezza from a microsoft sql 2008 server via linked server option and or via ssis? What should be configured where?
    Tuesday, November 11, 2008 8:48 PM
  • for more information please visit http://www.netezzaforum.com
    Wednesday, January 13, 2010 5:02 AM
  • The new Netezza Forum - http://netezzaforum.com
    • Edited by superuser123 Thursday, January 14, 2010 7:07 AM http://netezzaforum.com
    Thursday, January 14, 2010 7:05 AM