SSIS SQL Task - Valid Oracle Code Fails in SQL Task

Question

Post reply

SSIS SQL Task - Valid Oracle Code Fails in SQL Task

kellerb

Old Hand

Points: 330
More actions
January 20, 2011 at 12:27 pm

#149794

I'm attempting to automate the creation of partitions in a Business Intelligence Dev Studio project. The following code functions properly when executed through the Toad for Oracle editor, but will not function as an SSIS SQL Task:
begin
execute immediate ('alter table dept.umaster add partition U' || to_char(sysdate, 'IYYYMMDD') || ' values less than (to_date(''' || to_char(sysdate+1, 'DD-MON-YYYY') || ''')) logging nocompress');
end;
I loaded the Oracle OLE DB provider instead of the MS provider as noted elsewhere online, but this did not change the results. Any suggestions?

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

kellerb Old Hand Points: 330 More actions · Answer 1

If I attempt to execute the code as a procedure, I get the following error:

[Execute SQL Task] Error: Executing the query "begin

dept.newUmasterPartition;

end;" failed with the following error: "ORA-01858: a non-numeric character was found where a numeric was expected

ORA-06512: at "DEPT.NEWUMASTERPARTITION", line 4

ORA-06512: at line 1". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If I attempt to load the previous code directly into the SSIS SQL Statement Field, I get the following error:

[Execute SQL Task] Error: Executing the query "begin

execute immediate('alter table dept.um..." failed with the following error: "ORA-01858: a non-numeric character was found where a numeric was expected

ORA-06512: at line 1". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Does anyone have a suggestion? Thanks!

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

These aren't Oracle script errors, but SSIS object errors.

My first thought is to turn BypassPrepare to TRUE.

After that, we'd have to evaluate the properties and settings in the object.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

kellerb Old Hand Points: 330 More actions · Answer 3

Here are the current settings for the object:

I don't believe that I changed anything other than toggling "Bypass Prepare" and selecting the proper connection.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

Can you screen shot the next two windows as well (Parameters and result set)? Or if you can simply confirm they're empty, that would help.

Next troubleshoot would be to make sure you can get a simple Oracle query through the link. Is there an Oracle equivalent of select top 1 * from syscolumns?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

kellerb Old Hand Points: 330 More actions · Answer 5

The other screens are empty. In addition, I am able to run other statements through the same task without error, even the non dynamic version of code from my original post:

BEGIN

ALTER TABLE DEPT.UMASTER ADD PARTITION U20110119 VALUES LESS THAN (TO_DATE('20-Jan-2011') LOGGING NOCOMPRESS;

END;

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

kellerb (1/24/2011)
The other screens are empty. In addition, I am able to run other statements through the same task without error, even the non dynamic version of code from my original post:

Then I was wrong, I'm sorry. It's something to do with the driver being used by SSIS, and I'm out of my depth.

You could feed the statement into it via expression/variable, and thus remove the local dynamic nature. Best I've got, I'm afraid.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA