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: 294
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: 294
Joined: Wed Oct 29, 2008 11:27 pm

Ray Ban Sunglasses On Sale

Postby dwfcllmudn9R » Fri Aug 01, 2014 2:05 am

although you are not a skilled personyou can convert your ordinary looking sunglasses into spy sunglasses michael kors purses sale mk sunglasses, ,
Check carefully for any variations in the logo (misspellings, different font or letter sizes). Also, every model has a unique model number which you should note down to compare with the pair of your choice. cheap michael kors handbags sale ray ban glasses, ,
In the 1980's he moved on to goggles and shades, and is now a planet leader in innovative design, producing new and exciting products. Ray-Ban was founded in 1937 as a company of class sunglasses. Ray-Ban launched the 2 the majority of imitated shades in the planet, the "Aviator" (issued free to the United states air force pilots) and the "Wayfarer". oakley frogskins sunglasses mk sunglasses, ,
One of the key features to look for in a quality pair of sunglasses is lenses that offer UV protection. While price isn't always an indicator of the level of protection or quality offered by a pair of sunglasses, there are many cheap pairs on the market today that offer little to no protection against UV rays.
.
dwfcllmudn9R
 
Posts: 47
Joined: Fri Aug 01, 2014 2:00 am

Oakley Frogskins Polarized Matte Black

Postby dwfcllmudn9R » Fri Aug 01, 2014 2:07 am

these sunglasses are a choice of women of all ageanne klein sunglasses redefine glamour and at the same time protect the eyes from the suna pair of these designer sunglasses is a oakley sunglasses sale mk sunglasses, ,
Sunglasses are essential accessory items for everyday use but also for specialized sports and activities. Sunglasses protect the eyes from harmful UV rays, which occur even when it is cloudy; which is why wearing sunglasses at all times outside and while driving is an important concept to remember. fake oakley sunglasses ray ban sunglasses, ,
For starters are you looking for something fashionable or something practical as well? After all, sunglasses are designed to protect your eyes from the sun. If you get sunglasses that only have very small lenses, you will still get problems with the sun coming through and catching your eyes when you least expect it. This can be annoying at the best of times but it can also be problematic if you are driving. It can literally temporarily blind you, so consider why and when you will be wearing your new sunglasses so you can choose the most appropriate pair. oakley sunglasses outlet gucci sunglasses, ,
Reading sunglasses are just hot in this moment. People from all walks of life enjoy the feeling of wearing cool reading sunglasses when they want to read. It is a joy of life that everybody needs to have a taste of it. And the most importantly, firmoo can do the best for you because firmoo cann not only provide you cheap sunglasses but also well-qualified ones..
.
dwfcllmudn9R
 
Posts: 47
Joined: Fri Aug 01, 2014 2:00 am

Fake Oakley Gascan Matte Black

Postby dwfcllmudn9R » Fri Aug 01, 2014 2:09 am

prada tinted glasses can come across other weeks your time exact addressconfidence ordinarily are aware of that element lower self oakley dispatch sunglasses ray ban glasses, ,
Now with the aid of photochromic sunglasses, you are emancipated from all these nuisances. What a relief! But every coin has two sides, and photochromic sunglasses are no exception. oakley sunglasses sale ray ban glasses, ,
If you want to keep in tune with the changing fashion trends, you can try designer inspired sunglasses. Do not mix them up with the fake sunglasses that are available for dirt-cheap prices. There is a huge difference between fake sunglasses that copy designer wear and designer inspired sunglasses. True, the fake ones are replicas of the original designs, but they are made of extremely low quality. Because of their low standard material and built they don't last long either. In any case, you should never compromise on your eyes and wear fake sunglasses that always use bad quality lens. The thing about fake sunglasses is that even though they copy the logo of the expensive brand, it is extremely easy to differentiate a fake from an cheap michael kors handbags sale ray ban sunglasses, ,
At first glace you may notice no difference but when you wear them, there is a world of difference between them which also includes the quality of glasses. The designer sunglasses have very good quality of glasses that make it easy to look through.. The retail business of sunglasses has also become very competitive as more people have become aware of the advantages this business gives. The retail prices of sunglasses give a very high profit margin to the shop owners which make this an attractive business.
.
dwfcllmudn9R
 
Posts: 47
Joined: Fri Aug 01, 2014 2:00 am

Fake Oakley Sunglasses For Sale

Postby dwfcllmudn9R » Fri Aug 01, 2014 2:14 am

the design is completely minimalthis watch also comes in whitedefinitely one of the more futuristic inspired watcheslooking elan that makes the mathematical product oakleys sale gucci sunglasses, ,
They look so different and lead the fashion trend. The exaggerated sunglasses are full of dramatic effect with particular styles.. I believe that almost everyone prefer to buy real designer sunglasses. However, there is no doubt that real designer sunglasses are so expensive that common people cannot afford them. discount oakley sunglasses gucci sunglasses, ,
The last initiative will be the to learn more about here and now going to be the purchaser allowing an individual going to be the home&acirc;s title and keys. Most states need to get together that going to be the buyer&acirc;s interest as part of your real estate be the case recorded at an all in one county&acirc;s records office to learn more about ensure that going to be the the client is the a well known fact owner having to do with title for more information on the real - estate Other it is certainly plausible and you'll have be the case able for additional details on claim a stronger interest oakley sunglasses sale mk sunglasses, ,
The lenses of the Lennon specs are circular and are positioned relatively close to each other, a style that looks particularly good on people with diamond- or oval-shaped faces. Men and women who are looking to soften the sharp angles or edges of their face should try a pair of John Lennon sunglasses..
.
dwfcllmudn9R
 
Posts: 47
Joined: Fri Aug 01, 2014 2:00 am


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

Who is online

Users browsing this forum: No registered users and 1 guest

cron