SSIS error when doing function call to direct OLEDB provider

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

SSIS error when doing function call to direct OLEDB provider

Postby hcgroenewald@gmail.com » Thu Nov 14, 2013 8:20 am

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 :
Code: Select all
SELECT * FROM admin.create_table();


Greenplum function :

Code: Select all
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
hcgroenewald@gmail.com
 
Posts: 3
Joined: Fri Aug 30, 2013 11:20 am

Re: SSIS error when doing function call to direct OLEDB prov

Postby Moderator » Mon Nov 18, 2013 1:07 pm

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
User avatar
Moderator
Site Admin
 
Posts: 310
Joined: Wed Oct 29, 2008 11:27 pm

Re: SSIS error when doing function call to direct OLEDB prov

Postby hcgroenewald@gmail.com » Wed Nov 20, 2013 2:08 am

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
hcgroenewald@gmail.com
 
Posts: 3
Joined: Fri Aug 30, 2013 11:20 am

Re: SSIS error when doing function call to direct OLEDB prov

Postby hcgroenewald@gmail.com » Sun Dec 08, 2013 12:29 pm

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

Code: Select all
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 :
Code: Select all
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
hcgroenewald@gmail.com
 
Posts: 3
Joined: Fri Aug 30, 2013 11:20 am

Re: SSIS error when doing function call to direct OLEDB prov

Postby Moderator » Wed Dec 11, 2013 10:54 pm

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!
User avatar
Moderator
Site Admin
 
Posts: 310
Joined: Wed Oct 29, 2008 11:27 pm


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: Google [Bot] and 2 guests

cron