SAP HANA ODBC Connections using SSDT/SSIS
<p>We are migrating our SAP BW instance from MSSQL to HANA DB and have the requirement to use SSIS out of the system.  Using SSDT(VS2010), I get a variety of errors when trying to read data from HANA.  I have the drivers installed and can connect and query the data in a small C# app I wrote (will post that code below as well).</p>
<p>Similar Stack question <a href="https://stackoverflow.com/questions/13943765/why-does-my-odbc-connection-fail-when-running-an-ssis-load-in-visual-studio-but">here</a>: My settings are:
<a href="https://i.stack.imgur.com/qLoIS.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qLoIS.jpg" alt="enter image description here" /></a></p>
<p>First error:
Using the .Net Provider for ODBC conncections
<a href="https://i.stack.imgur.com/NHw5D.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NHw5D.jpg" alt=".NET ODBC" /></a></p>
<p>I get the following error:
[SSIS.Pipeline] Error: ADO NET Source failed validation and returned error code 0xC0208449.
[SSIS.Pipeline] Error: ADO NET Source failed validation and returned error code 0xC0208449.</p>
<p>Failed Validation? Is this an authorization issue?  No
<a href="https://i.stack.imgur.com/o1hVT.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/o1hVT.jpg" alt="User PW" /></a></p>
<p>Second Error: Using straight ODBC connection
<a href="https://i.stack.imgur.com/ZqWmk.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ZqWmk.jpg" alt="ODBC" /></a></p>
<p>I get the following errors:</p>
<p>[ODBC Source <a href="https://i.stack.imgur.com/o1hVT.jpg" rel="nofollow noreferrer">4</a>] Error: The AcquireConnection method call to the connection manager HANA_ODBC failed with error code 0xC0014009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.</p>
<p>[SSIS.Pipeline] Error: ODBC Source failed validation and returned error code 0x80004005.</p>
<p>[Connection manager &quot;HANA_ODBC&quot;] Error: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server.</p>
<p>Same success on connection manager:
<a href="https://i.stack.imgur.com/t30H8.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/t30H8.jpg" alt="success 1" /></a></p>
<p>Third Error: Using .NET HANA Provider supplied when you install HANA Client
<a href="https://i.stack.imgur.com/4VFKg.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4VFKg.jpg" alt="enter image description here" /></a></p>
<p>I don't even see the option for the 32bit DSN.  Only the 64bit DSN shows and when user and pass is entered it throws an exception.</p>
<p>64bit DSN:
<a href="https://i.stack.imgur.com/fe3aV.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fe3aV.jpg" alt="enter image description here" /></a></p>
<p>32bit DSN:
<a href="https://i.stack.imgur.com/TK3ur.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TK3ur.jpg" alt="enter image description here" /></a></p>
<p>C# that can connect and query data from HANA:</p>
<pre><code>using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Odbc;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            using (OdbcConnection myConnection = new OdbcConnection())
            {
                string myConnectionString;                
                myConnectionString = &quot;DSN=HANA32;SERVERNODE=MyHana:30015;UID=SYSTEM;PWD=MyPW;DATABASENAME=DB&quot;;
                myConnection.ConnectionString = myConnectionString;
                
                try
                {
                    myConnection.Open();
                }
                catch (System.Data.Odbc.OdbcException ex)
                {
                    Console.Error.WriteLine(ex);               
                }
                if (myConnection.State == ConnectionState.Open)
                {
                    Console.Write(&quot;Connection Open&quot;);
                    Console.WriteLine();
                    OdbcCommand DbCommand = myConnection.CreateCommand();
                    DbCommand.CommandText = &quot;SELECT \&quot;BATCH\&quot; FROM \&quot;_SYS_BIC\&quot;.\&quot;ZBW/ZBATCH_ATTRS\&quot; LIMIT 10&quot;;
                    OdbcDataReader DbReader = DbCommand.ExecuteReader();
                    while (DbReader.Read())
                    {
                        Console.WriteLine(DbReader[&quot;BATCH&quot;].ToString());

                    }        
                    DbReader.Close();
                    DbCommand.Dispose();
                    myConnection.Close();
                    Console.ReadLine();                    
                }
                else
                {
                    Console.Write(&quot;Failure&quot;);
                    Console.ReadLine();                                       
                }
            }
        }
    }
}
</code></pre>
<p><a href="https://i.stack.imgur.com/PnuB2.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PnuB2.jpg" alt="Console" /></a></p>
<p>Stack question here: <a href="https://stackoverflow.com/questions/20617840/ssis-2012-and-sap-hana">Hana and SSIS</a> has an answer but the link in the answer is dead.</p>
<p>Any help on this issue would be greatly appreciated!!</p>