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 :
Greenplum function :

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 : www.pgoledb.com/index.php?option ... egory_id=1

Kind regards
Hendrik Groenewald

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

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

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 : [url] stackoverflow.com/questions/4547672/retu...gresql-with-pl-pgsql [/url]

Call being made in SSIS SQL Execute Task :
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

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!