sql-servergoogle-bigqueryssisodbcsimba

SSIS ODBC Google BigQuery (Simba) works in local but return ODBC-1 error when I launch pakcage from SQL Agent


I have configured an ODBC connection on my post to connect with Google BigQuery. I couldn't do it natively so I download Simba Drive that helps me to connect with Google BigQuery. I have created a ODBC connection in 32bit to my BigQuery Project without problem, the connection works with the button "test". Everything alright for now. Then I created a package SSIS that use this connection and it works to.

Things start to get complicated when I try this to another server. I first created the odbc connection and it worked. But after that I put the package on the server then I ran the package with an SQL Agent job and it return me this error : Connection Managager "connection_name" An ODBC -1 error has occured. With no more explanation.

Do you have any idea of what kind of error is it ? An most importantly do you have any idea of what I could do to have more explanation on this error, I though about create a C# script that use this connection then put a try catch to get more verbose, but I don't know how to do it.


Solution

  • The issue is resolved, as @bosskay972 mentioned. Initially, confirm that your SSIS project is 32-bit and that the versions of your SQL Server and SSIS are the same. Proceed to the job, select Step, then Step Properties, then Data Source, and check all the boxes to ensure that you have your odbc connection. Once you have done so, you will have additional information about your issue. Verify that you have filled in the TableName and SqlCommand columns in the properties of your component that connects to ODBC.

    Posting the answer as community wiki for the benefit of the community that might encounter this use case in the future.

    Feel free to edit this answer for additional information.