A Visual Basic Procedure for Creating a Basic DTS Package

'The package is saved to SQL Server on the local machine

'The package is saved using the sa login with no password

'Change the parameters of the SaveToSQLServer method toward

' the end of the procedure if these choices are inappropriate

 

'This package contains the following objects:

' 1. Two connections - both to the Pubs database on the local

' SQL Server

' 2. Three steps

' 3. Two precedence constraints, so that the steps will occur

' in order.

' 4. Three tasks - two ExecuteSQL tasks and one DataPumpTask:

' The first ExecuteSQL task drops and creates the AuthorID table

' The second ExecuteSQL task deletes all records from AuthorID

' The DataPumpTask loads the AuthorID table from the au_id

'field of the Authors table.

' 5. One source field and one destination field in the DataPumpTask's transformation

' 6. A simple ActiveX script in the transformation.



'HOW TO USE THIS CODE:

' 1. Create a Visual Basic project

' 2. Paste this code into a Form with the default name, Form1

' 3. Add a reference to the Microsoft DTSPackage Object Library



Option Explicit

Private Sub Form_Load()

On Error GoTo ProcErr

Dim pkg As New DTS.Package

Dim con As DTS.Connection

Dim stp As DTS.Step

Dim tsk As DTS.Task

Dim cusSQL As DTS.ExecuteSQLTask

Dim cusData As DTS.DataPumpTask

Dim oleprop As DTS.OleDBProperty

Dim prc As DTS.PrecedenceConstraint

Dim tran As DTS.Transformation

Dim props As DTS.Properties

Dim col As DTS.Column

Dim txt As String


'Set Package name - Must be unique on the Server

pkg.Name = InputBox("Unique name for new package -", "Package Name")



'Create connections for the package

Set con = pkg.Connections.New("SQLOLEDB.1")

con.Name = "Pubs #1"

con.ID = 1

con.DataSource = "(local)"

con.Catalog = "pubs"

con.UseTrustedConnection = True

con.ConnectionProperties("Integrated Security") = "SSPI"

con.ConnectionProperties("Persist Security Info") = True

con.ConnectionProperties("Initial Catalog") = "pubs"

con.ConnectionProperties("Data Source") = "(local)"

pkg.Connections.Add con

Set con = Nothing


Set con = pkg.Connections.New("SQLOLEDB.1")

con.Name = "Pubs #2"

con.ID = 2

con.DataSource = "(local)"

con.Catalog = "pubs"

con.UseTrustedConnection = True

con.ConnectionProperties("Integrated Security") = "SSPI"

con.ConnectionProperties("Persist Security Info") = True

con.ConnectionProperties("Initial Catalog") = "pubs"

con.ConnectionProperties("Data Source") = "(local)"

pkg.Connections.Add con

Set con = Nothing


'Create the steps

Set stp = pkg.Steps.New

stp.Name = "DTSStep_DTSExecuteSQLTask_1"

stp.Description = "Create AuthorID Table"

stp.TaskName = "DTSTask_DTSExecuteSQLTask_1"

pkg.Steps.Add stp

Set stp = Nothing


Set stp = pkg.Steps.New

stp.Name = "DTSStep_DTSExecuteSQLTask_2"

stp.Description = "Delete AuthorID Table"

stp.TaskName = "DTSTask_DTSExecuteSQLTask_2"

pkg.Steps.Add stp

Set stp = Nothing


Set stp = pkg.Steps.New

stp.Name = "DTSStep_DTSDataPumpTask_1"

stp.Description = "Load AuthorID Table"

stp.TaskName = "DTSTask_DTSDataPumpTask_1"

pkg.Steps.Add stp

Set stp = Nothing


'Create Precedence Constraints for the steps

Set stp = pkg.Steps("DTSStep_DTSExecuteSQLTask_2")

Set prc = stp.PrecedenceConstraints.New("DTSStep_DTSExecuteSQLTask_1")

prc.StepName = "DTSStep_DTSExecuteSQLTask_1"

prc.PrecedenceBasis = DTSStepPrecedenceBasis_ExecStatus

