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();
}
}
}
}
}
Stack question here: Hana and SSIS has an answer but the link in the answer is dead.
Any help on this issue would be greatly appreciated!!
