We have been trying to ETL data from an Intersystems Cache database to an SQL Server 2019 database, using SSIS in VS 2019.
We have installed the drivers from Intersystems on the server, using their version 2018年01月08日.766. Then we added a System DSN pointing to the Intersystems server, using the insalled Intersystems driver.
In SSIS, we added a connection manager using a normal ODBC-connection manager. Then we added a ODBC source to the Data flow pane, chose the ODBC connection manager we had created in the previous step. After adding a SQL command we tried to chose the Columns tab, but then we got the following error: Exception from HRESULT:0xC0202003 Error at Data Flow Task [ODBC Source[2]]: Unable to access the acuired connections.
If instead chosing Table Name in the Data access mode, we are able to get a list of tables in the source database, but we still are not able to get the columns.
So what do we have to do to get the data from the Intersystems source database into SSIS?
1 Answer 1
So the solution to this was the following:
In stead of adding an ODBC connection manager in SSIS, you should add a new ADO.NET Connection manager: enter image description here
The connection manager should have the provider .Net Providers\Odbc Data Provider
Chose the System DSN that you have created with Intersystems Cache as the driver. (You will find in the dropdown under "Use user or system data source name:"
In the Data Flow pane of the SSIS package, add a new ADO NET Source to the canvas. In the properties for the ADO NET Source, chose the ADO.NET connection manager you have created and then chose the "SQL Command" as Data access mode. You should now be able to see the columns in the Columns tab. (In the picture below, the table-name is obviousley changed :) )
(We still could not use the "Table name" data access mode. That mode still throws an exception).
