Click here to Skip to main content
15,867,834 members
Articles / Database Development / SQL Server
Article

Data Transformation Services Package Progress Dialog

Rate me:
Please Sign up or sign in to vote.
4.67/5 (5 votes)
3 Dec 20012 min read 75.4K   2K   28   5
Show a progress dialog when you execute your DTS Package at runtime.

Sample Image - DTS.gif

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
United Kingdom United Kingdom
Was made redundant in early 2003 after 10 years in computer programming, since then started my own business (selling computer books on the net)
www.pricecutbook.co.uk


Comments and Discussions

 
GeneralOrdering the display of the DTS package steps Pin
RAClarke15-Dec-05 4:29
RAClarke15-Dec-05 4:29 
GeneralRe: Ordering the display of the DTS package steps Pin
RAClarke15-Dec-05 4:44
RAClarke15-Dec-05 4:44 
Generalcompiler error Pin
Member 15833620-May-04 8:30
Member 15833620-May-04 8:30 
GeneralRe: compiler error Pin
RAClarke9-Dec-05 13:21
RAClarke9-Dec-05 13:21 
Generalvery excellent Pin
bsd91117-Dec-02 14:41
bsd91117-Dec-02 14:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.