Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Execute PLSQL Code in an Oracle Package from SSIS


By:   |   Last Updated: 2008-03-05   |   Comments (15)   |   Related Tips: 1 | 2 | 3 | More > Integration Services Oracle

Problem
We have a number of Oracle stored procedures that we need to execute from an SSIS package.  The PL/SQL code is deployed in various Oracle packages.  Many of these stored procedures require parameters and we're struggling to find the best way to do this given that the various Oracle connection manager types don't support parameterized queries.  Do you have any ideas?

Solution
The lack of support for parameterized queries in the various Oracle connection managers is an unfortunate limitation in SSIS.  However, as is usually the case, there has to be an alternative that will get the job done.  The solution that I prefer is to use the Script Component in the Data Flow to execute the Oracle PL/SQL code.  The advantage of this approach is that you can parameterize the PL/SQL code as well as take care of other things like setting the transaction isolation level, managing transactions, and handling exceptions.  This is done by implementing what's called an anonymous PL/SQL block.

Let's walk through the steps involved to implement this.  To add a little bit of context to this example, the requirement was to extract certain student loan records, transform them into a standard format used by educational institutions (called CommonLine4 or CL4), and send the CL4 file to a disbursing agent who transfers the funds to the school on the student's behalf.  While you may not think of SSIS right away for this task, it worked well.

Start out with a new SSIS package, right click in the Connection Managers area and select Add New ADO.NET Connection.  Select the Provider as shown; this is the Microsoft .NET Data Provider for Oracle.  The Server name is the Oracle instance name.

The stored procedure we need to call takes a single input parameter - the number of days before the disbursement date; i.e. we want to extract the loans that have a scheduled disbursement within the next so many days.  Setup a package variable as follows:

For demonstration purposes the value is simply hard-coded.  However, check out our previous tips on package configuration using SQL Server or XML files.

The next step is to add a Data Flow task, then add a Script Component to the Data Flow.  When prompted for the Script Component Type select Source.

There are three steps to be performed on the Script Transformation Editor that is displayed after selecting the Script Component Type.  Change Output 0 to OutputBuffer on the Inputs and Outputs  page.


Add the package variable that we created earlier to the ReadOnlyVariables list on the Script page.  This allows us to access the variable in the VB.NET code.  You can specify multiple variables if necessary; just separate them with a comma.

Add the Oracle connection manager that we defined earlier to the Connection Managers page.  This is an important step; it allows us to use the connection manager in the VB.NET code but SSIS takes care of managing the connection.

Return to the Script page in the Script Transformation Editor and click the Design Script button to enter the VB.NET code as shown below:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
''' ADD IMPORT
Imports System.Data.OracleClient
Public Class ScriptMain
    Inherits UserComponent
''' DECLARATIONS
    Private connMgr As IDTSConnectionManager90
    Private oracleConn As OracleConnection
    Private oracleCmd As OracleCommand
''' GET CONNECTION
    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
      connMgr = Me.Connections.ORCL
      oracleConn = CType(connMgr.AcquireConnection(Transaction), OracleConnection)
    End Sub
''' SETUP PL/SQL BLOCK
    Public Overrides Sub PreExecute()
        Dim plsql As String = 
          "BEGIN " + _
          "    SET TRANSACTION ISOLATION LEVEL READ COMMITTED; " + _
          "    CL4_pkg.InitExtract (:DaysToDisbursement); " + _
          "    COMMIT; " + _
          "EXCEPTION " + _
          "    WHEN OTHERS THEN " + _
          "        ROLLBACK; " + _
          "        RAISE; " + _
          "END;"
        oracleCmd = New OracleCommand(plsql, oracleConn)
        With oracleCmd
            .CommandType = CommandType.Text
            .Parameters.Add(":DaysToDisbursement", OracleType.Number)
        End With
    End Sub
''' DISPOSE
    Public Overrides Sub PostExecute()
        If Not oracleCmd Is Nothing Then
            oracleCmd.Dispose()
        End If
    End Sub
''' RELEASE
    Public Overrides Sub ReleaseConnections()
        connMgr.ReleaseConnection(oracleConn)
    End Sub
