Execute Package

Executing a DTS package is very simple, in its simplest form the execution contains for 3 steps. These 3 steps form the foundation for all more complicated forms for package execution.

The three steps are:

1. Load the package
2. Execute the package
3. Unload the package

In the first step you need to determine from which location you are going to load the package. DTS packages can be stored in three different locations/formats. It can be stored as:

1. A file, which most of the time will have the extension ".DTS", but this is not required. Internally the file is a COM structured storage file, which is capable of hosting multiple packages and multiple version of the same package inside the file.
2. The package can be stored in the SQL Server msdb database. Packages are stored in the msdb.dbo.sysdtspackages table. The package is stored in the packagedata column, this column contains a single version of the package. When packages are stored in the sysdtspackages table, versions are represented by multiple rows in the table. The contents of the package is a serialized format of the file format, stored as an image data type.
3. The last location/format that can be used for storing DTS packages is the Microsoft Meta Data Services, also known as the Microsoft Repository. When stored inside the Meta Data Services, the package is stored as object entities and object relationships.

The reason why the location is important is because it determines the method that you need to use on the Package object to load the package. In the same order as described above these are the methods you need to load a package from the respective location/

1. LoadFromStorageFile
2. LoadFromSQLServer
3. LoadFromRepository

After the package has been loaded, execution of the package is as simple as calling the Execute() method on the Package object, which does not take any parameters. The Execute() method is a blocking/synchronous call. If you need progress notifications etc, you need to use events.

After the Execute package call returns, the last step is to clean up the execution state by calling the UnInitialize() method. Until you have called the UnInitialize() method you can inspect the execution result of each step in the package and retrieve error information.

Below will follow examples on how to execute a DTS package in its simplest form using:

• Visual Basic
• Visual C#
• Visual C++

Visual Basic

This Visual Basic example assumes you have add a reference to your project to the "Microsoft DTSPackage Object Library", located in "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtspkg.dll", when using SQL Server 2000. When using SQL Server 7.0 the file is located in "C:\MSSQL7\BINN\dtspkg.dll" if you installed SQL Server in the default installation directory.

Option Explicit

Public Sub main()

    Dim oPkg As DTS.Package2
    Set oPkg = New DTS.Package2
    
    oPkg.LoadFromSQLServer "(local)\dev", , , _
        DTSSQLStgFlag_UseTrustedConnection, , , , _
        "PackageExecutionWithParams"
    
    oPkg.Execute
    oPkg.UnInitialize
    
    Set oPkg = Nothing

End Sub

goto top

Visual C#

using System;
using System.Runtime.InteropServices;
using DTS;

namespace DTS
{
    /// <summary>
    /// Summary description for ExecPkg.
    /// </summary>
    class ExecPkg
    {
        [MTAThread]
        static void Main(string[] args)
        {
            try
            {
                Package2Class package = new Package2Class();
                object pVarPersistStgOfHost = null;

                /* if you need to load from file
                package.LoadFromStorageFile(
                    "c:\\TestPackage.dts",
                    null,
                    null,
                    null,
                    "Test Package",
                    ref pVarPersistStgOfHost);
                */

                package.LoadFromSQLServer(
                    "(local)\\dev",
                    null,
                    null,
                    DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,
                    null,
                    null,
                    null,
                    "Hello DTS",
                    ref pVarPersistStgOfHost);

                package.Execute();
                package.UnInitialize();

                                // force Release() on COM object
                //
                System.Runtime.InteropServices.Marshal.ReleaseComObject(package);
                package = null;
            }
            catch(System.Runtime.InteropServices.COMException e)
            {
                Console.WriteLine("COMException {0}", e.ErrorCode.ToString() );
                Console.WriteLine("{0}", e.Message);
                Console.WriteLine("{0}", e.Source);
                Console.WriteLine("Stack dump\n{0}\n", e.StackTrace);
            }
            catch(System.Exception e)
            {
                Console.WriteLine("Exception");
                Console.WriteLine("{0}", e.Message);
                Console.WriteLine("{0}", e.Source);
                Console.WriteLine("Stack dump\n{0}\n", e.StackTrace);
            }
       } // main
    } // class ExecPkg
} // namespace DTS

download ExecPkg.zip

goto top

Visual C++

// ***************************************************************************
// Copyright (C) 1991-2001 SQLDev.Net
//
// $BeginHeader$
//
// @filename@: ExecPkg.h
// @author@: GertD@SQLDev.Net
// @description@: Shows basic package execution and result checking
// @remarks@: part of "Programming SQL Server DTS", by Gert E.R. Drapers
// @created@: 2001-10-01 17:00:00
// @lastsaved@: 2001-10-01 19:25:00
//
// update history:
// @version@ @initials@ @updatedate@ @description@
// 000000001 GD 2001-10-01 created
//
// $EndHeader$
// ***************************************************************************

