Thread: MS SQL Server General Questions/Oracle and SSIS; SQL tasks and procedures

Oracle and SSIS; SQL tasks and procedures

Source


Connection Manager

The only non-default property is RetainSameConnection set to True (connections to Oracle are expensive). Personally, I see no need for resource pooling but that is another post.

Execute SQL Task

General

ConnectionType
OLE DB
SQLSourceType
SQLStatement:
{call your_stupid_package.dumb_ass_procedure(?,?)}
or
BEGIN your_stupid_package.dumb_ass_procedure(?,?); END;
IsQueryStoredProcedure
False (usually greyed out, but can be set via Properties, but has no bearing on anything)

Parameter Mapping; Add each parameter:

Variable Name
whatever User::variable you have defined already
Direction
Input or Output
Data Type
VARCHAR (I have not been able to get anything else to work yet)
Parameter Name
the left to right sequence number of the parameter starting at zero (so, if you only have one the parameter name is 0)
Parameter Size
the size of the variable




Re: Oracle and SSIS; SQL tasks and procedures

DECLARE

BEGIN

YourStoredProcedure();

END;



This is an example of the Calling Oracle procedure from SSIS 'Execute SQl task'