prc.Value = DTSStepExecStat_Completed

stp.PrecedenceConstraints.Add prc

Set prc = Nothing


Set stp = pkg.Steps("DTSStep_DTSDataPumpTask_1")

Set prc = stp.PrecedenceConstraints.New("DTSStep_DTSExecuteSQLTask_2")

prc.StepName = "DTSStep_DTSExecuteSQLTask_2"

prc.PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult

prc.Value = DTSStepExecResult_Success

stp.PrecedenceConstraints.Add prc

Set prc = Nothing


'Create ExecuteSQL Tasks

Set tsk = pkg.Tasks.New("DTSExecuteSQLTask")

Set cusSQL = tsk.CustomTask

cusSQL.Name = "DTSTask_DTSExecuteSQLTask_1"

cusSQL.Description = "Create AuthorID Table"

cusSQL.SQLStatement = _

"if exists(select * from sysobjects where name = 'AuthorID')" & vbCrLf _

& " drop table AuthorID " & vbCrLf _

& "go" & vbCrLf & vbCrLf _

& "create table AuthorID (AuthorID varchar(20) null)"

cusSQL.ConnectionID = 1

pkg.Tasks.Add tsk

Set cusSQL = Nothing

Set tsk = Nothing


Set tsk = pkg.Tasks.New("DTSExecuteSQLTask")

Set cusSQL = tsk.CustomTask

cusSQL.Name = "DTSTask_DTSExecuteSQLTask_2"

cusSQL.Description = "Delete Author Table"

cusSQL.SQLStatement = "delete AuthorID"

cusSQL.ConnectionID = 1

pkg.Tasks.Add tsk

Set cusSQL = Nothing

Set tsk = Nothing


'Create DataPump Task

Set tsk = pkg.Tasks.New("DTSDataPumpTask")

Set cusData = tsk.CustomTask

cusData.Name = "DTSTask_DTSDataPumpTask_1"

cusData.Description = "Load AuthorID Table"

cusData.SourceConnectionID = 1

cusData.SourceSQLStatement = "select au_id from authors"

cusData.DestinationConnectionID = 2

cusData.DestinationObjectName = "[pubs].[dbo].[AuthorID]"


'Add transformations for DataPump task

Set tran = cusData.Transformations.New("DTS.DataPumpTransformScript.1")

tran.Name = "DTSTransformation__1"


'Add Source columns for transformation

Set col = tran.SourceColumns.New("au_id", 1)

col.Name = "au_id"

col.Ordinal = 1

tran.SourceColumns.Add col

Set col = Nothing


'Add Destination columns for transformation

Set col = tran.DestinationColumns.New("AuthorID", 1)

col.Name = "AuthorID"

col.Ordinal = 1

tran.DestinationColumns.Add col

Set col = Nothing


'Create the ActiveX script

txt = ""

txt = txt & "'Simple transformation of one field" & vbCrLf

txt = txt & "Option Explicit" & vbCrLf

txt = txt & vbCrLf

txt = txt & "Function Main" & vbCrLf

txt = txt & "" & vbCrLf

txt = txt & " DTSDestination(""AuthorID"") = DTSSource(""au_id"") & vbCrLf"

txt = txt & "" & vbCrLf

txt = txt & " Main = DTSTransformStat_OK" & vbCrLf

txt = txt & "" & vbCrLf

txt = txt & "End Function"


'Set the TransformServerProperties

Set props = tran.TransformServerProperties

props("Language") = "VBScript"

props("FunctionEntry") = "Main"

props("Text") = txt

Set props = Nothing


cusData.Transformations.Add tran

Set tran = Nothing


pkg.Tasks.Add tsk

Set cusData = Nothing

Set tsk = Nothing


'Save new package

pkg.SaveToSQLServer "(local)", "sa", ""

MsgBox pkg.Name & " created as local SQL Server DTS package."


Set pkg = Nothing

Unload Form1


ProcExit:

Exit Sub

ProcErr:

MsgBox Err.Number & " - " & Err.Description

End Sub