#define STRICT
#define WIN32_LEAN_AND_MEAN
#define INC_OLE2
#define UNICODE
#define _UNICODE

#include <windows.h>
#include <stdio.h>
#include <tchar.h>
#include <comdef.h> // COM Compiler support for _bstr_t

#import "C:\\Program Files\\Microsoft SQL Server\\80\\Tools\\binn\dtspkg.dll" \
no_namespace rename("EOF", "EndOfFile")

void DisplayError(_com_error& pCE);
void DisplayStepResults(_Package2Ptr spPackage);

// ***************************************************************************
// Copyright (C) 1991-2001 SQLDev.Net
//
// $BeginHeader$
//
// @filename@:     ExecPkg.cpp
// @author@:       GertD@SQLDev.Net
// @description@:  Shows basic package execution and result checking
// @remarks@:      part of "Programming SQL Server DTS", by Gert E.R. Drapers
// @created@:      2001-10-01 17:00:00
// @lastsaved@:    2001-10-01 19:25:00
//    
// update history:
// @version@ @initials@ @updatedate@ @description@
// 000000001 GD         2001-10-01   created
//
// $EndHeader$
// ***************************************************************************

#include "ExecPkg.h"

INT _tmain(INT argc, TCHAR* argv[], TCHAR* envp)
{
    HRESULT hr;
    if SUCCEEDED(hr = OleInitialize(NULL) )
    {
try
{
    HRESULT hr;
            _Package2Ptr spPackage;

            if (SUCCEEDED(spPackage.CreateInstance(__uuidof(Package2))))
            {
                try
                {
                    _variant_t v; // VarPersistStgOfHost

                    hr = spPackage->LoadFromSQLServer(
                        _T("(local)"),             // _bstr_t ServerName
                        _T(""),     // _bstr_t ServerUserName
                        _T(""),     // _bstr_t ServerPassword
                        DTSSQLStgFlag_UseTrustedConnection,  
                        _T(""),     // _bstr_t PackagePassword
                        _T(""),     // _bstr_t PackageGuid
                        _T(""),     // _bstr_t PackageVersionGuid
                        _T("TestPackage"),                  // _bstr_t PackageName
                        &v);     // VARIANT* pVarPersistStgOfHost

                    hr = spPackage->Execute();

                    // get results per step
                    //
                    DisplayStepResults(spPackage);

                    hr = spPackage->UnInitialize();

                }
                catch(_com_error pCE)
                {
                    DisplayError(pCE);
                    spPackage.Release();     // Free the interface
                }
            }
        }
        catch(_com_error pCE)
        {
            DisplayError(pCE);
        }

        OleUninitialize();

    }
    else
    {
        _tprintf(_T("Call to CoInitialize failed.\n"));
    }

    return (0);

}

// **********************************************************************
// display error information
// **********************************************************************
void DisplayError(_com_error & pCE)
{
    _tprintf(_T("\n%s Error: %ld\r\n")
             _T("%s\r\n")
             _T("%s\r\n"),
            (TCHAR*)pCE.Source(),
            pCE.Error(),
            (TCHAR*)pCE.Description(),
            (TCHAR*)pCE.ErrorMessage());
}

// **********************************************************************
// display result of each Step
// **********************************************************************
void DisplayStepResults(_Package2Ptr spPackage)
{
    try
    {
HRESULT hr;
StepsPtr steps = spPackage->GetSteps();

for (long i = 1; i <= steps->GetCount(); i++)
{
            StepPtr step = steps->Item(i);

    if (hr = step->GetExecutionStatus() == DTSStepExecStat_Completed)
            {
                if (hr = step->GetExecutionResult() == DTSStepExecResult_Failure)
                {
                    long lErrorCode;
    BSTR bstrSource;
    BSTR bstrDescription;
    BSTR bstrHelpFile;
    long lHelpContext;
    BSTR bstrIDofInterfaceWithError;

    hr = step->GetExecutionErrorInfo(
                        &lErrorCode,
                        &bstrSource,
&bstrDescription,
&bstrHelpFile,
&lHelpContext,
&bstrIDofInterfaceWithError);

    _tprintf(_T("step %s failed\n"),
                        (TCHAR*)step->Name );

                    _tprintf(_T("error %d %s %s\n"),
                        lErrorCode,
                        (TCHAR*)bstrSource,
                        (TCHAR*)bstrDescription);
                }
                else
                {
                    _tprintf(_T("step %s succeeded\n"),
                        (TCHAR*)step->Name );
                }
            }
}
    }
    catch(_com_error pCE)
    {
        DisplayError(pCE);
    }
}

download ExecPkg.zip

goto top

###

Questions or problems regarding this web site should be directed to Web Master.
Copyright © 1991-2005 SQLDev.Net. All rights reserved.
Last modified: 04/06/05.