Package class:
Option Strict Off
Option Explicit On
Imports DTS
Imports System.Data
Imports System.Data.SqlClient
Imports System.Diagnostics
Class Package
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private _blogID As String
Private _packageName As String
Private _sourceDB As String
Private _destinationDB As String
Private _sourceServer As String
Private _destinationServer As String
Private _postAuthor As String
Private _userID As String
Private _sectionID As String
Private _settingsID As String
Private _doExecute As Boolean
Public Sub BuildPackage(ByVal u As User, ByVal blogID As String)
' common settings
_packageName = Config.PackageName & " package for .Text BlogID " & blogID
_sourceDB = Config.SourceDB
_destinationDB = Config.DestinationDB
_sourceServer = Config.SourceServer
_destinationServer = Config.DestinationServer
_doExecute = Config.DoExecute
_blogID = blogID
_userID = u.UserID
_sectionID = u.SectionID
_settingsID = u.SettingsID
_postAuthor = u.PostAuthor
goPackage = goPackageOld
goPackage.Name = _packageName
goPackage.WriteCompletionStatusToNTEventLog = False
' goPackage.LogFileName = "blah"
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 1
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 256
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0
'---------------------------------------------------------------------------
' create package connection information
'---------------------------------------------------------------------------
Dim oConnection As DTS.Connection2
'------------- a new connection defined below.
'For security purposes, the password is never scripted
oConnection = goPackage.Connections.New("SQLOLEDB")
oConnection.ConnectionProperties.Item("Integrated Security").Value = "SSPI"
oConnection.ConnectionProperties.Item("Persist Security Info").Value = True
oConnection.ConnectionProperties.Item("Initial Catalog").Value = _destinationDB
oConnection.ConnectionProperties.Item("Data Source").Value = _destinationServer
oConnection.ConnectionProperties.Item("Application Name").Value = "DTS Designer"
oConnection.Name = "CommunityServer"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = _destinationServer
oConnection.ConnectionTimeout = 60
oConnection.Catalog = _destinationDB
oConnection.UseTrustedConnection = Config.UseIntegratedSecurity
oConnection.UseDSL = False
oConnection.UserID = Config.DestinationSqlUsername
'If you have a password for this connection, please uncomment and add your password below.
oConnection.Password = Config.DestinationSqlPassword
goPackage.Connections.Add(oConnection)
oConnection = Nothing
'------------- a new connection defined below.
'For security purposes, the password is never scripted
oConnection = goPackage.Connections.New("SQLOLEDB")
oConnection.ConnectionProperties.Item("Integrated Security").Value = "SSPI"
oConnection.ConnectionProperties.Item("Persist Security Info").Value = True
oConnection.ConnectionProperties.Item("Initial Catalog").Value = _sourceDB
oConnection.ConnectionProperties.Item("Data Source").Value = _sourceServer
oConnection.ConnectionProperties.Item("Application Name").Value = "DTS Designer"
oConnection.Name = "DotText"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = _sourceServer
oConnection.ConnectionTimeout = 60
oConnection.Catalog = _sourceDB
oConnection.UseTrustedConnection = Config.UseIntegratedSecurity
oConnection.UseDSL = False
oConnection.UserID = Config.SourceSqlUsername
'If you have a password for this connection, please uncomment and add your password below.
oConnection.Password = Config.SourceSqlPassword
goPackage.Connections.Add(oConnection)
oConnection = Nothing
'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------
Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint
'------------- a new step defined below
oStep = goPackage.Steps.New
oStep.Name = "DTSStep_DTSDataPumpTask_1"
oStep.Description = "blog_Content to cs_posts"
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 = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add(oStep)
oStep = Nothing
'------------- a new step defined below
oStep = goPackage.Steps.New
oStep.Name = "DTSStep_DTSDataPumpTask_2"
oStep.Description = "blog_Content to cs_thread"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSDataPumpTask_2"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add(oStep)
oStep = Nothing
'------------- a new step defined below
oStep = goPackage.Steps.New
' TESTING TESTING TESTING
oStep.Name = "DTSStep_DTSDataPumpTask_3"
oStep.Description = "blog_Content to cs_weblog_posts"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSDataPumpTask_3"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add(oStep)
oStep = Nothing
'------------- a new step defined below
oStep = goPackage.Steps.New
oStep.Name = "DTSStep_DTSDataPumpTask_4"
oStep.Description = "blog_LinkCategories"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSDataPumpTask_4"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add(oStep)
oStep = Nothing
'------------- a new step defined below
oStep = goPackage.Steps.New
oStep.Name = "DTSStep_DTSDataPumpTask_5"
oStep.Description = "blog_LinkCategories to cs_postcategories"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSDataPumpTask_5"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add(oStep)
oStep = Nothing
'------------- a new step defined below
oStep = goPackage.Steps.New
oStep.Name = "DTSStep_DTSDataPumpTask_6"
oStep.Description = "blog_Links to cs_links"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSDataPumpTask_6"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add(oStep)
oStep = Nothing
'------------- a new step defined below
oStep = goPackage.Steps.New
oStep.Name = "DTSStep_DTSDataPumpTask_7"
oStep.Description = "blog_Urls to cs_urls"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSDataPumpTask_7"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add(oStep)
oStep = Nothing
'------------- a new step defined below
oStep = goPackage.Steps.New
oStep.Name = "DTSStep_DTSDataPumpTask_8"
oStep.Description = "blog_referrals to cs_referrals"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSDataPumpTask_8"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add(oStep)
oStep = Nothing
'------------- a new step defined below
oStep = goPackage.Steps.New
oStep.Name = "DTSStep_DTSDataPumpTask_9"
oStep.Description = "blog_Links to cs_postCategories"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSDataPumpTask_9"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add(oStep)
oStep = Nothing
'------------- a precedence constraint for steps defined below
oStep = goPackage.Steps.Item("DTSStep_DTSDataPumpTask_2")
oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSDataPumpTask_1")
oPrecConstraint.StepName = "DTSStep_DTSDataPumpTask_1"
oPrecConstraint.PrecedenceBasis = 1
oPrecConstraint.Value = 0
oStep.PrecedenceConstraints.Add(oPrecConstraint)
oPrecConstraint = Nothing
'------------- a precedence constraint for steps defined below
oStep = goPackage.Steps.Item("DTSStep_DTSDataPumpTask_3")
oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSDataPumpTask_2")
oPrecConstraint.StepName = "DTSStep_DTSDataPumpTask_2"
oPrecConstraint.PrecedenceBasis = 1
oPrecConstraint.Value = 0
oStep.PrecedenceConstraints.Add(oPrecConstraint)
oPrecConstraint = Nothing
'------------- a precedence constraint for steps defined below
' TESTING TESTING TESTING
oStep = goPackage.Steps.Item("DTSStep_DTSDataPumpTask_4")
oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSDataPumpTask_3")
oPrecConstraint.StepName = "DTSStep_DTSDataPumpTask_3"
oPrecConstraint.PrecedenceBasis = 1
oPrecConstraint.Value = 0
oStep.PrecedenceConstraints.Add(oPrecConstraint)
oPrecConstraint = Nothing
'------------- a precedence constraint for steps defined below
oStep = goPackage.Steps.Item("DTSStep_DTSDataPumpTask_5")
oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSDataPumpTask_4")
oPrecConstraint.StepName = "DTSStep_DTSDataPumpTask_4"
oPrecConstraint.PrecedenceBasis = 1
oPrecConstraint.Value = 0
oStep.PrecedenceConstraints.Add(oPrecConstraint)
oPrecConstraint = Nothing
'------------- a precedence constraint for steps defined below
oStep = goPackage.Steps.Item("DTSStep_DTSDataPumpTask_6")
oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSDataPumpTask_5")
oPrecConstraint.StepName = "DTSStep_DTSDataPumpTask_5"
oPrecConstraint.PrecedenceBasis = 1
oPrecConstraint.Value = 0
oStep.PrecedenceConstraints.Add(oPrecConstraint)
oPrecConstraint = Nothing
'------------- a precedence constraint for steps defined below
oStep = goPackage.Steps.Item("DTSStep_DTSDataPumpTask_7")
oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSDataPumpTask_6")
oPrecConstraint.StepName = "DTSStep_DTSDataPumpTask_6"
oPrecConstraint.PrecedenceBasis = 1
oPrecConstraint.Value = 0
oStep.PrecedenceConstraints.Add(oPrecConstraint)
oPrecConstraint = Nothing
'------------- a precedence constraint for steps defined below
oStep = goPackage.Steps.Item("DTSStep_DTSDataPumpTask_8")
oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSDataPumpTask_7")
oPrecConstraint.StepName = "DTSStep_DTSDataPumpTask_7"
oPrecConstraint.PrecedenceBasis = 0
oPrecConstraint.Value = 4
oStep.PrecedenceConstraints.Add(oPrecConstraint)
oPrecConstraint = Nothing
'------------- a precedence constraint for steps defined below
oStep = goPackage.Steps.Item("DTSStep_DTSDataPumpTask_9")
oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSDataPumpTask_8")
oPrecConstraint.StepName = "DTSStep_DTSDataPumpTask_8"