Download source code - 38.8 Kb

Executive Summary

This paper targets software developers familiar in Microsoft DTS packages. It gives a basic overview of DTS packages and the need for exporting DTS packages, with a sample scenario. With this a sample tool, can be used for the developers to export DTS packages from one SQL Server to another and thus saving the time and productivity.

Introduction

In today's business world, an organization consists of so many applications with different sizes. Even though the business consists of many applications, it is must that the data needs to be accessed in a centralized area. That is the organization needs to centralize the data between the applications or servers. Different vendors have different tools / applications to transfer the data between these servers. Microsoft’s MSSQL Server uses MS Data Transformation Services (DTS) to does this functionality.

DTS (Data Transformation Services) is a set of tools to extract transform and consolidate data from discrete sources into a single / multiple destinations. DTS uses an object model named DTS packages to transfer the data. DTS package is a collection of objects like task, step, connection etc., and these can be stored as objects in SQL Server. Even though packages are stored in the server, in the export scenario [see sample scenario mentioned in Section2], it is slightly different than the other objects like table, views etc. That is the DTS packages can not be directly exported from one SQL Server to another. This paper provides the information about different approaches to export these packages from one SQL Server to another SQL Server

Need for Exporting DTS packages

In software industry, from software development to software implementation, the cycle consists of so many layers like one set for development and another for testing etc.,

(ie. like Development Server, Testing server and Production Server). The data that points in a DTS package in development environment may not be same as in the test environment and will be different in the production.

The following sample scenario explains this with little brief.

Sample scenario

Assume that an application is designed for 10 countries and each country has 6 packages according to the business logic. So the application contains totally 60 (10 X 6 = 60) packages. Assume each package pumps data from an Data server X, Data server Y to a centralized Server.

During the development stage these packages were developed in DEVELOPMENT _SERVER and the Data Sources will be pointing to Development Environment. During the Testing stage these packages need to be moved to TESTING_SERVER and the Data Sources should point to the respective servers. And while the implementation, these packages are need to be moved to PRODUCTION_SERVER.

Fig.1. Need for export DTS packages

So, the DTS Packages designed are need to be moved to Testing Environment and Production Environment. As mentioned earlier these packages can not be moved like other SQL Server objects (like tables, views, stored procedures, etc). The movement of these packages will be explained with in the further section of this paper with the same sample scenario.

The common way to move these packages from server to another is, open each and every package and use the ‘Save As’ option and save the package in the target server. But the according to the sample scenario the application contains around 60 packages and according to our sample application, these packages need to be moved initially to TESTING_SERVER server and the later PRODUCTION_SERVER server. So literally 120 (60 X 2 = 120) packages needs to be exported.

Opening each packages and ‘Save As’ to the target server is a hectic approach and it is time consuming way. But this approach is the best approach if the application contains very few DTS packages. So, as a better way to export all the packages from one server to another server, the second approach DTS package with SYSDTSPACKAGES can be considered.

DTS Package with SYSDTSPACKAGES Object

SQL Server stores all the DTS packages into msdb.dbo.sysdtspackages object. Exporting this object from one server to another is, simple as exporting the packages from one server to another. Exporting these packages or msdb.dbo.sysdtspackages table can be done through a simple DTS package that pumps the msdb.dbo.sysdtspackages of the source server to msdb.dbo.sysdtspackages of the target server. This approach is very effective and pumps all the packages from one server to another in a quick and efficient way.

The following section of this paper gives the code to build a DTS package programmatically with a task and step object to export these packages. The complete functionality is designed as a tool with this and can be downloaded with this paper and the developer can modify the tool according to the business requirement.

(Note: This tool has been developed in Visual Basic 6.0 and the type libraries includes are Microsoft ADO 2.5,Micrososft DTS Package Object Library,
Microsoft DTS custom Tasks Object Library and
Micrososft DTS Data PumpScripting Object Library)

'***********************************************************************
'Populate DTS Packages
'This subroutine is used to fetch the latest version of DTS packages from
'the SQL Server
'***********************************************************************

