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.
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.
- 1 ODBC drivers and data sources are configured seperately for 32 bit and 64 bit processes and are not visible between one another. When you created the SSIS job in SQL Agent did you remember to go to the General > Configuration > Advanced tab and tick/untick the "32 bit runtime" checkbox appropriately? – AlwaysLearning Commented Mar 25 at 12:40
- @AlwaysLearning good guess, it wasn't on 32 bit runtime, but after checking the checkbox it's still not working with the same error message – bosskay972 Commented Mar 25 at 12:47
- I solve the problem, but there are many things to do: First, make sure that your SSIS project runs in 32 bits, and make sure that your SSIS is on the same SQL Server version as your SQL Server. Then make sure that when you go to the job, then go to step, then step properties, then data source, then check all the checkboxes to make sure to take your odbc connection, after all of that, you will now have more indication about your problem. Now make sure that in the properties of your component that connects to ODBC, you've completed the fields TableName and SqlCommand. After this, everything good – bosskay972 Commented Mar 25 at 15:04
1 Answer
Reset to default 1The 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.