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 Feb 27, 2000

Build your own DTS CustomTask

By Darren Green

We all use the standard DTS Tasks within our packages, but sometimes we need that little bit more. The first choice is to write an ActiveX Script Task to do the job, and this usually suffices. Unfortunately there are some limitations as to what you can do, the most obvious being the inability to define variables as types. The other problem is one of maintenance, as the script must be maintained in every package that requires this custom functionality. This is where CustomTasks can be used. Once written and registered, a CustomTask is available for use in all packages straight from the Designer Toolbar.

Unfortunately with the method outlined below, properties for the CustomTask are unavailable for manipulation within VBScript.

For a better introduction to CustomTasks, have a look at the SQL Server Magazine article Constructing DTS Custom Tasks

On with the guide:

  • Create a new ActiveX DLL project. The sample project is called MyCustomTask, and the class is called CustomTask.

  • Reference the Microsoft DTS Package Object Library (Project->References menu).

    You can now start adding code to your class as described below -

  • First you must setup the CustomTask interface via the Implements statement, and declare the two required properties, Name & Description. If you have any properties of your own, then declare them here as well.
    Option Explicit
    
    'Required for CustomTask
    Implements DTS.CustomTask
    
    'Required Custom Task Properties
    Public Description As String
    Public Name As String
    
    'Define your own CustomTask Properties here
    Public MyProperty As String
    

  • Next set up the property handling for the required properties
    Private Property Get CustomTask_Name() As String
        CustomTask_Name = Name
    End Property
    
    Private Property Let CustomTask_Name(ByVal vNewValue As String)
        Name = vNewValue
    End Property
    
    Private Property Get CustomTask_Description() As String
        CustomTask_Description = Description
    End Property
    
    Private Property Let CustomTask_Description(ByVal vNewValue As String)
        Description = vNewValue
    End Property
    

  • Let DTS handle your CustomTask properties for you
    Private Property Get CustomTask_Properties() As DTS.Properties
        Set CustomTask_Properties = Nothing
    End Property
    

  • Create the Execute Method for your CustomTask
    Private Sub CustomTask_Execute(ByVal pPackage As Object, _
      ByVal pPackageEvents As Object, _
      ByVal pPackageLog As Object, _
      pTaskResult As DTSTaskExecResult)
    
     'Handle any Error
    On Error Goto ExecuteError
    
     'Set Result Flag to assume success
    pTaskResult = DTSTaskExecResult_Success
    
     'Put your processing code here
     'Put some more for luck
     '...
    MsgBox "CustomTask_Execute"
    
        GoTo ExitCode
    
    ExecuteError:
     'Set the Result Flag to Failure
    pTaskResult = DTSTaskExecResult_Failure
    
    ExitCode:
     'Clean up any objects here
    
    End Sub
    

    Using the template above you can successfully build a simple CustomTask, which is perfectly usable. By default DTS will implement a simple grid which enables you to view and set properties of the CustomTask, but our next stage is to enhance the usability and of the Task, by adding a user interface for these properties- Next Page



  • 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