Public Sub populateSysDTS()

    Dim    SQL    As String
    Dim    ObjCon As New ADODB.Connection
    Dim    ObjRs As New ADODB.Recordset
    On Error GoTo ErrHandler:
    
    ObjCon.Open "Provider=SQLOLEDB;SERVER=" & gsSrcServerName & ";UID=" &_
                 gsSrcUserId & ";PWD=" & gsSrcPwd

    SQL = "SELECT A.name FROM msdb.dbo.sysdtspackages AS A INNER JOIN " &_
    "(SELECT B.[name] , B.[id], B.[createdate] " & _
    " FROM msdb.dbo.sysdtspackages B " & _
    " GROUP    BY B.[name], B.[id], B.[createdate] HAVING B.[createdate]" &_
    " IN (SELECT TOP 1 C.[createdate]" & _
    " FROM msdb.dbo.sysdtspackages C Where B.[ID] = C.[ID] " & _
    " ORDER    BY C.[createdate] DESC)    ) AS B ON A.[id] = B.[id] " &_
    "AND A.[createdate] = B.[createdate]"

    ObjRs.Open SQL,    ObjCon,    1, 3

    Erase sPackageNames
    ReDim Preserve sPackageNames(0)

    Do Until ObjRs.EOF
        sPackageNames(UBound(sPackageNames)) = ObjRs(0)
        ReDim Preserve sPackageNames(UBound(sPackageNames) + 1)
        ObjRs.MoveNext
    Loop

    ObjRs.Close
    ObjCon.Close

    Set ObjRs = Nothing
    Set ObjCon = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Number & vbCrLf & Err.Description, vbInformation, "DTS Export"
    End

End Sub

'***********************************************************************
'Create    DTS    Task
'This subroutine is    used to    create a DTS task object.
'***********************************************************************
Public Sub CreateTask(ByVal    ObjCustomTask As Object)
    Dim    oTransformation    As DTS.Transformation2
    Dim    oTransProps    As DTS.Properties
    Dim    oColumn    As DTS.Column
    Set oTransformation = _
        ObjCustomTask.Transformations.New("DTS.DataPumpTransformCopy")
    oTransformation.Name = "DTS_Export DataPump"
    oTransformation.TransformFlags = 63
    oTransformation.ForceSourceBlobsBuffered = 0
    oTransformation.ForceBlobsInMemory = False
    oTransformation.InMemoryBlobSize = 1048576
    oTransformation.TransformPhases = 4
    ObjCustomTask.Transformations.Add oTransformation
    Set oTransformation = Nothing
End Sub

'***********************************************************************
'Create DTS Connection
'This function will create a DTS Connection Object and will return the same.
'***********************************************************************

Public Function    MakeConnection
(
    ByRef lsStrConnectionName As String, 
    lsStrAppName As    String,    
    ByRef lsConnectionId As    Integer, 
    ByRef lsStrDataSource As String, 
    ByRef lsStrDatabase    As String, 
    ByRef lsStrUserId As String, 
    ByRef lsStrPassword    As String
) As Connection

    Set ObjConnection = objPackage.Connections.New("SQLOLEDB")
    ObjConnection.Name = lsStrConnectionName
    ObjConnection.ConnectionProperties("Persist    Security Info") = True
    ObjConnection.ConnectionProperties("Data Source") = lsStrDataSource
    ObjConnection.ConnectionProperties("Initial    Catalog") = lsStrDatabase
    ObjConnection.ConnectionProperties("User ID") = lsStrUserId
    ObjConnection.Password = lsStrPassword
    ObjConnection.ConnectionProperties("Application    Name") = lsStrAppName
    ObjConnection.ID = lsConnectionId
    ObjConnection.ConnectionTimeout = 60
    ObjConnection.UseTrustedConnection = False
    ObjConnection.UseDSL = False

    Set MakeConnection = ObjConnection
    Set ObjConnection = Nothing
End    Function

'***********************************************************************
'Create DTS Step
'This function will create a DTS Step Object and will return the same.
'***********************************************************************

Public Function    CreateStep(ByVal loObjPack As Object) As Step
    Dim    ObjStep    As DTS.Step2
    Dim    oPrecConstraint    As DTS.PrecedenceConstraint
    Set ObjStep = loObjPack.Steps.New
    ObjStep.Name = "DTS_Export_Step"
    ObjStep.Description = "DTS-Export Step"
    ObjStep.ExecutionStatus = 1
    ObjStep.TaskName = "DTS_Export_DataPump"
    ObjStep.CommitSuccess = False
    ObjStep.RollbackFailure = False
    ObjStep.CloseConnection = False
    ObjStep.IsPackageDSORowset = False
    ObjStep.JoinTransactionIfPresent = False
    ObjStep.DisableStep = False
    ObjStep.FailPackageOnError = False
    Set CreateStep = ObjStep
