Issuing Inserts/Updates to Progress OpenEdge via ODBC

odbcprogress-databasessisssis-2014

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 [2]: 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.

Best Answer

Once you've identified which one you need, probably 32 bit version, you'd need to ensure your project is using the appropriate run-time. Right click on your project and select Properties and then navigate to the Debugging tab under the Configuration Properties. After inverting the Run64BitRuntime value, I assume your package will work from within SSDT.

Related Question