3

I am trying to ETL data out of my ServiceNow instance and into a SQL Server DB. I am extracting data via the ServiceNow ODBC driver, and I'm using SSIS as my ETL tool. I'm running into a problem with my ODBC source in my data flow task.

In SSIS, I have created an ODBC connection to my ODBC driver DSN, and the connection tested successfully. I then added an ODBC Source to my data flow task. The source is using the given ODBC connection. When I choose Data access mode = Table Name, no table names appear. So, I then change Data access mode to SQL command, and I enter a certain SQL query (on the change_request table). When I click on the Parse Query button, SSIS / Visual Studio throws the following error:

state: '42S02'. Native Error Code: 10129. [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]Base table:change_request not found.[10129]

What is causing this to fail in Visual Studio?

For reference, I have the 64-bit version of the ODBC driver installed as a System DSN. Also, the query successfully executes in the iSQL utility that comes with the ODBC driver install. (So, the query is "right".) And, I am also able to successfully connect to tables via the ODBC driver in other tools (e.g., Qlik Sense, Power BI). So, the problem is specific to Visual Studio. Perhaps I'm doing something wrong in Visual Studio.

I also tried an ADO.NET connection (with the ODBC data provider) in an ADO NET Source, but I experience the same problem.

Hadi
  • 36,233
  • 13
  • 65
  • 124
skyline01
  • 1,919
  • 8
  • 34
  • 55

1 Answers1

0

I think this is a 32-bit/64-bit issue since Visual Studio only runs in 32-bit mode. So you need to install the 32-bit ODBC driver and test again.

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Upon further research, I see that I have both the 32-bit and the 64-bit driver installed. Unfortunately, they both have the same name. I'm trying to determine which is which at the moment. – skyline01 May 02 '19 at 21:43
  • @skyline01 check the following link https://serverfault.com/questions/405742/ssis-dsn-not-showing-as-odbc-data-source – Hadi May 02 '19 at 21:46
  • I decided to sidestep the issue by creating a new User DSN in the 32-bit odbcad32.exe file (not in the x86 folder). I only see 1 service name when I configure this DSN. So, I don't know if it's the 32-bit service or the 64-bit service. I created a new SSIS package with the new user DSN, but I still have the same problem. So, to rule out that the service isn't the 64-bit service, I'm going to uninstall both drivers and re-install only the 32-bit one. I need admin rights for that, though, which is going to take a few days to get. – skyline01 May 02 '19 at 22:09
  • I just received admin rights to my machine. So, I uninstalled both drivers and re-installed the 32-bit driver. I can now see tables in my ODBC source. So, the 64-bit driver was the cause of the problem. – skyline01 May 14 '19 at 20:13