Query SAP HANA from BizTalk

ODBC

The easiest way to connect to a HANA table or view, is to use ODBC. BizTalk has an ODBC adapter, but to be honest, try to avoid using that to connect to HANA. It's not easy to use and you can't find alot of documentation about it.
The best option is to setup a linked server in SQL Server. In the back, it also uses ODBC, but you will be able to query the data with the WCF-SQL adapter which is alot easier.

Setup a linked server

To be able to connect to HANA, you will need to install the SAP HANA Client. Watch out, there is a 32bit and a 64bit version. Make sure that you get the correct one. You can download this from the SAP support site, but you need an account. So find somebody who has an account and ask them to download the latest version and install it on your SQL server.

Second step is to setup a new System DSN in the ODBC Data Source Administrator.

In the next screen, enter a relevant name and description, and provide the server and port of your HANA server.

When you press "Connect" you can enter the credentials to test the connection.

Next step, execute the following script in SSMS.

EXEC master.dbo.sp_addlinkedserver @server = N'HANA', 
         @srvproduct=N'HANA',
         @provider=N'MSDASQL', 
         @datasrc=N'HANA_64'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HANA',
          @useself=N'False',
          @locallogin=NULL,
          @rmtuser=N'USER',
          @rmtpassword='PWD'

After this, you have a new Linked Server called HANA.

You can now query HANA directly from SSMS.

Building Queries

During some tests, I found that it is best to use the OPENQUERY() function in SQL to send your queries directly to HANA. The SQL optimizer doesn't work that well with HANA so it will take forever to query simple data.

SELECT * FROM OPENQUERY(HANA,'SELECT
       "Field1","Field2"
FROM "_SYS_BIC"."HANAView"')

You can ask the HANA developer to give you the full query, because you can't really browse the data yourself. (At least, I didn't find a way yet)

It becomes a bit tricky when you want to use variables in your query. But you can find some help on the Microsoft support site.
https://support.microsoft.com/en-us/help/314520/how-to-pass-a-variable-to-a-linked-server-query
It looks a bit strange, but it does the trick.

Conclusion

I have tried with the ODBC adapter first and it works for simple queries without parameters. But once it gets more complicated, it has it's limitations.
The Linked Server way is easier from a BizTalk perspective. According to our SAP people, they also offer an ODATA API that you can use to query, but I haven't had the chance to test that.
If you have found other (and maybe better) ways to connect to HANA, please share in the comment section.