Data Transformation Services (DTS) Package Example
The following package transfers the selected columns from the authors table in pubs database into an excel spreadsheet. To give you an idea of how powerful DTS capabilities are, this package is presented in VB code, although it was built through a package designer within SQL Server. It took a minute to generate this code with the package designer. If there was a need to customize the transformations the VB code, most of which has already been written could be edited:
代码
'first define package properties and some objects:
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
Set goPackage = goPackageOld
goPackage.Name = "New Package"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0
'next define connections to the data source and destination
Dim oConnection As DTS.Connection2
'connection to SQL Server
Set oConnection = goPackage.Connections.New("SQLOLEDB")
oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("User ID") = "sa"
oConnection.ConnectionProperties("Initial Catalog") = "pubs"
oConnection.ConnectionProperties("Data Source") = "BP-J1\JPGR"
oConnection.ConnectionProperties("Application Name") = "DTS Designer"
oConnection.Name = "Microsoft OLE DB Provider for SQL Server"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "BP-J1\JPGR"
oConnection.UserID = "sa"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "pubs"
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
'oConnection.Password = "<put here password the>"
goPackage.Connections.Add oConnection
Set oConnection = Nothing
'connection to XL
Set oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0")
oConnection.ConnectionProperties("User ID") = "Admin"
oConnection.ConnectionProperties("Data Source") = "e:\pubs.xls"
oConnection.ConnectionProperties("Extended Properties") = "Excel 8.0;HDR=YES;"
oConnection.Name = "Microsoft Excel 97-2000"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "e:\pubs.xls"
oConnection.UserID = "Admin"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
goPackage.Connections.Add oConnection
Set oConnection = Nothing
'next create steps for the package
Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint
Set oStep = goPackage.Steps.New
oStep.Name = "DTSStep_DTSDataPumpTask_1"
oStep.Description = "Transform Data Task: undefined"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSDataPumpTask_1"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = True
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add oStep
Set oStep = Nothing
'invoke the transformation task
Call Task_Sub1(goPackage)
'execute the package
goPackage.Execute
goPackage.Uninitialize
Set goPackage = Nothing
Set goPackageOld = Nothing
End Sub
'next define the tasks for the package. Each column is transferred with
'the same task but a different transformation:
Public Sub Task_Sub1(ByVal goPackage As Object)
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "DTSTask_DTSDataPumpTask_1"
oCustomTask1.Description = "Transform Data Task: undefined"
oCustomTask1.SourceConnectionID = 1
oCustomTask1.SourceObjectName = "[pubs].[dbo].[authors]"
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName = "authors"
oCustomTask1.ProgressRowCount = 1000
oCustomTask1.MaximumErrorCount = 0
oCustomTask1.FetchBufferSize = 1
oCustomTask1.UseFastLoad = True
oCustomTask1.InsertCommitSize = 0
oCustomTask1.ExceptionFileColumnDelimiter = "|"
oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
oCustomTask1.AllowIdentityInserts = False
oCustomTask1.FirstRow = "0"
oCustomTask1.LastRow = "0"
oCustomTask1.FastLoadOptions = 2
oCustomTask1.ExceptionFileOptions = 1
oCustomTask1.DataPumpOptions = 0
Call oCustomTask1_Trans_Sub1(oCustomTask1)
Call oCustomTask1_Trans_Sub2(oCustomTask1)
Call oCustomTask1_Trans_Sub3(oCustomTask1)
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
End Sub
Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)
'transformation for the au_lname column:
Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
oTransformation.Name = "DTSTransformation__2"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
Set oColumn = oTransformation.SourceColumns.New("au_lname", 1)
oColumn.Name = "au_lname"
oColumn.Ordinal = 1
oColumn.Flags = 8
oColumn.Size = 40
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("au_lname", 1)
oColumn.Name = "au_lname"
oColumn.Ordinal = 1
oColumn.Flags = 102
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oTransProps = oTransformation.TransformServerProperties
Set oTransProps = Nothing
oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing
End Sub
Public Sub oCustomTask1_Trans_Sub2(ByVal oCustomTask1 As Object)
'transformation for au_fname column:
Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
oTransformation.Name = "DTSTransformation__3"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
Set oColumn = oTransformation.SourceColumns.New("au_fname", 1)
oColumn.Name = "au_fname"
oColumn.Ordinal = 1
oColumn.Flags = 8
oColumn.Size = 20
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("au_fname", 1)
oColumn.Name = "au_fname"
oColumn.Ordinal = 1
oColumn.Flags = 102
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oTransProps = oTransformation.TransformServerProperties
Set oTransProps = Nothing
oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing
End Sub
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
Set goPackage = goPackageOld
goPackage.Name = "New Package"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0
'next define connections to the data source and destination
Dim oConnection As DTS.Connection2
'connection to SQL Server
Set oConnection = goPackage.Connections.New("SQLOLEDB")
oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("User ID") = "sa"
oConnection.ConnectionProperties("Initial Catalog") = "pubs"
oConnection.ConnectionProperties("Data Source") = "BP-J1\JPGR"
oConnection.ConnectionProperties("Application Name") = "DTS Designer"
oConnection.Name = "Microsoft OLE DB Provider for SQL Server"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "BP-J1\JPGR"
oConnection.UserID = "sa"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "pubs"
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
'oConnection.Password = "<put here password the>"
goPackage.Connections.Add oConnection
Set oConnection = Nothing
'connection to XL
Set oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0")
oConnection.ConnectionProperties("User ID") = "Admin"
oConnection.ConnectionProperties("Data Source") = "e:\pubs.xls"
oConnection.ConnectionProperties("Extended Properties") = "Excel 8.0;HDR=YES;"
oConnection.Name = "Microsoft Excel 97-2000"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "e:\pubs.xls"
oConnection.UserID = "Admin"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
goPackage.Connections.Add oConnection
Set oConnection = Nothing
'next create steps for the package
Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint
Set oStep = goPackage.Steps.New
oStep.Name = "DTSStep_DTSDataPumpTask_1"
oStep.Description = "Transform Data Task: undefined"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSDataPumpTask_1"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = True
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add oStep
Set oStep = Nothing
'invoke the transformation task
Call Task_Sub1(goPackage)
'execute the package
goPackage.Execute
goPackage.Uninitialize
Set goPackage = Nothing
Set goPackageOld = Nothing
End Sub
'next define the tasks for the package. Each column is transferred with
'the same task but a different transformation:
Public Sub Task_Sub1(ByVal goPackage As Object)
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "DTSTask_DTSDataPumpTask_1"
oCustomTask1.Description = "Transform Data Task: undefined"
oCustomTask1.SourceConnectionID = 1
oCustomTask1.SourceObjectName = "[pubs].[dbo].[authors]"
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName = "authors"
oCustomTask1.ProgressRowCount = 1000
oCustomTask1.MaximumErrorCount = 0
oCustomTask1.FetchBufferSize = 1
oCustomTask1.UseFastLoad = True
oCustomTask1.InsertCommitSize = 0
oCustomTask1.ExceptionFileColumnDelimiter = "|"
oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
oCustomTask1.AllowIdentityInserts = False
oCustomTask1.FirstRow = "0"
oCustomTask1.LastRow = "0"
oCustomTask1.FastLoadOptions = 2
oCustomTask1.ExceptionFileOptions = 1
oCustomTask1.DataPumpOptions = 0
Call oCustomTask1_Trans_Sub1(oCustomTask1)
Call oCustomTask1_Trans_Sub2(oCustomTask1)
Call oCustomTask1_Trans_Sub3(oCustomTask1)
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
End Sub
Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)
'transformation for the au_lname column:
Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
oTransformation.Name = "DTSTransformation__2"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
Set oColumn = oTransformation.SourceColumns.New("au_lname", 1)
oColumn.Name = "au_lname"
oColumn.Ordinal = 1
oColumn.Flags = 8
oColumn.Size = 40
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("au_lname", 1)
oColumn.Name = "au_lname"
oColumn.Ordinal = 1
oColumn.Flags = 102
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oTransProps = oTransformation.TransformServerProperties
Set oTransProps = Nothing
oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing
End Sub
Public Sub oCustomTask1_Trans_Sub2(ByVal oCustomTask1 As Object)
'transformation for au_fname column:
Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
oTransformation.Name = "DTSTransformation__3"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
Set oColumn = oTransformation.SourceColumns.New("au_fname", 1)
oColumn.Name = "au_fname"
oColumn.Ordinal = 1
oColumn.Flags = 8
oColumn.Size = 20
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("au_fname", 1)
oColumn.Name = "au_fname"
oColumn.Ordinal = 1
oColumn.Flags = 102
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oTransProps = oTransformation.TransformServerProperties
Set oTransProps = Nothing
oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing
End Sub
