MSSQLTips

This post is related to this tip: Using the Script Component to Execute PL/SQL Code in an Oracle Package from SQL Server Integration Services

http://www.mssqltips.com/tip.asp?tip=1448

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

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.

 

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

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

 

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.