SSIS error when doing function call to direct OLEDB provider

More
6 years 6 months ago #10329 by hcgroenewald@gmail.com
hcgroenewald@gmail.com created the topic: SSIS error when doing function call to direct OLEDB provider
Hi,

I get the following error from SSIS ( 2008 ) when I try and call a Greenplum function ( see function declaration below ).
This happens when trying to do the following call in a EXECUTE SQL TASK in ssis :
SELECT * FROM admin.create_table();

Greenplum function :
CREATE OR REPLACE FUNCTION "admin".create_table()
  RETURNS void AS
$BODY$

DECLARE 
	timeSuffix CHAR(14);

BEGIN
		EXECUTE 'SELECT to_char(now(),''YYYYMMDDhhmmss'')' INTO timeSuffix;

		EXECUTE 'CREATE TABLE admin.test_table_' || timeSuffix || ' (test_field INT) ;';

END

$BODY$
  LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION "admin".create_table() OWNER TO gpadmin;

SSIS Error :

SSIS package "Package.dtsx" starting.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "SELECT * FROM admin.create_table();
" failed with the following error: "Cannot determine output columns for procedure admin.create_table". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.

I have been able to insert and delete records from Greenplum through the SSIS driver, but am unable to call a normal TRUNCATE function as well.

Any feedback that can be provided would be greatly appreciated.

I am using the following driver : http://www.pgoledb.com/index.php?option ... egory_id=1

Kind regards
Hendrik Groenewald

Please Log in or Create an account to join the conversation.

More
6 years 6 months ago #10335 by Moderator
Moderator replied the topic: Re: SSIS error when doing function call to direct OLEDB prov
Hi,
please do not add "SELECT * FROM" before the procedure call. Call it as "admin.create_table()".

Otherwise SSIS is trying to build columns information from "void" function and cannot do it.

HTH

Please Log in or Create an account to join the conversation.

More
6 years 6 months ago #10433 by hcgroenewald@gmail.com
hcgroenewald@gmail.com replied the topic: Re: SSIS error when doing function call to direct OLEDB prov
Thank you for the reply, works like a charm :) Appreciate your help.

Moderator wrote: Hi,
please do not add "SELECT * FROM" before the procedure call. Call it as "admin.create_table()".

Otherwise SSIS is trying to build columns information from "void" function and cannot do it.

HTH

Please Log in or Create an account to join the conversation.

More
6 years 5 months ago #10660 by hcgroenewald@gmail.com
hcgroenewald@gmail.com replied the topic: Re: SSIS error when doing function call to direct OLEDB prov
Hi all,

Thank you for the prompt response last time in helping me.

I have another scenario that I would like to ask about.
Have looked at various options with the Postgresql methods and coming up short it seems.

We have functions written in Greenplum that we are trying to get multiple values returned from the SSIS SQL Execute Task, and then would like SSIS to map those as resultset information into SSIS variables.

Greenplum code an example that we are trying to get to work ( as a trial to find out how we can do the same for our own functions ) :
Taken from : http://stackoverflow.com/questions/4547672/return-multiple-fields-as-a-record-in-postgresql-with-pl-pgsql
CREATE FUNCTION test_ret(a TEXT, b TEXT) RETURNS RECORD AS $$
DECLARE 
  ret RECORD;
BEGIN
  -- Note the CASTING being done for the 2nd and 3rd elements of the RECORD
  IF LENGTH(a) < LENGTH(b) THEN
      ret := (TRUE, (a || b)::TEXT, 'a shorter than b'::TEXT);
  ELSE
      ret := (FALSE, (b || a)::TEXT, NULL::TEXT);
   END IF;
RETURN ret;
END;$$ LANGUAGE plpgsql;

Call being made in SSIS SQL Execute Task :
SELECT a, b, c  FROM test_ret('foobar','bar') AS (a BOOL, b TEXT, c TEXT);

SSIS variables are set to boolean for the first result ( a from the SELECT statement ), string for the second ( b ) and string for the third (c).

We get the current error when running this simple example :

SSIS package "Package.dtsx" starting.
Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "RUNVAR_Test1": "Exception from HRESULT: 0x80040E21".
Task failed: Execute SQL Task
SSIS package "Package.dtsx" finished: Success.


The setting for result set on the SQL Execute Task is set as Single Row Result set. And the mappings has been set up as RUNVAR_Test1 = a ( boolean for both ), RUNVAR_Test2 = b ( string on SSIS side, TEXT on Postgresql function side ), RUNVAR_Test3 = c ( string on SSIS side, TEXT on Postgresql function side ).

Any help that you can provide would be greatly appreciate.

Apologies if this is a stupid question.

Kind regards,
Hendrik

Please Log in or Create an account to join the conversation.

More
6 years 5 months ago #10700 by Moderator
Moderator replied the topic: Re: SSIS error when doing function call to direct OLEDB prov
Hi, I tested the query via linked server: SELECT * FROM OPENQUERY(GREENPLUM, 'SELECT a, b, c FROM test_ret(''foobar'',''bar'') AS (a BOOL, b TEXT, c TEXT)');
and it returned correct result: False, barfoobar, NULL.

Could you send a test package to support at pgoledb?
Thank you!

Please Log in or Create an account to join the conversation.

Time to create page: 0.108 seconds
Powered by Kunena Forum