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.