''' EXECUTE PL/SQL CODE
    Public Overrides Sub CreateNewOutputRows()
        Try
            OutputBuffer.AddRow()
            Dim days = Me.Variables.vDaysToDisbursement
            With oracleCmd
                .Parameters(":DaysToDisbursement").Value = days                  
                .ExecuteNonQuery()
            End With
        Catch ex As Exception
            Me.ComponentMetaData.FireError(-1, "InitExtract", 
               ex.Message, String.Empty, 0, True)
        End Try
        OutputBuffer.SetEndOfRowset()
    End Sub
End Class

Let's wrap up with a brief explanation of each comment in the above block of VB.NET code:

  • ADD IMPORT: add the Oracle client namespace; this is the namespace where the Oracle ADO.NET objects are found in the .NET framework.
  • DECLARATIONS: declare the SSIS connection manager and the Oracle ADO.NET objects.
  • GET CONNECTION: get the Oracle connection from the SSIS connection manager.
  • SETUP PL/SQL BLOCK: this is where the anonymous PL/SQL block is defined.  Note that we can parameterize the statement, set the transaction isolation level, and catch any exceptions.
  • DISPOSE: dispose of the connection.
  • RELEASE: tell the connection manager we are done with the connection.
  • EXECUTE PL/SQL CODE: Execute the anonymous PL/SQL block.

While there is quite a bit of code shown in the Script Component, most of it is just boilerplate stuff; only a small amount of the code is different in each instance of the Script Component.

Next Steps

  • If you need to execute Oracle code and pass in a bunch of parameters, you might give this approach a try.  The alternative of concatenating the parameters together into a string and using an Execute SQL task to execute the command from a string isn't necessarily bad, but if you have several parameters it can be tedious and error-prone.
  • An enhancement to the above approach could be to deploy the code into a DLL, exposing a single function that could be called from the Script Component.


Last Updated: 2008-03-05


next webcast button


next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, January 11, 2013 - 8:49:05 AM - Raymond Barley Back To Top

In this case you don't need a cursor; you can simply execute this T-SQL:

INSERT INTO tbnseinstrument VALUES(CV.ins_id,CV.ins_name)

SELECT * FROM tbinstrument

 

You should specify the column names instead of SELECT *

 

 

 

 

 


Friday, January 11, 2013 - 6:00:22 AM - chetan Back To Top

Hi..sir..i want to fiere cursor of pl-sql in vb.net ,i know syntax of cursor in oracle but it's i try in ms sql server 2005 but it's not working

so how to write cursor for vb.net or ms sql server and how to fiere from vb.net becuse i am creating vb.net form

my oracle cursor code are following..

 

DECLARE

CURSOR C IS SELECT * FROM tbinstrument;

BEGIN

FOR CV IN C

LOOP

INSERT INTO tbnseinstrument VALUES(CV.ins_id,CV.ins_name);

END LOOP;

END;

/

thanks


Monday, October 29, 2012 - 9:15:37 AM - Ray Barley Back To Top

I can't believe I forgot about having to specify the command parameter .Parameters.Add(":p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

Glad you got it working!

Now we have a pretty complete example.


Monday, October 29, 2012 - 12:00:25 AM - ck Back To Top

Hi Ray

Thanks for your help and successfully retrieve the data accordingly.
At first, I still encountered error upon calling the stored proc with invalid parameters passing.
Therefore, I added a parameter p_cursor in my PreExecute module as an output for my Ref_Cursor and it solved the issue.

Below is my code:

/*Oracle SP Script - Start*/
CREATE OR REPLACE PROCEDURE sp_getinfo
(
  P_DATE IN VARCHAR2,
  RCD OUT SYS_REFCURSOR
)
  AS
BEGIN
  OPEN RCD FOR
    select name, age from info where dob = to_date(P_DATE, 'yyyymmdd');
END;

/
/*Oracle SP Script - End*/

 

/*Script editor - Start*/
Public Overrides Sub PreExecute()
 Dim plsql As String = "BEGIN" + _
 "  sp_getinfo(:p_date,:p_cursor);" + _
 "END;"

        oracleCmd = New OracleCommand(plsql, oracleConn)
        With oracleCmd
            .CommandType = CommandType.Text
            .Parameters.Add(":p_date", OracleType.Varchar)
     .Parameters.Add(":p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
        End With
End Sub

Public Overrides Sub CreateNewOutputRows()
        Try
            Dim days = Me.Variables.vDate
            oracleCmd.Parameter(":p_date").Value = days

            Dim oDataReader As OracleDataReader = oracleCmd.ExecuteReader
            While oDataReader.Read = True
                OutputBuffer.AddRow()
                OutputBuffer.name = oDataReader.GetString(0)
                OutputBuffer.age = oDataReader.GetString(1)
            End While
        Catch ex As Exception
            Me.ComponentMetaData.FireError(-1, "InitExtract",
               ex.Message, String.Empty, 0, True)
        End Try
        OutputBuffer.SetEndOfRowset()
    End Sub
End Class

/* Script Editor - End*/

 


Thursday, October 25, 2012 - 2:16:34 PM - Ray Barley Back To Top

No.  The code I showed above came from a .NET project where I called stored procs that return ref cursors.


Thursday, October 25, 2012 - 12:01:46 PM - ck Back To Top

Do I need to add any additional parameter declaration for "RCD OUT SYS_REFCURSOR" in the PreExecute and CreateNewOutputRows?


Thursday, October 25, 2012 - 10:08:07 AM - Ray Barley Back To Top

What you need to do is call the ExecuteReader method on the oracle command instead of the ExecuteNonQuery (in the CreateNewOutputRows method)

ExecuteReader will return an OracleDataReader which you use to iterate through the result set.  For example instead of using With oracleCmd

 

oracleCmd.Parameters(":p_date").Value = days

Using oDataReader As OracleDataReader = oracleCmd.ExecuteReader()

While oDataReader.Read() = True

-- access columns in result set using ordinals; eg

-- oDataReader.GetOracleDate(0).Value

-- oDataReader.GetOracleString(1).Value

End While

 

 

 


Thursday, October 25, 2012 - 8:00:26 AM - ck Back To Top

Hi Ray

I'm calling an Oracle SP by passing an input parameter and return me value through ref_cursor as below:

/*Oracle SP Script - Start*/
CREATE OR REPLACE PROCEDURE sp_getinfo
(
  P_DATE IN VARCHAR2,
  RCD OUT SYS_REFCURSOR
)
  AS
BEGIN
  OPEN RCD FOR
    select name, age from info where dob = to_date(P_DATE, 'yyyymmdd');
END;

/
/*Oracle SP Script - End*/

How should I add in the return value from the oracle SP

/*Script editor - Start*/
Public Overrides Sub PreExecute()
 Dim plsql As String = "BEGIN" + _
 "  sp_getinfo(:p_date);" + _
 "END;"

        oracleCmd = New OracleCommand(plsql, oracleConn)
        With oracleCmd
            .CommandType = CommandType.Text
            .Parameters.Add(":p_date", OracleType.Date)
        End With
End Sub

Public Overrides Sub CreateNewOutputRows()
        Try
            OutputBuffer.AddRow()
            Dim days = Me.Variables.vDate
            With oracleCmd
                .Parameters(":p_date").Value = days                 
                .ExecuteNonQuery()
            End With
        Catch ex As Exception
            Me.ComponentMetaData.FireError(-1, "InitExtract",
               ex.Message, String.Empty, 0, True)
        End Try
        OutputBuffer.SetEndOfRowset()
    End Sub
End Class


/*Script editor - End*/


Monday, April 23, 2012 - 2:22:16 PM - Ray Barley Back To Top

If you want to use the technique in this tip and return rows, take a look at this tip: http://www.mssqltips.com/sqlservertip/1787/oracle-parameterized-queries-in-sql-integration-services-ssis/ 

As far as the server name for connecting to Oracle, you do get it from tnsnames.ora.  Is it possible that you package protection level is not saving the password in the SSIS package?


Monday, April 23, 2012 - 9:02:31 AM - sqlprof Back To Top

hi

thanks for the detailed post firstly.

i'm into a similar situation (i use win 2008 server 64-bit and sql 2008r2 x64) wherein i need to fetch data from oracle package function. the return type of function is sys_refcursor. i tried using linked server, but it says the provider MSDAORA isn't registered. on googling, it seems this provider isn't made by MS for x64 types. i also tried setting linked server using the available provider (OraOLEDB.Oracle) in SSMS->Linked Server\Server Objects, but even here it says...theisn't registered..

so, on further googling i found ur post, as i thought to write SSIS script to call this oracle package function. on following ur steps, i get to see the .net Providers\OracleClient Data Provider, but the connection doesn't seem to establish. i don't understand what to punch in the Server Name field. i used the alias name created in Oracle Net Manager here (as i can't directly enter the Oracle connection string here) and further entered username/pwd fields. but the test connection fails. additionally, to get rid of this, i used the name in tnsNames.ora in the Server Name field, but even that doesn't work. can u help me how to do i go about in connecting it? also, would ur .net script work if i fire a Oracle query in SSIS script like .. select user_name.package_name.function_name() from dual;


Monday, July 06, 2009 - 4:53:56 AM - raybarley Back To Top

Take a look at this new tip: http://www.mssqltips.com/tip.asp?tip=1787&home

It shows how to execute a parameterized query against an oracle database. 


Friday, July 03, 2009 - 7:13:14 AM - raybarley Back To Top

Here is an example showing how to execute a query against an oracle database.  I have one parameter.  Parameters in oracle have to be prefixed with a colon.  If you want more than one just follow the pattern; i.e. WHERE DEPTNO = :DEPT OR SOMECOLUMN = :ANOTHERPARAMTER then add a line of code to define the parameter and another to assign it a value.

This example selects from the SCOTT.EMP table that comes with oracle.  The query is:

SELECT ENAME, JOB FROM SCOTT.EMP WHERE DEPTNO = :DEPT

I have an oracle instance installed where I just accepted all defaults.  My connection string is:

Data Source=orcl;User ID=scott;Password=tiger;Persist Security Info=True;Unicode=True;

One thing I do is to use the connection manager in the SSIS package to define the oracle connection then just access it in the code.  This is exactly what's done in the tip example.

This example uses the Script Component in the Data Flow. When you drag a Script Component from the Toolbox onto
the data flow, a dialog will popup with radio buttons for Source, Destination and Transformation; pick Source.

The Script Component Source allows you to write code to get data then add it to the data flow.  Normally you would use a Data Flow Source component to get data from Excel, SQL Server, etc.  

When you configure the Script Component you have to define the output columns.  Under Inputs and Outputs you will see Ouput 0; rename that to just Output. Expand it and add the columns that you need.  In this example I added EmployeeName and Job, both defined as
Unicode string [DT_WSTR].

I added a variable to the package and named it v_dept.  I hard code the value to 10.  When you configure the Script Component Source, you have to specify the v_dept variable in the ReadOnly variables on the script page; then you canuse it in the code.

In the sample code below there are a number of steps.  However, most of the code is exactly the same as what's in the tip.  The only differences are the The PreExecute function which sets up the oracle command; it's ust a query with a single parameter.  The CreateNewOutputRows function executes the oracle command.  It supplies the value for the parameter then iterates through the result set.  For each row in
the result set it adds a row to the data flow and assigns the values to the output fields 

One more thing before we get to the code; you have to add a reference to the System.Data.OracleClient assembly; this is covered in the tip.

Now here is the code:

 Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper



' STEP 1: add import of OracleClient

Imports System.Data.OracleClient




Public Class ScriptMain
    
  Inherits UserComponent

    

' STEP 2: add variables
    
Private connMgr As IDTSConnectionManager90
    
Private oracleConn As OracleConnection
    
Private oracleCmd As OracleCommand
    
Private oracleRdr As OracleDataReader

    

' STEP 3: add override to get connection
    
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

        
  connMgr = Me.Connections.ORCL
        
  oracleConn = CType(connMgr.AcquireConnection(Transaction), OracleConnection)

    
End Sub

    

' STEP 4: add override to setup the oracle command
    
Public Overrides Sub PreExecute()

        
  Dim sql As String = "SELECT ENAME, JOB FROM SCOTT.EMP WHERE DEPTNO = :DEPT"
        
  oracleCmd = New OracleCommand(sql, oracleConn)
        
  With oracleCmd
            
    .CommandType = CommandType.Text
            
    .Parameters.Add(":DEPT", OracleType.Number)
        
  End With

    
End Sub

    

' STEP 5: Add override to dispose of the oracle command
    
Public Overrides Sub PostExecute()
        
  If Not oracleCmd Is Nothing Then
            
    oracleCmd.Dispose()
        
  End If
    
End Sub

    

' STEP 6: Add override to release the oracle connection
    
Public Overrides Sub ReleaseConnections()

        
  connMgr.ReleaseConnection(oracleConn)

    
End Sub

    

' STEP 7: Add code to execute the query and add the results
    
' to the data flow
    
Public Overrides Sub CreateNewOutputRows()

        
  Dim DEPT As Integer = Me.Variables.vdept
        
  With oracleCmd
            
    .Parameters(":DEPT").Value = DEPT
        
  End With

        
  oracleRdr = oracleCmd.ExecuteReader()
        
  While oracleRdr.Read()
            
    OutputBuffer.AddRow()
            
    OutputBuffer.EmployeeName = oracleRdr.GetString(0)
            OutputBuffer.Job = oracleRdr.GetString(1)
        
  End While

        
  OutputBuffer.SetEndOfRowset()

    
End Sub



End Class

 


Tuesday, June 30, 2009 - 7:15:55 PM - fsanchez Back To Top

Hi Ray,

Thanks for responding to my post.  I do appreciate it.  As you know, I am trying to get up to speed with the new platform and help or suggestions is greatly appreciate it.  The target database is Oracle so all objects are created in the Oracle database.  I want to migrate all my ActiveX scripts over to the new platform but don't know what is the right way.  Any suggestion?  Do you have sample code that illustrate more than one parameter and is not calling a package function but is executing query?

Fernando


Tuesday, June 30, 2009 - 6:23:42 PM - raybarley Back To Top

The original tip came from an idea that I came up with to use anonymous PL/SQL blocks to put a wrapper around calls to stored procedures and functions in Oracle packages that take multiple parameters.  The anonymous PL/SQL blocks also allow me to handle the transaction commit or rollback in the block rather than putting that code in the Oracle stored procedure or function.  

Since PL/SQL has a rich set of capabilities, we felt it better to leverage it as much as possible and just call it from the SSIS packages.

 


Tuesday, June 30, 2009 - 12:13:40 PM - fsanchez Back To Top

Hi,

I am in the process of converting an ActiveX script to the new SSIS platform and I am looking for some ideas or prefer methods.  I saw this posting tip and I am very interested in knowing if there are more examples.  Currently, my ActiveX script performance several steps, it logs an audit record in the ELT_JOB_LOG table when the package is executed if the job id is not provided by the parent package.  It also logs an audit record in the ETL_PROCESS_LOG table for the package.   Most importantly, the ActiveX scripts ensures that the package variables are initialize with the values for downstream reference.  Each package has the identical ActiveX script for logging auditing information.

Here is code from one of my ActiveX script which I am trying to migrate.  I recently created a function that returns the next sequential process log id but I having difficult using it your sample code to execute. The purpose of using an Oracle function is to move the process to the database and just return the necessary values when called.

CREATE OR REPLACE FUNCTION SET_PROCESS_INFO_FUNC(FV_PROCESS_ID NUMBER,FV_JOB_ID NUMBER)
RETURN VARCHAR2 IS

PRAGMA AUTONOMOUS_TRANSACTION;

    LV_PROCESS_LOG_ID               NUMBER;
    LV_JOB_ID                       NUMBER;

BEGIN

   /*********************************************************************/
   /** CHECK IF THE JOB ID WAS PROVIDED BY THE CALLING PROCESS (PARENT)**/
   /** IF THE JOB ID WAS NOT PROVIDED THEN GENERATE/CREATE A NEW       **/
   /** JOB ID FOR THE PROCESS                                          **/
   /*********************************************************************/
  
    IF FV_JOB_ID IS NULL THEN

        SELECT NVL(MAX(LV_JOB_ID),0)+1 AS JOB_ID
          INTO LV_JOB_ID
          FROM ETL_JOB_LOG;

        INSERT INTO ETL_JOB_LOG
        SELECT LV_JOB_ID,
               'RUNNING' AS JOB_STATUS,
               FV_PROCESS_ID AS PROCESS_ID,
               TRUNC(SYSDATE) AS START_DT,
               TRUNC(SYSDATE) AS SYSTEM_DT,
               TO_CHAR(SYSDATE,'HH12:MI:SS AM') AS START_TIME,
               NULL AS END_TIME,
               DBMS_UTILITY.GET_TIME AS BTIME,
               NULL AS ETIME,
               NULL AS ELAPSED_TIME_SEC
          FROM DUAL;

        COMMIT;

    ELSE

        LV_JOB_ID := FV_JOB_ID;

    END IF;

   /*********************************************************************/
   /** CHECK IN SEE IF THE PROCESS IS ALREADY RUNNING                  **/
   /** IF THE PROCESS IS ALREADY RUNNING LOG THIS JOB AS FAILED AND    **/
   /** RETURN THE PROCESS_LOG_ID TO THE CALLING PROCESS AS             **/
   /** VERIFICATION                                                    **/
   /*********************************************************************/

    SELECT PROCESS_LOG_ID
      INTO LV_PROCESS_LOG_ID
      FROM ETL_PROCESS_LOG
     WHERE PROCESS_STATUS = 'RUNNING'
       AND PROCESS_ID     = FV_PROCESS_ID;

    UPDATE ETL_JOB_LOG
       SET JOB_STATUS       = 'FAILED',
           END_TIME         = TO_CHAR(SYSDATE,'HH12:MI:SS AM'),
           ETIME            = DBMS_UTILITY.GET_TIME,
           ELAPSED_TIME_SEC = (DBMS_UTILITY.GET_TIME-BTIME) / 100
     WHERE LV_JOB_ID  = LV_JOB_ID
       AND PROCESS_ID = FV_PROCESS_ID;

     COMMIT;

     RETURN TO_CHAR(LV_PROCESS_LOG_ID);

EXCEPTION
     WHEN NO_DATA_FOUND THEN

         /*********************************************************************/
         /** IF WE MADE IT THIS FAR, THE PROCESS IS NOT RUNNING AND WE CAN   **/
         /** LOG THE AUDIT RECORD IN THE ETL_PROCESS_LOG AUDIT TABLE         **/
         /*********************************************************************/

          SELECT NVL(MAX(PROCESS_LOG_ID),0)+1 AS PROCESS_LOG_ID
            INTO LV_PROCESS_LOG_ID
            FROM ETL_PROCESS_LOG;

          INSERT INTO ETL_PROCESS_LOG
          SELECT LV_PROCESS_LOG_ID,
                 LV_JOB_ID,
                 FV_PROCESS_ID,
                 'RUNNING' AS PROCESS_STATUS,
                 TRUNC(SYSDATE) AS START_DT,
                 TO_CHAR(SYSDATE,'HH12:MI:SS AM') AS START_TIME,
                 NULL AS END_TIME,
                 DBMS_UTILITY.GET_TIME AS BTIME,
                 NULL AS ETIME,
                 NULL AS ELAPSED_TIME_SEC
            FROM DUAL;

          COMMIT;

         /*********************************************************************/
         /** UPDATE THE ETL_PROCESS_CONTROL TABLE WITH A START STATUS        **/
         /** FOR THE CURRENT PROCESS                                         **/
         /*********************************************************************/

          UPDATE ETL_PROCESS_CONTROL
             SET STATUS       = 'STARTED',
                 LAST_UPDATED = SYSDATE
           WHERE PROCESS_ID = FV_PROCESS_ID;

         COMMIT;

RETURN TO_CHAR(LV_PROCESS_LOG_ID);

END SET_PROCESS_INFO_FUNC;
/

 

'**********************************************************************
'  Visual Basic ActiveX Script
'**********************************************************************
'  Script Name..: xScriptTaskLogProcess.vbs
'  DTS Package..:                             
'
'  Comments.....:                                                      
'                                                                     
'
'***********************************************************************

'***********************************************************************
' COMMAND TYPE ENUM VALUES
'***********************************************************************

Const adCmdUnKnown    = &H0008
Const adCmdText       = &H0001
Const adCmdTable      = &H0002
Const adCmdStoreProce = &H0004

Function Main()

 Dim oConn, oRecordSet, oCmd
 Dim sSQL, iProcessLogID, iProcessID, sProcessStatus, iJobID, iCurrentJobID, sJobStatus
 Dim sEndTime, iETime, iElapsedTimeSec
 
 iProcessLogID = NULL
 
 '**************************************************************
 ' CREATE ADO OBJECTS
 '**************************************************************
 Set oConn       = CreateObject("ADODB.Connection")
 Set oRecordSet  = CreateObject("ADODB.Recordset")
 Set oCmd        = CreateObject("ADODB.Command")
  
 '**************************************************************
 ' SET MSAORA CONNECTION PROPERTIES
 '**************************************************************
 oConn.Provider = "MSDAORA"
 oConn.Properties("Data Source").Value = DTSGlobalVariables("gsDataSource").Value
 oConn.Properties("User ID").Value     = DTSGlobalVariables("gsUserID").Value
 oConn.Properties("Password").Value    = DTSGlobalVariables("gsUserPassword").Value
 
 '**************************************************************
 ' THESE PROPERTIES ARE ONLY SET IF THE PROVIDER IS SQLOLEDB 
    '**************************************************************
 'oConn.Properties("Initial Catalog").Value = ""
 'oConn.Properties("Integrated Security").Value = ""


 '**************************************************************
 ' OPEN CONNECTIVITY TO THE DATABASE.
 '**************************************************************
 oConn.Open
 
 '**************************************************************
 ' SET THE ACTIVE CONNECTION TO THE TARGET DATABASE ADO OBJECT
 '**************************************************************
 oCmd.ActiveConnection = oConn
 oCmd.CommandType = adCmdText
 
 '**************************************************************
 ' PREPARE SQL STATEMENT FOR VERIFICATION OF RUN STATUS
 '**************************************************************
 sProcessStatus = "'" & "RUNNING" & "'"

 sSQL = "SELECT PROCESS_LOG_ID, JOB_ID" &_
        "  FROM ETL_PROCESS_LOG" &_
        " WHERE PROCESS_STATUS = " & sProcessStatus &_
        "   AND PROCESS_ID = " & CLng(DTSGlobalVariables("giProcessID").Value)


 '**************************************************************
    ' EXECUTE THE SQL STATEMENT                            
 '**************************************************************
    oRecordSet.Open sSQL, oConn


 '**************************************************************
    ' GENERATE NEW PROCESS LOG ID AND/OR JOB ID IF PROCESS IS NOT RUNNING
 '**************************************************************
    If Not(oRecordSet.EOF and oRecordSet.BOF) Then
  
  oRecordSet.MoveFirst
  iProcessLogID = oRecordSet.Fields("PROCESS_LOG_ID")
   
    End If
   
    oRecordSet.Close

 'MsgBox "Process Log ID = " & iProcessLogID
 
 If IsNull(iProcessLogID) Then
 
  sSQL = "SELECT NVL(MAX(PROCESS_LOG_ID),0) AS IDVALUE" &_
         "  FROM ETL_PROCESS_LOG"
        
        
  'MsgBox sSQL
  
  oRecordSet.Open sSQL, oConn
    
  If Not(oRecordSet.EOF and oRecordSet.BOF) Then
    
    oRecordSet.MoveFirst
    iProcessLogID = CLng(oRecordSet.Fields("IDVALUE")) + 1
    
    'MsgBox "Process Log ID = " & iProcessLogID
    
    DTSGlobalVariables("giProcessLogID").Value = CLng(iProcessLogID)
    
  End If
   
  oRecordSet.Close
  
  '**************************************************************
  ' CHECK TO SEE IF JOB ID WAS PROVIDED BY THE CALLING PCKG
  ' IF NOT, GENERATE JOB ID FOR PROCESS AND LOG IT
  '**************************************************************
  If ( DTSGlobalVariables("giJobID").Value = 0 ) Then
  
    DTSGlobalVariables("giParentExecFlag").Value = False
    
    sSQL = "SELECT NVL(MAX(JOB_ID),0) AS IDVALUE" &_
               "  FROM ETL_JOB_LOG"
                     
    oRecordSet.Open sSQL, oConn
    
    If Not(oRecordSet.EOF and oRecordSet.BOF) Then
     
     oRecordSet.MoveFirst
     iJobID = CLng(oRecordSet.Fields("IDVALUE")) + 1
     DTSGlobalVariables("giJobID").Value = CLng(iJobID)
     
    End If
    
    oRecordSet.Close
    
    'MsgBox "Job ID = " & iJobID
    
    oCmd.CommandText = "INSERT INTO ETL_JOB_LOG " &_
                                 "SELECT " & DTSGlobalVariables("giJobID").Value & ", " &_
                                           sProcessStatus & ", " &_
                                           DTSGlobalVariables("giProcessID").Value & ", " &_
                                           "SYSDATE" & ", " &_
                                           "SYSDATE" & ", " &_
                                           "TO_CHAR(SYSDATE,'HH12:MI:SS AM')" & ", " &_
                                           "NULL" & ", " &_
                                           "DBMS_UTILITY.GET_TIME" & ", " &_
                                           "NULL" & ", " &_
                                           "NULL" &_
                                    "  FROM DUAL"
  
        'MsgBox oCmd.CommandText
       
    '**************************************************************
    ' EXECUTE AND COMMIT SQL STATEMENT          
    '**************************************************************
    oCmd.Execute,,adExecuteNoRecords
    oCmd.CommandText = "Commit"
    oCmd.Execute,,adExecuteNoRecords
    
  Else
  
    DTSGlobalVariables("giParentExecFlag").Value = True

  End If
 
         'MsgBox "giParentExecFlag = " & DTSGlobalVariables("giParentExecFlag").Value
 
  oCmd.CommandText = "INSERT INTO ETL_PROCESS_LOG " &_
                           "SELECT " & DTSGlobalVariables("giProcessLogID").Value & ", " &_
                                     DTSGlobalVariables("giJobID").Value & ", " &_
                                     DTSGlobalVariables("giProcessID").Value & ", " &_
                                     sProcessStatus & ", " &_
                                     "SYSDATE" & ", " &_
                                     "TO_CHAR(SYSDATE,'HH12:MI:SS AM')" & ", " &_
                                     "NULL" & ", " &_
                                     "DBMS_UTILITY.GET_TIME" & ", " &_
                                     "NULL" & ", " &_
                                     "NULL" &_
                              "  FROM DUAL"
     
      'MsgBox oCmd.CommandText
  
  '**************************************************************
  ' EXECUTE AND COMMIT SQL STATEMENT          
  '**************************************************************
  oCmd.Execute,,adExecuteNoRecords
  oCmd.CommandText = "Commit"
  oCmd.Execute,,adExecuteNoRecords
 

  '**************************************************************
  ' PREPARE SQL STATEMENT FOR UPDATING ETL_PROCESS_CONTROL TABLE
  '**************************************************************

  sProcessStatus = "'" & "STARTED" & "'"

  oCmd.CommandText = "UPDATE ETL_PROCESS_CONTROL " &_
       "   SET STATUS       = " & sProcessStatus & "," &_
       "       LAST_UPDATED = SYSDATE " &_
       " WHERE PROCESS_ID   = " & CLng(DTSGlobalVariables("giProcessID").Value)

  'MsgBox oCmd.CommandText

  '**************************************************************
  ' EXECUTE AND COMMIT SQL STATEMENT          
  '**************************************************************
  oCmd.Execute,,adExecuteNoRecords
  oCmd.CommandText = "Commit"
  oCmd.Execute,,adExecuteNoRecords

  '**************************************************************
  ' CLOSE THE CONNECTION OBJECT                     
  '**************************************************************
  oConn.Close
 
  Main = DTSTaskExecResult_Success
  
 Else

  '**************************************************************
  ' IF THERE IS AN EXISTING JOB RUNNING THIS ETL PROCESS OR
  ' DTS PACKAGE, THE FOLLOWING STATEMENT WILL UPDATE THE CURRENT
  ' STATUS OF THE JOB TO FAILURE
  '**************************************************************
 
   '**************************************************************
   ' PREPARE AND EXECUTE SQL STATEMENT TO OBTAIN EXECUTION TIME
   '**************************************************************

   sSQL = "SELECT BTIME, " &_
     "       TO_CHAR(SYSDATE,'HH12:MI:SS AM') AS END_TIME, " &_
     "       DBMS_UTILITY.GET_TIME AS ETIME, " &_
     "       (DBMS_UTILITY.GET_TIME - BTIME)/100 AS ELAPSED_TIME_SEC " &_
     "  FROM ETL_JOB_LOG " &_
     " WHERE JOB_ID = " & CLng(DTSGlobalVariables("giJobID").Value) &_
     "   AND PROCESS_ID = " & CLng(DTSGlobalVariables("giProcessID").Value)

   'msgbox sSQL

   oRecordSet.Open sSQL, oConn


   '**************************************************************
   ' RETRIEVE RECORD FIELD VALUE FOR ETIME AND ELAPSED_TIME_SEC
   '**************************************************************
   If Not(oRecordSet.EOF and oRecordSet.BOF) Then

       oRecordSet.MoveFirst
       sEndTime        = oRecordSet.Fields("END_TIME")
       iETime          = oRecordSet.Fields("ETIME")
       iElapsedTimeSec = oRecordSet.Fields("ELAPSED_TIME_SEC")

   End If

   '**************************************************************
   ' CLOSE THE RECORD SET OBJECT                               
   '**************************************************************
   oRecordSet.Close
 
   '**************************************************************
   ' UPDATE JOB AUDIT STATUS                              
   '**************************************************************
   sJobStatus = "'" & "FAILED" & "'"
   sEndTime       = "'" & sEndTime & "'"

   oCmd.CommandText = "UPDATE ETL_JOB_LOG " &_
       "   SET JOB_STATUS = " & sJobStatus & "," &_
       "       END_TIME = " & sEndTime & "," &_
       "       ETIME    = " & CLng(iETime) & "," &_
       "       ELAPSED_TIME_SEC = " & CLng(iElapsedTimeSec) &_
       " WHERE JOB_ID     = " & CLng(DTSGlobalVariables("giJobID").Value) &_
       "   AND PROCESS_ID = " & CLng(DTSGlobalVariables("giProcessID").Value)

   'MsgBox oCmd.CommandText

   '**************************************************************
   ' EXECUTE AND COMMIT SQL STATEMENT          
   '**************************************************************
   oCmd.Execute,,adExecuteNoRecords
   oCmd.CommandText = "Commit"
   oCmd.Execute,,adExecuteNoRecords

   '**************************************************************
   ' CLOSE THE CONNECTION OBJECT                     
   '**************************************************************
   oConn.Close
 
   Main = DTSTaskExecResult_Failure
 
 End If

  
End Function


Learn more about SQL Server tools