SAP MaxDB ODBC Driver SSIS Connection Error

[Former Member]

I installed a SAP MaxDB ODBC driver (maxdb-all-win-64bit-x86_64-7_8_02_35) and I created a user DSN called MaxDB_Source. But every time I test the source in the SSIS connection manager, this error in the attachment always pops out:

Test connection failed because of an error in initializing provider.

ERROR [HY000] [SAP AG] [SDBODBC DLL] [Max DB] General error; -400

ERROR [HY000] [SAP AG] [SDBODBC DLL] [Max DB] General error; -400

The strange thing is, when I choose the connection from the 'Use user or system data source name' drop down list in the connection manager, test connection would always fail and the "Use connection string" would look something like this:

< Dsn=MaxDB_Source;uid=xxxxx >

However if I choose the "Use connection string" and build it, test connection succeeds and "Use connection string" looks like this:

<Dsn=MaxDB_Source;servernode=xxxx.xxxx.net;serverdb=xxx;uid=xxxxx>

But it only works when I build it. It won't work even if I copy this exact connection string and paste it in the "Use connection string" blank.

I've also tried the configuration file, import and export wizard, and connection string like this:

Dsn=MaxDB_Source;Driver={SAP MaxDB MaxDB (Unicode)};servernode=xxxxxxx.xxxxxx.net;serverdb=xxx;uid=xxxx;pwd=********

and still none of these worked.

I can't build the connection string every time I use this connection, so I wonder if there's any way to solve this so I could just select the source from the drop down list just like any other connection? I've been struggling for over a week and I'm really frustrated right now. I really appreciate any help.

Thank you.

[former_member184473]

Hello Kevin,

Are you able to connect to the database as superdba?

1) dbmcli -d <DB> -u superdba,<pwd>

and

2) dbmcli -d <DB> -u control,<pwd>

    dbmcli> sql_connect superdba,<pwd>

You can also create the ODBC trace:

Troubleshooting Using the SAP MaxDB ODBC Trace - SAP Documentation 

Activate: 

odbc_cons trace API ON

odbc_cons trace SHORT ON

odbc_cons trace sql on

odbc_cons show all

To deactivate the traces:

odbc_cons trace API OFF

odbc_cons trace SHORT OFF

odbc_cons trace sql OFF

Regards,

Eduardo Rezende

[Former Member]

thank you for the response.

Our issue is not with ODBC.  We tried and configured ODBC to point to our MaxDB.  ODBC works fine.

I want to use ADO.NET connection manager inside SSIS to leverage ODBC connection.  Specifically we select ".Net Provider\Odbc Data Provider" to configure ADO.NET connection to be able to use ODBC.

Now this ADO.NET connection does not work.  Looks like that ADO.NET connection fails to properly connect to ODBC connection that was already configured to point MaxDB source.

In addition we use 64-bit Windows 2008 server and Visual Studio 2008 for this development.

Hope that this clarifies some confusion.

Your assistance on this is much appreciated.