Data Transformation Services Package Progress Dialog

• Download source files - 64.3 Kb
• Download demo project - 36 Kb

Introduction

Microsoft� SQL Server� Data Transformation Services (DTS) is a set of graphical tools and programmable objects that lets you extract, transform, and consolidate data from various sources into single or multiple destinations. This article will not go into the fine details on what DTS is; if you want to find out more details on DTS, see Microsoft site.

When you execute the DTS package using the DTS editor via SQL Server Enterprise Manager, you get a nice dialog that shows the progress of each step in the DTS package. This nice progress dialog does not show up when you execute the package at runtime in your C++ code. In order to get the progress dialog at runtime, I have created the CDTSProgressThreadDlg.

The CDTSProgressThreadDlg mimics the progress dialog that is used to execute the DTS package in Enterprise Manager.

Note - You can use SQL NS to execute a DTS package that shows a progress dialog, but you need to redistribute SQL NS components.

Using DTS COM Objects

Use the following import statement to access DTS COM object, this should be done in your stdafx.h file.

#import "c:\mssql7\binn\resources\1033\dtspkg.rll"

Using the CDTSProgressThreadDlg

Due to the use of worker threads, the calling process is a bit untidy. The following code shows how a DTS Package is executed:

bOK = ProcessDTS(m_sPackageName, sCaption, "SQL Server", "FoxPro", 
      CDTSProgressThreadDlg::SQL_SERVER_ICON, 
      CDTSProgressThreadDlg::FOXPRO_ICON, bCancelled);

A helper function called ProcessDTS is called. This takes the DTS Package name, the caption that will appear on the progress dialog, the description of the source database, the description of the destination database, the icon to use for the source database, the icon to use for the destination database, and finally a bool is passed in to see if the package was cancelled.

If you look at the code for ProcessDTS, you will see that this function calls a static function called ExecuteDTSFunctionWithProgressDialog. (This type of execution has been based on PJ Naughter's thread based progress dialogs.) The ExecuteDTSFunctionWithProgressDialog uses the CDTSProgressThreadDlg.

bool CDTSDlg::ProcessDTS(const CString& sDTSName, const CString& sDTSTitle, 
                         const CString& sSourceDesc, const CString& sDestinationDesc, 
                         int nSourceIcon, int nDestinationIcon, bool& bCancelled) 
{
    USES_CONVERSION;

    DTS::_PackagePtr pDTSPackage;
    HRESULT hr;

    CInfo info;
    try
    {
        if(SUCCEEDED(hr = pDTSPackage.CreateInstance(__uuidof(DTS::Package))))
        {
            info.m_pPackage = pDTSPackage;
            info.m_sDTSName = sDTSName;
            info.m_bOK = true;
            info.m_bCancelled = false;
            info.m_bSaveDTSPackage = false;
            info.m_sSourceDescription = sSourceDesc;
            info.m_sDestinationDescription = sDestinationDesc;
            info.m_nSourceIcon = nSourceIcon;
            info.m_nDestinationIcan = nDestinationIcon;
            info.m_sServerName = m_sServerName;
            info.m_sUsername = m_sUsername;
            info.m_sPassword = m_sPassword;
            info.m_bUseTrusteConenction = m_bUseTrusteConenction;

            DWORD dwFlags = PSTAT_CONFIRMCANCEL;

            CString sCancelPrompt = 
                 "Are you sure you want to cancel ?\n\n"
                 "This will terminate when the connection to the "
                 "current task terminates";
            if(!ExecuteDTSFunctionWithProgressDialog(DoDTSProcess, 
                                      _T(sDTSTitle), &info, 
                                      dwFlags, _T(sCancelPrompt), 
                                      THREAD_PRIORITY_NORMAL, 
                                      this, pDTSPackage))
                AfxMessageBox("User cancelled transfer");
        }
        else
        {
            IUnknown* pIUnk = NULL;
            pDTSPackage->QueryInterface(IID_IUnknown, (LPVOID*)&pIUnk); 
            _com_issue_errorex(hr, pIUnk, __uuidof(DTS::Package));

        }pDTSPackage->UnInitialize();

    }
    catch(_com_error e)
    {
        AfxMessageBox(ReportError(e));
    }

    bCancelled = info.m_bCancelled;
    return info.m_bOK;
}

DTS Package Events (Connection Points)

The DTS::PackageEvents COM object provides all the information (events) about the status of the package when it is being executed. This COM object is used in the CDTSProgressThreadDlg. Once an event is fired by the DTS Execution procedure, this information is captured on the sink object (CPackageSink) and updated on the List control on the progress dialog.

Conclusion

This class can be easily used at runtime to execute a DTS package.

Reference and Credits

• PJ Naughter's Thread based Progress Dialog.
• Darren Green web site - The best site for more examples on DTS.