2

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.

3
  • FWIW, the openquery syntax generally gives better performance with filtered queries because it pushes the filter to the source system. The 4 part query can result in all the data being pulled back to SQL Server and then the filter is applied. Syntax for openquery may be a little wonky but I'll take that over the performance drag. Never mucked about with pushing updates though
    – billinkc
    May 6, 2015 at 16:59
  • There may be error messages posted before this with more information on why the AcquireConnection method call failed <- were there additional messages? SELECT * FROM ssisdb.catalog.operation_messages AS OM WHERE OM.message_type = 120 AND OM.operation_id = @MyLastRun;
    – billinkc
    May 6, 2015 at 17:02
  • I've been trying in both SSMS and SSIS. Edited to include full set of errors from SSIS (I'm not using the catalog yet--we skipped 2012, and not yet up to speed on 2014).
    – SQLFox
    May 6, 2015 at 18:10

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.