title: SAP HANA ODBC Connections using SSDT/SSIS
body: <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>