Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 5, 1999

DTS How to...dynamically change...Tasks

By Darren Green

4 How to dynamically change...Tasks

I would recommend familiarising yourself with ScriptPkg before reading this article:

  • Dynamically change package properties utilising ScriptPkg

    This article will focus on the more complex process of changing Task properties, but for an explanation and examples of changing connection properties please see:

  • How to dynamically change...Connections

    On with the article..

    Each package contains a collection of Tasks, which not surprisingly contains Task objects. These tasks objects contain a CustomTask, which is where the interesting properties are stored. For example the Execute SQL Task contains a Custom Task (DTSExecuteSQLTask) with the following properties:

  • oCustomTask.Name
  • oCustomTask.Description
  • oCustomTask.SQLStatement
  • oCustomTask.ConnectionID
  • oCustomTask.CommandTimeout
  • In this case, perhaps the most interesting is the SQLStatement property, which holds the SQL query as seen in the DTS Designer. The easiest way to investigate the properties of a CustomTask is to build a package with the relevant task, and examine the ScriptPkg output. I have also written a simple utility that lists the CustomTask properties, DTSObject, which is very quick and easy to use.

    To gain reference to a CustomTask you need to know the CustomTaskID. This is a property of the Task object and identifies the type of CustomTask contained by the Task. These are listed below for convenience:

    Description CustomTaskID
    Active Script Task DTSActiveScriptTask
    Execute Process Task DTSCreateProcessTask
    Execute SQL Task DTSExecuteSQLTask
    Data Driven Query Task DTSDataDrivenQueryTask
    Transfer SQL Server Objects DTSTransferObjectsTask
    Send Mail DTSSendMailTask
    Bulk Insert Task DTSBulkInsertTask
    Transform Data Task DTSDataPumpTask
    OLAP Services Processing Task DTSOlapProcess.Task
    Padding Task PaddingTask.Padding
    GVCustomTask GVCustomTask.GlobalVariableToSQL

    N.B. All of the examples below are written in ActiveX Script (VBScript), and are designed for use within an Active Script Task. They will of course function equally well in Visual basic with a few minor tweaks.

    The following example examines a package, looking for an Execute Process Task. It will then update the ProcessCommandLine property, the file to execute, to a new value:

    Option Explicit
    Function Main()
        ' Declare Variables
        Dim oPKG
        Dim oTask
        Dim oCustomTask
        'Get Package Object
        Set oPKG = DTSGlobalVariables.Parent
        'Examine Tasks
        For Each oTask in oPKG.Tasks
            'Test for Execute Process Task
            If oTask.CustomTaskID = "DTSCreateProcessTask" Then
                'Get CustomTask (Execute Process Task) Object
                Set oCustomTask = oTask.CustomTask
                'Set new ProcessCommandLine value
                oCustomTask.Properties.Item("ProcessCommandLine").Value =_
                  "C:\NewPath\NewFile.exe"
            End If
        Next
        ' Clear Up
        Set oCustomTask = Nothing
        Set oTask = Nothing
        Set oPKG = Nothing
    
        Main = DTSTaskExecResult_Success
    End Function
    

    The one draw back with the example above is that it will amend every Execute Process Task. To amend a particular Execute Process Task, you will need to expand the Test line to include an additional condition. The two properties we could use are Name and Description. Name is automatically assigned by the designer (DTSTask_CustomTaskID_Count), and although it is defined as Read/Write in Books Online, for all of the Microsoft defined Tasks it can only be set and read via code. Description on the other hand is set via the Properties sheet of the Task in the Designer. This does of course mean that you must ensure the Description is unique for each task or at least unique to the group of tasks that you wish to be amended.

    To test for a specific instance(s) of a Task with a Description of "ExecProcTask Number 1" , expand the Test line as detailed below:

    Option Explicit
    Function Main()
    ...
    'Test for Execute Process Task (Specific Description)
            If oTask.CustomTaskID = "DTSCreateProcessTask" _
              And oTask.Description = "ExecProcTask Number 1" Then
    
    OR
    'Test for Execute Process Task (Specific Description)
            If oTask.CustomTaskID = "DTSCreateProcessTask" _
              And oTask.CustomTask.Description = "ExecProcTask Number 1" Then
    ...
    End If
        Next
    
        Main = DTSTaskExecResult_Success
    End Function
    

    If you do know the name of the task, then you can reference it directly. The two examples below both demonstrate this, but using different methods to gain reference to the ProcessCommandLine property:

    Option Explicit
    Function Main()
        ' Declare Variables
        Dim oPKG
        Dim oTask
        Dim oCustomTask
        'Get Package Object
        Set oPKG = DTSGlobalVariables.Parent
        ' Get the CustomTask for "DTSTask_DTSCreateProcessTask_1"
        Set oCustomTask = oPKG.Tasks("DTSTask_DTSCreateProcessTask_1").CustomTask
        ' Set new ProcessCommandLine value 
        oCustomTask.Properties.Item("ProcessCommandLine").Value =_
          "C:\NewPath\NewFile.exe"
        ' Clear Up 
        Set oCustomTask = Nothing 
        Set oTask = Nothing 
        Set oPKG = Nothing 
    
        Main = DTSTaskExecResult_Success
    End Function
    
    OR
    
    Option Explicit
    Function Main()
        ' Declare Variables
        Dim oPKG
        Dim oTask
        Dim oCustomTask
        'Get Package Object
        Set oPKG = DTSGlobalVariables.Parent
        ' Set new ProcessCommandLine value for "DTSTask_DTSCreateProcessTask_1"
        oPKG.Tasks("DTSTask_DTSCreateProcessTask_1").CustomTask.Properties._
          Item("ProcessCommandLine").Value = "C:\NewPath\NewFile.exe"
        ' Clear Up 
        Set oPKG = Nothing 
    
        Main = DTSTaskExecResult_Success
    End Function
    

    An obvious extension of this would be to set the property to value held elsewhere. My Text File Connection file name article demonstrates methods for GlobalVariables (DTS) and values held in a SQL table.

    The following example, just for the sake of another example, will change the SQLStatement of specified Execute SQL Task to the contents of a file.
    (This example uses the Microsoft FileSystemObject)

    Option Explicit
    Function Main()
        ' Declare Variables, Constants & Objects
        Dim oPKG
        Dim oTask
        Dim oCustomTask
        Dim oFS
        Dim oFile
        Const ForReading = 1
        Set oFS = CreateObject("Scripting.FileSystemObject")
    
        'Get Package Object
        Set oPKG = DTSGlobalVariables.Parent
        'Examine Tasks
        For Each oTask in oPKG.Tasks
            'Test for Execute SQL Task (Specific Description)
            If oTask.CustomTask.Description = "Execute SQL Task: FileDemo" _
              And oTask.CustomTaskID = "DTSExecuteSQLTask" Then
                'Get CustomTask (Execute SQL Task: FileDemo) Object
                Set oCustomTask = oTask.CustomTask
                'Open SQL File
                Set oFile = oFS.OpenTextFile("C:\DemoSQL.sql", ForReading)
                'Set SQLStatement to SQL File data
                oCustomTask.Properties("SQLStatement").Value = oFile.ReadAll
            End If
        Next
    
        ' Clear Up
        Set oCustomTask = Nothing
        Set oTask = Nothing
        Set oPKG = Nothing
    
        Main = DTSTaskExecResult_Success
    End Function
    


    MS SQL Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    Latest Forum Threads
    MS SQL Forum
    Topic By Replies Updated
    SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
    Need help changing table contents nkawtg 1 August 17th, 03:02 AM
    SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
    SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















    Thanks for your registration, follow us on our social networks to keep up-to-date