Network DTS Scheduling

• Download source - 1.45 Kb

Introduction

All of us are familiar of DTS packages that come with SQL Server. Here we will describe how to execute DTS packages from Network. The packages are built on any sql server / client. The Server IP/Name, sql server user-id, and password will be required to access the system.

Next, clicking the view button we get all the DTS packages names listed on this server. On selecting any one and clicking on execute button the specified DTS packages are fired and executed on the server.

So, using this we can even execute a DTS packages from a network client, and execute DTS periodically. So, all the departmental stores where regular updating is required, and time to time required to execute the DTS packages, we can just add this one in scheduler, and benefit from avoiding manual work.

Hope this will benefit the readers.

Using the code

The details of DTS object, and its instance are here

// The DTS object instance is getting here, 
    Dim objDTSAppl      As DTS.Application
    Dim objPkgSQLServer As DTS.PackageSQLServer
    Dim colPkgInfo      As DTS.PackageInfos
    Dim objPkgInfo      As DTS.PackageInfo
    Dim strMsg          As String
    Dim iCount          As Integer
//

'Next call the GetPackageSQLServer() method, 
'and pass the sql-servername/ip, sqluserid, 
'and password. It will check the instance of the sql server, 
'and if found then check the user id/password 
'authentication on this server.
    
Set objDTSAppl = New DTS.Application
Set objPkgSQLServer = objDTSAppl.GetPackageSQLServer(
 Trim(txtServer.Text), Trim(txtUserID.Text), 
 Trim(txtPassword.Text), DTSSQLStgFlag_Default)
Set colPkgInfo = objPkgSQLServer.EnumPackageInfos("", True, "")
    
'After authentication it will read out all the
'DTs packages on this server, and add this in combo box.
 
  For Each objPkgInfo In colPkgInfo
        cmbDTSPackage.AddItem objPkgInfo.Name
        arrVersionID(iCount) = objPkgInfo.VersionID
        ReDim Preserve arrVersionID(UBound(arrVersionID) + 1)
        iCount = iCount + 1
  Next objPkgInfo

Points of Interest

Using DTS.Application, DTS.PackageSQLServer and DTS classess, we even can monitor, and use DTS scheduling jobs in offline applications or web based forms.