End    Function

'***********************************************************************
'Sub routine used to export DTS Objects
'***********************************************************************

Private    Sub    exportPackage()
    Dim    lsPackages As String
    Dim    clsServer As New DTS_Export
    Dim    ObjTask    As DTS.Task
    Dim    ObjCustomTask As DTS.DataPumpTask2
    Dim    lsSourceSQLStatement As    String

    lsPackages = ""
    If lstTgtServer.ListCount > 0 Then
        If MsgBox("Press OK    to Export this packages", _
				vbInformation+ vbOKCancel, _
                 "DTS-Export") = vbOK Then
            ctrlPB.Max = lstTgtServer.ListCount + 10
            ctrlPB.Visible = True
            Screen.MousePointer = vbHourglass
            For    iindex = 0 To lstTgtServer.ListCount - 1
                ctrlPB.Value = iindex + 1
                
                If lsPackages = "" Then
                    lsPackages = "'" & lstTgtServer.List(iindex) & "'"
                Else
                    lsPackages = lsPackages & ",'" & lstTgtServer.List(iindex) & "'"
                End If
            Next

            objPackage.Name = "DTS Export Package"
            objPackage.WriteCompletionStatusToNTEventLog = False
            objPackage.FailOnError = False
            objPackage.PackagePriorityClass = 2
            objPackage.MaxConcurrentSteps = 4
            objPackage.LineageOptions = 0
            objPackage.UseTransaction = True
            objPackage.TransactionIsolationLevel = 4096
            objPackage.AutoCommitTransaction = True
            objPackage.RepositoryMetadataOptions = 0
            objPackage.UseOLEDBServiceComponents = True
            objPackage.LogToSQLServer = False
            objPackage.LogServerFlags = 0
            objPackage.FailPackageOnLogFailure = False
            objPackage.ExplicitGlobalVariables = False
            objPackage.PackageType = 0

            Set ObjConnection_1 =_
				clsServer.MakeConnection("DTS-Export Source Server", "DTS_Exprt",1, 
            gsSrcServerName, "msdb", gsSrcUserId, gsSrcPwd)
            objPackage.Connections.Add ObjConnection_1

            Set ObjConnection_1 = _
				clsServer.MakeConnection("DTS-Export Target Server", "DTS_Exprt",2, 
            gsTgtServerName, "msdb", gsTgtUserId, gsTgtPwd)

            objPackage.Connections.Add ObjConnection_1

            Set ObjTask = objPackage.Tasks.New("DTSDataPumpTask")
            Set ObjCustomTask = ObjTask.CustomTask
            ObjCustomTask.Name = "DTS_Export_DataPump"
            ObjCustomTask.Description = "Tranfer packages"
            ObjCustomTask.SourceConnectionID = 1
    
            lsSourceSQLStatement = " SELECT    a.name,a.id,a.versionid," &_
            "a.description,a.categoryid, " &_
            "a.createdate,a.owner,a.packagedata,a.owner_sid FROM" &_
            "msdb.dbo.sysdtspackages    AS A " &_
            "INNER JOIN    (SELECT    B.[name] , B.[id], B.[createdate]" & _
            " FROM msdb.dbo.sysdtspackages B lsSourceSQLStatement = " &_
            "lsSourceSQLStatement"    &_
            " WHERE NAME IN("    & lsPackages & ")" & _
            " GROUP    BY B.[name], B.[id], B.[createdate]" &_
            "HAVING B.[createdate] IN (SELECT TOP 1 C.[createdate]" & _
            " FROM msdb.dbo.sysdtspackages C Where B.[ID] = C.[ID]"    & _
            " ORDER    BY C.[createdate] DESC)    ) AS B ON A.[id] = " &_
            "B.[id] AND A.[createdate] = B.[createdate]"

            ObjCustomTask.SourceSQLStatement = lsSourceSQLStatement
            ObjCustomTask.DestinationConnectionID = 2
            ObjCustomTask.DestinationObjectName = "msdb.dbo.sysdtspackages"
            ObjCustomTask.ProgressRowCount = 1000
            ObjCustomTask.MaximumErrorCount = 0
            ObjCustomTask.FetchBufferSize = 1
            ObjCustomTask.UseFastLoad = True
            ObjCustomTask.InsertCommitSize = 0
            ObjCustomTask.AllowIdentityInserts = False
            ObjCustomTask.FirstRow = "0"
            ObjCustomTask.LastRow = "0"
            ObjCustomTask.FastLoadOptions = 2
            ObjCustomTask.ExceptionFileOptions = 1
            ObjCustomTask.DataPumpOptions = 0
            Set ObjStep_1 = clsServer.CreateStep(objPackage)
            objPackage.Steps.Add ObjStep_1

            clsServer.CreateTask ObjCustomTask
            objPackage.Tasks.Add ObjTask
            objPackage.Execute
            'objPackage.SaveToSQLServer "TARGET_SERVER", "sa", "", DTSSQLStgFlag_Default
            objPackage.UnInitialize
            ctrlPB.Value = iindex +    10
            lstTgtServer.Clear
            Screen.MousePointer = vbDefault
        End If
    Else
        MsgBox "To Export DTS packages, at least one package must be selected.",_
                vbInformation, "DTS Export"
    End If
