Having trouble calling function in SSIS ExecuteSQLQuery task

Having trouble calling function in SSIS ExecuteSQLQuery task

Postby dborochin » Tue 10 Nov 2009 00:19

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.
dborochin
 
Posts: 2
Joined: Mon 09 Nov 2009 19:21

Postby Shalex » Wed 11 Nov 2009 15:33

Please post here the DDL script of your register_consumer function and related database objects. We will try to reproduce the problem.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

I can post that, but my questions is pretty general.

Postby dborochin » Wed 11 Nov 2009 16:46

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;
dborochin
 
Posts: 2
Joined: Mon 09 Nov 2009 19:21

Postby Shalex » Mon 16 Nov 2009 17:08

We will investigate the issue and notify you about the results as soon as possible.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Tue 19 Jan 2010 11:35

Please try the latest 4.65.79 build of dotConnect for PostgreSQL. I cannot reproduce the mentioned problem using this build.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL