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

• nugon19

  Valued Member

  Points: 71

  More actions

  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

• cneuhold

  Hall of Fame

  Points: 3375

  More actions

  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.