End Sub

The above set of code will invoke each package object and updates the connection attributes. Invoking each package is a little bad design, but even this can made to run in a separate thread. Once the execution of this code is made to run at different thread, the developer can start to work on other tasks. Otherwise the developer has to open each package to edit the attributes. Again this will be a hectic process for the developers and kills more time. So, the developers cab use the DTSPackage Object model to save time and increase productivity. By making this as an automated process the developer’s time and productivity can be saved.

Conclusion

This paper provided a basic overview of the DTS packages and the need to export DTS packages from one SQL Server to another SQL Server with a sample scenario. And this provides a tool to export the DTS packages and thus increases the productivity. Even this tool is specifically designed to export DTS packages it is advisable to use this only to export more numbers of DTS packages at a time. The developers can modify the code of this tool according to their requirement and to make more productivity.

References

http://msdn.microsoft.com/

DTS Package Object model

As mentioned by the above way, the DTS packages have been exported quickly. Now the developer can see those packages in the target server. But using this sysdtspackage object model will not export any package related information like connection attributes, user id etc,

As per the sample scenario, even though all these packages have been exported, still it will be pointing to the old connection attributes. (If it has exported to Production environment from test, then the connections still will be pointing to TEST servers.) To change the attributes of the DTS packages, Microsoft’s provides DTS Package Object model where the developer can access the individual DTS package objects and can modify the settings programmatically. The following set of code is used to update the connection settings.

'**********************************************************************
'Section used to update the DTS Connection settings
'***********************************************************************

objPackage.LoadFromSQLServer gsSrcServerName, gsSrcUserId, gsSrcPwd,_
                            DTSSQLStgFlag_Default, , , , lsPackageName
Set cns = objPackage.Connections
For Each cn In cns
	If UCase(cn.DataSource) = UCase(gsUpdateSRCServer) And _
	   UCase(cn.UserID) = UCase(gsUpdateSRCUserId) Then 'And cn.Password = gsUpdateSRCPwd Then
		cn.DataSource = gsUpdateTGTServer
		cn.UserID = gsUpdateTGTUserId
		cn.Password = gsUpdateTGTPwd
		objPackage.SaveToSQLServer gsSrcServerName, gsSrcUserId, gsSrcPwd, DTSSQLStgFlag_Default
	End If
Next

Set objPackage = Nothing

The above set of code will invoke each package object and updates the connection attributes. Invoking each package is a little bad design, but even this can made to run in a separate thread. Once the execution of this code is made to run at different thread, the developer can start to work on other tasks. Otherwise the developer has to open each package to edit the attributes. Again this will be a hectic process for the developers and kills more time. So, the developers cab use the DTSPackage Object model to save time and increase productivity. By making this as an automated process the developer’s time and productivity can be saved.

Conclusion

This paper provided a basic overview of the DTS packages and the need to export DTS packages from one SQL Server to another SQL Server with a sample scenario. And this provides a tool to export the DTS packages and thus increases the productivity. Even this tool is specifically designed to export DTS packages it is advisable to use this only to export more numbers of DTS packages at a time. The developers can modify the code of this tool according to their requirement and to make more productivity.

References

http://msdn.microsoft.com/