Oracle Provider and Batch Statements

I am trying to use the Execute SQL Task to execute a very simple batch against the Oracle database:

Code Snippet

TRUNCATE TABLE FactTable1;
TRUNCATE TABLE FactTable2;
TRUNCATE TABLE FactTable3;
TRUNCATE TABLE FactTable4;
TRUNCATE TABLE FactTable5;

I have an Oracle Connection Manager configured to source off of an Oracle 9i database. I have tried both the Microsoft OLEDB provider for Oracle and the native Oracle provider.

When I run the above statement in SQL Plus or SQL Workbench, it works just fine.

In fact, it works in SQL Plus and SQL Workbench like this as well:

Code Snippet

TRUNCATE TABLE FactTable1

/
TRUNCATE TABLE FactTable2

/
TRUNCATE TABLE FactTable3

/
TRUNCATE TABLE FactTable4

/
TRUNCATE TABLE FactTable5

Although both of these would introduce the need to probably do some expressions to add the ";" or "/" conditionally only if the target is Oracle (we are *trying* to have one package support both SQL and Oracle) neither works. I get the following error message from SSIS command window:

...failed with the following error: "ORA-00911: invalid character". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Can anyone please provide some guidance on how to accomplish this with the Execute SQLTask task, taking into consideration that I very much generalize on the Oracle side?

Many thanks,

Rick

Well, I have tried every permutation that I can come up with and it seems that both Oracle providers simply do not support multiple statements in an Execute SQL Task.

If anyone knows otherwise, I would very much appreciate your input.

Rick

|||This is shot in the dark; but have you tried putting the staments in a file; then tell the execute sql task to use the file.|||

I haven't tried this and am happy to give it a shot, but can we talk about where this would be going? Obviously, reading the statements from a text file would not be a production solution but I imagine it would give you some insight as to where the issue might be?

Also, kind of along the same lines, I considered resorting to a ForEach Lookup tasks which certainly is more elegant than creating a bunch of sepereate Execute SQL Tasks but geez, something this fundamental (that works against SQL) shouldn't be this hard.

Many thanks.

Rick

|||Here's another suggestion... have you tried separating your statements with "GO"? GO is how most T-SQL clients parse for batches and although the ExecSQL works with other databases besides SQL Server, it uses the same parser as some of the other T-SQL clients we ship, so GO may work (unless the ExecSQL task is special casing T-SQL).|||My workaround is to create an Oracle stored proc wrappering in the statements in one SQL task, execute it in a second and drop the proc. in a third.