question Error while writing data to Hadoop via SSIS ODBC in Support Questions

Error while writing data to Hadoop via SSIS ODBC

tahir2 — Fri, 16 Sep 2022 11:40:45 GMT

I am trying to export data from SQL Server via SSIS ODBC to hadoop. while i can succesfully read the data via ODBC from Hadoop, trying to write to Hadoop via SSIS ODBC Destination (via Hortonworks ODBC Driver) from Microsoft SQL Server integration services gives an error:

failed with error code 0x80004005 while processing input "ODBC Destination Input" (31). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

Error: Open Database Connectivity (ODBC) error occurred. state: 'HY000'. Native Error Code: 80. [Hortonworks][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: Error while compiling statement: FAILED: ParseException line 1:61 cannot recognize input near '?' ')' '<EOF>' in value row constructor

There is NO hive or SQL code written directly. here is snapshot of the destination component in SSIS. For test purposes there is only one column that i am exporting and that is of type integer. (mapped in the Mappings tab)

it seems this component generates some query which hive doesnt like or has syntax problems. but i cannot see that query.

what i already have tried:

- tweaking the settings of the source (Validate External data True/false)

- Setting the "Native Query" in ODBC DSN Settings to checked.

but so far nothing helped.

Can anybody help me with this please?

Any pointers appriciated!

Thank you!

Re: Error while writing data to Hadoop via SSIS ODBC

AndresUrrego — Tue, 13 Jun 2017 09:43:38 GMT

Any update buddy?

Re: Error while writing data to Hadoop via SSIS ODBC

tahir2 — Tue, 13 Jun 2017 13:15:24 GMT

didnt work so far...

Re: Error while writing data to Hadoop via SSIS ODBC

AndresUrrego — Wed, 14 Jun 2017 20:15:59 GMT

have you configured already the odbc connection parammeter using the hortonworks driver and it worked, I' reaching you cause i'm trying that and i dont know how to do it. thanks

Re: Error while writing data to Hadoop via SSIS ODBC

tahir2 — Wed, 14 Jun 2017 20:18:02 GMT

yes, i have configured it and it works. I can import data from Hadoop to SQL server.

Re: Error while writing data to Hadoop via SSIS ODBC

tahir2 — Wed, 14 Jun 2017 20:24:55 GMT

For Dev i use 32 bit DSN and for Server 64 bit.

in DSN configeration in the host i have IP in the Host, Hadoops port (not the default).

Machanism with user name and password. here the user/password of Hadoop (Knox) synched with AD.

in HTTP settings, i ADAM/default/hive and ssl=true in the list.

in the SSL, enabled SSL, checked the path to log file.

In Advanced options, "use native Query" checked. Edit the batch size, string size etc.

Re: Error while writing data to Hadoop via SSIS ODBC

AndresUrrego — Wed, 14 Jun 2017 20:26:27 GMT

A couple of questions from my side are: Just to let you know my scenario, I'M playing with a single node configuration in a virtual machine with hortonworks services as Hive, Pig , etc.

* Do you use a specific user, created to get access to hive in your cluster using a view?

*Did you follow this tutorial? here

Re: Error while writing data to Hadoop via SSIS ODBC

tahir2 — Wed, 14 Jun 2017 20:45:56 GMT

I had the DSN configured from someone else so not sure if the tutorial was followed.

my user access through hive. I can log in via the terminal or hue as well.

Re: Error while writing data to Hadoop via SSIS ODBC

AndresUrrego — Wed, 14 Jun 2017 20:55:01 GMT

did you create the user that you use in hive or someone else did it for you

Re: Error while writing data to Hadoop via SSIS ODBC

tahir2 — Wed, 14 Jun 2017 20:59:03 GMT

someone did it for me.

Re: Error while writing data to Hadoop via SSIS ODBC

iclin — Fri, 10 Nov 2017 02:40:08 GMT

@Tahir Naeem, have you worked out the solution? Seems I run into the same issue.

@Tahir Naeem, Have you worked out this? Seems I run into the same issue.

Re: Error while writing data to Hadoop via SSIS ODBC

tahir2 — Wed, 15 Nov 2017 21:22:58 GMT

No unfortunately it didnt work and i have long given up on this. This ODBC driver isnt mature yet and it has many other problems a swell. e.g. while sourcing data via hive it doesnt allow even a slightly complex query (with an aggregation or group by).

I intend to rather pull data from Hadoop instead (with sqoop or something).