Having trouble calling function in SSIS ExecuteSQLQuery task

[dborochin]

Hello,
I'm having some trouble trying to setup a call to a function on Postgres via the SSIS Execute SQL Query task. Here is the SQL Call:

Code: Select all

select * from object_monitor.register_consumer( 'bidw_monitor', 'bidw_consumer' )

This works 100%, on PGAdminIII.

When I call this in SSIS, I get this error:

Code: Select all

[Execute SQL Task] Error: Executing the query "select * from object_monitor.register_consumer( 'b..." failed with the following error: "Failed to create an IDataAdapter object. This provider may not be fully supported with the Execute SQL Task. Error message 'syntax error at or near "select"'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

[Shalex]

Please post here the DDL script of your register_consumer function and related database objects. We will try to reproduce the problem.

[dborochin]

This is only a wrapper functions to a whole system residing on postgresql. I don't understand why you need to see the ddl of the function itself. I have no issue in calling the function in PGAdminIII.

Assume I have a function that returns a set of data. When I call this functions, in pgadmin, it returns data. When I call this function in SSIS, via an execute sql query component, it gives me an error.

Here is the ddl:

CREATE OR REPLACE FUNCTION object_monitor.register_consumer(p_monitor_name text, p_consumer_name text)
RETURNS SETOF integer AS
$BODY$
-- Subscribe to the monitor
INSERT INTO object_monitor.monitors_consumers
VALUES ( $1, $2 )
;

-- Subscribe to the queues
SELECT * FROM object_monitor.subscribe_consumer( $1, $2 );
$BODY$
LANGUAGE 'sql' VOLATILE SECURITY DEFINER;
ALTER FUNCTION object_monitor.register_consumer(text, text) OWNER TO su;

[Shalex]

We will investigate the issue and notify you about the results as soon as possible.

[Shalex]

Please try the latest 4.65.79 build of dotConnect for PostgreSQL. I cannot reproduce the mentioned problem using this build.