SAP HANA ODBC Connections using SSDT/SSIS

Question

SAP HANA ODBC Connections using SSDT/SSIS

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).

Similar Stack question here: My settings are:

First error: Using the .Net Provider for ODBC conncections

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.

Failed Validation? Is this an authorization issue? No

Second Error: Using straight ODBC connection

I get the following errors:

[ODBC Source 4] 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.

[SSIS.Pipeline] Error: ODBC Source failed validation and returned error code 0x80004005.

[Connection manager "HANA_ODBC"] Error: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server.

Same success on connection manager:

Third Error: Using .NET HANA Provider supplied when you install HANA Client

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.

64bit DSN:

32bit DSN:

C# that can connect and query data from HANA:

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 = "DSN=HANA32;SERVERNODE=MyHana:30015;UID=SYSTEM;PWD=MyPW;DATABASENAME=DB";
                myConnection.ConnectionString = myConnectionString;
                
                try
                {
                    myConnection.Open();
                }
                catch (System.Data.Odbc.OdbcException ex)
                {
                    Console.Error.WriteLine(ex);               
                }
                if (myConnection.State == ConnectionState.Open)
                {
                    Console.Write("Connection Open");
                    Console.WriteLine();
                    OdbcCommand DbCommand = myConnection.CreateCommand();
                    DbCommand.CommandText = "SELECT \"BATCH\" FROM \"_SYS_BIC\".\"ZBW/ZBATCH_ATTRS\" LIMIT 10";
                    OdbcDataReader DbReader = DbCommand.ExecuteReader();
                    while (DbReader.Read())
                    {
                        Console.WriteLine(DbReader["BATCH"].ToString());

                    }        
                    DbReader.Close();
                    DbCommand.Dispose();
                    myConnection.Close();
                    Console.ReadLine();                    
                }
                else
                {
                    Console.Write("Failure");
                    Console.ReadLine();                                       
                }
            }
        }
    }
}