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 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.
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",
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
bool CDTSDlg::ProcessDTS(const CString& sDTSName, const CString& sDTSTitle,
const CString& sSourceDesc, const CString& sDestinationDesc,
int nSourceIcon, int nDestinationIcon, bool& bCancelled)
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";
AfxMessageBox("User cancelled transfer");
IUnknown* pIUnk = NULL;
_com_issue_errorex(hr, pIUnk, __uuidof(DTS::Package));
bCancelled = info.m_bCancelled;
DTS Package Events (Connection Points)
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.
This class can be easily used at runtime to execute a DTS package.
Reference and Credits