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.
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
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.
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)
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
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
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
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
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.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.
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.
http://msdn.microsoft.com/
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.
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
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.
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.
http://msdn.microsoft.com/
