I'm attempting to build some ETL to integrate our SQL Server environment with a remotely hosted 3rd party app running Progress OpenEdge. I have a linked server to it set up in SQL Server 2014.
Selects are working well enough, though I find that using
openquery is much, much faster than the simpler
select * from SERVER.DB.SCHEMA.TABLE syntax.
I'm struggling with writes, however. I've found several references for valid update syntax for linked servers, but these all give permissions errors:
select * from openquery(SERVER,'update FOO.BAR set "COL" = 0 where "COL" = 1') update fb set [COL] = 0 from SERVER.DB.FOO.BAR fb where [COL] = 1 update openquery(SERVER,'select * from FOO.BAR where "COL" = 1') set [COL] = 0
This syntax is the only one I've found that works:
declare @script nvarchar(max) = N'update FOO.BAR set "COL" = 0 where "COL" = 1' execute (@script) at SERVER
This works for both inserts and updates, but I can't see a way to load a set of data this way, because I can't reference my local tables.
In SSIS, an ODBC connection passes the connectivity test, but fails during initialization:
Error: 0xC0014020 at Package1, Connection manager "SERVER": An ODBC error -1 has occurred.
Error: 0xC0014009 at Package1, Connection manager "SERVER": There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server.
Error: 0x20F at Data Flow Task, ODBC Destination : The AcquireConnection method call to the connection manager SERVER failed with error code 0xC0014009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: ODBC Destination failed validation and returned error code 0x80004005.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
Ultimately, I'm searching for a better way to merge/upsert a set of data (anything from a few dozen rows to a couple hundred thousand) without resorting to using RPC to insert/update one row at a time.