Post reply

Exec Oracle Pkg and store output param to GlobalVar using Execute SQL Task

  • January 13, 2004 at 4:12 am

    #161384

    Dear All,

    Can somebody help me to show the way execute Oracle Package/Stored Proc/Function and then stored the output parameter or return value to DTSGlobalVariable using Execute SQL Task in DTS Package?

    Regards,

    Nugon

  • January 14, 2004 at 10:04 am

    #490013

    hi!

    i wouldn't use an execute SQL task for that (don't even know if that is possible at all).

    create and active-x script task instead. connect to your ORACLE database by using standard ADO (MS MDAC) library. issue your command by using an ADO command object capturing the return value of a, say ORACLE function into your global variable. close your connection.

    very brief example of VBScript code:

    dim conn, cmd
set conn = CreateObject("ADODB.Connection")
conn.Open( <connection string> )
set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = <your ORACLE command>
cmd.Execute
DTSGlobalVariables( <your global var> ) = <output param/return value of command>
conn.Close
cmd = Nothing
conn = Nothing

    for further information on ADO refer to MSDN.

    best regards, chris.

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

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