SSIS - Batch load Hive table through ODBC

I am attempting to port an SSIS package to target HIVE instead of DB2. I am having an issue with the ODBC Destination task using the Hortonworks HIVE ODBC. I previously tried the Microsoft ODBC but that failed to create a valid insert query.

I read the documentation "Leveraging a Hadoop cluster from SQL Server Integration Services" which mentioned that writing to Hive from ODBC is not possible but this documentation is from 2013.

The problem: I have a data flow task with an OLE DB Source(DB2) and a ODBC Destination(Hive). If I set the ODBC Destination to 'Row-by-row' then the load completes as expected. However, if I set it to 'Batch' then I only receive the following error:

Error: 0x384 at Push Tokens to HIVE, ODBC Destination [2]: Open Database Connectivity (ODBC) error occurred. SQLExecute returned error while inserting row 1

I have attached the ODBC trace from the problematic task. I expected to see a SQLExecute SQL_ERROR in the trace but that seems to be missing.

Is this even possible through ODBC or do I need to use HDFS Destination followed by a Hive Task?

DtsDebugHost    1e9c-2488	ENTER SQLPrepareW 
		HSTMT               0x08D45108
		WCHAR *             0x0583CEE4 [      86] "INSERT INTO `default`.`temp_000000000000_return` ( `link_id`,`tok_data`) VALUES (?, ?)"
		SDWORD                    86

DtsDebugHost    1e9c-2488	EXIT  SQLPrepareW  with return code 0 (SQL_SUCCESS)
		HSTMT               0x08D45108
		WCHAR *             0x0583CEE4 [      86] "INSERT INTO `default`.`temp_000000000000_return` ( `link_id`,`tok_data`) VALUES (?, ?)"
		SDWORD                    86

DtsDebugHost    1e9c-2488	ENTER SQLSetStmtAttrW 
		SQLHSTMT            0x08D45108
		SQLINTEGER                  21 <SQL_ATTR_PARAMS_PROCESSED_PTR>
		SQLPOINTER          0x057D8578
		SQLINTEGER                   0 

DtsDebugHost    1e9c-2488	EXIT  SQLSetStmtAttrW  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x08D45108
		SQLINTEGER                  21 <SQL_ATTR_PARAMS_PROCESSED_PTR>
		SQLPOINTER          0x057D8578
		SQLINTEGER                   0 

DtsDebugHost    1e9c-2488	ENTER SQLSetStmtAttrW 
		SQLHSTMT            0x08D45108
		SQLINTEGER                  20 <SQL_ATTR_PARAM_STATUS_PTR>
		SQLPOINTER          0x084B4158
		SQLINTEGER                   0 

DtsDebugHost    1e9c-2488	EXIT  SQLSetStmtAttrW  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            0x08D45108
		SQLINTEGER                  20 <SQL_ATTR_PARAM_STATUS_PTR>
		SQLPOINTER          0x084B4158
		SQLINTEGER                   0 

DtsDebugHost    1e9c-2488	ENTER SQLBindParameter 
		HSTMT               0x08D45108
		UWORD                        1 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                      -16 <SQL_C_SLONG>
		SWORD                        4 <SQL_INTEGER>
		SQLULEN                   10
		SWORD                        0 
		PTR                0x08316178
		SQLLEN                     4
		SQLLEN *            0x08315F98

DtsDebugHost    1e9c-2488	EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
		HSTMT               0x08D45108
		UWORD                        1 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                      -16 <SQL_C_SLONG>
		SWORD                        4 <SQL_INTEGER>
		SQLULEN                   10
		SWORD                        0 
		PTR                0x08316178
		SQLLEN                     4
		SQLLEN *            0x08315F98 (137452312)

DtsDebugHost    1e9c-2488	ENTER SQLBindParameter 
		HSTMT               0x08D45108
		UWORD                        2 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                       -8 <SQL_C_WCHAR>
		SWORD                       -8 <SQL_WCHAR>
		SQLULEN                   35
		SWORD                        0 
		PTR                0x057F85C8
		SQLLEN                    72
		SQLLEN *            0x08315FB8

DtsDebugHost    1e9c-2488	EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
		HSTMT               0x08D45108
		UWORD                        2 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                       -8 <SQL_C_WCHAR>
		SWORD                       -8 <SQL_WCHAR>
		SQLULEN                   35
		SWORD                        0 
		PTR                0x057F85C8
		SQLLEN                    72
		SQLLEN *            0x08315FB8 (137452312)

DtsDebugHost    1e9c-2c5c	ENTER SQLExecute 
		HSTMT               0x08D45108

DtsDebugHost    1e9c-2c5c	EXIT  SQLExecute  with return code 0 (SQL_SUCCESS)
		HSTMT               0x08D45108

DtsDebugHost    1e9c-2c5c	ENTER SQLGetDiagRecW 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x08D45108
		SQLSMALLINT                  1 
		SQLWCHAR *          0x0927EFA0
		SQLINTEGER *        0x0927D784
		SQLWCHAR *          0x0927DF98 
		SQLSMALLINT               1024 
		SQLSMALLINT *       0x0927D788

DtsDebugHost    1e9c-2c5c	EXIT  SQLGetDiagRecW  with return code 100 (SQL_NO_DATA_FOUND)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x08D45108
		SQLSMALLINT                  1 
		SQLWCHAR *          0x0927EFA0
		SQLINTEGER *        0x0927D784
		SQLWCHAR *          0x0927DF98 
		SQLSMALLINT               1024 
		SQLSMALLINT *       0x0927D788

DtsDebugHost    1e9c-2488	ENTER SQLFreeHandle 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x08D45108

DtsDebugHost    1e9c-2488	EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           0x08D45108

DtsDebugHost    1e9c-2488	ENTER SQLDisconnect 
		HDBC                0x0254EBD8

DtsDebugHost    1e9c-2488	EXIT  SQLDisconnect  with return code 0 (SQL_SUCCESS)
		HDBC                0x0254EBD8

DtsDebugHost    1e9c-2488	ENTER SQLFreeHandle 
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           0x0254EBD8

DtsDebugHost    1e9c-2488	EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           0x0254EBD8

DtsDebugHost    1e9c-2488	ENTER SQLFreeHandle 
		SQLSMALLINT                  1 <SQL_HANDLE_ENV>
		SQLHANDLE           0x0254E748

DtsDebugHost    1e9c-2488	EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  1 <SQL_HANDLE_ENV>
		SQLHANDLE           0x0254E748

Relevant tags