'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: C:\Documents and Settings\Administrator\My Documents\My Webs\Scripts\ActiveX\Backup History Delete Archive.bas.txt
'Package Name: Backup History Delete Archive
'Package Description: 
'Generated Date: 7/16/2004
'Generated Time: 11:42:01 AM
'****************************************************************

Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
	set goPackage = goPackageOld

	goPackage.Name = "Backup History Delete Archive"
	goPackage.WriteCompletionStatusToNTEventLog = False
	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 = True
	goPackage.LogServerName = "(local)"
	goPackage.LogServerFlags = 256
	goPackage.FailPackageOnLogFailure = False
	goPackage.ExplicitGlobalVariables = False
	goPackage.PackageType = 0
	

'---------------------------------------------------------------------------
' begin to write package global variables information
'---------------------------------------------------------------------------

	Dim oGlobal As DTS.GlobalVariable

	Set oGlobal = goPackage.GlobalVariables.New("SQL Server")
	oGlobal = ""

	goPackage.GlobalVariables.Add oGlobal
	set oGlobal = Nothing


'---------------------------------------------------------------------------
' create package connection information
'---------------------------------------------------------------------------

Dim oConnection as DTS.Connection2

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("SQLOLEDB")

	oConnection.ConnectionProperties("Integrated Security") = "SSPI"
	oConnection.ConnectionProperties("Persist Security Info") = True
	oConnection.ConnectionProperties("Initial Catalog") = "msdb"
	oConnection.ConnectionProperties("Data Source") = "SQ-DMP101"
	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 = "SQ-DMP101"
	oConnection.ConnectionTimeout = 60
	oConnection.Catalog = "msdb"
	oConnection.UseTrustedConnection = True
	oConnection.UseDSL = False
	
	'If you have a password for this connection, please uncomment and add your password below.
	'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------

Dim oStep as DTS.Step2
Dim oPrecConstraint as DTS.PrecedenceConstraint

'------------- a new step defined below

Set oStep = goPackage.Steps.New

	oStep.Name = "DTSStep_DTSExecuteSQLTask_1"
	oStep.Description = "maintain system backup tables"
	oStep.ExecutionStatus = 1
	oStep.TaskName = "DTSTask_DTSExecuteSQLTask_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
Set oStep = Nothing

'------------- a new step defined below

Set oStep = goPackage.Steps.New

	oStep.Name = "DTSStep_DTSDynamicPropertiesTask_1"
	oStep.Description = "Set Server Name"
	oStep.ExecutionStatus = 1
	oStep.TaskName = "DTSTask_DTSDynamicPropertiesTask_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
Set oStep = Nothing

'------------- a precedence constraint for steps defined below

Set oStep = goPackage.Steps("DTSStep_DTSExecuteSQLTask_1")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSDynamicPropertiesTask_1")
	oPrecConstraint.StepName = "DTSStep_DTSDynamicPropertiesTask_1"
	oPrecConstraint.PrecedenceBasis = 1
	oPrecConstraint.Value = 0
	
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------

'------------- call Task_Sub1 for task DTSTask_DTSExecuteSQLTask_1 (maintain system backup tables)
Call Task_Sub1( goPackage	)

'------------- call Task_Sub2 for task DTSTask_DTSDynamicPropertiesTask_1 (Set Server Name)
Call Task_Sub2( goPackage	)

'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------

'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
tracePackageError goPackage
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing package lines above and uncomment the saving package line
set goPackage = Nothing

set goPackageOld = Nothing

End Sub


'-----------------------------------------------------------------------------
' error reporting using step.GetExecutionErrorInfo after execution
'-----------------------------------------------------------------------------
Public Sub tracePackageError(oPackage As DTS.Package)
Dim ErrorCode As Long
Dim ErrorSource As String
Dim ErrorDescription As String
Dim ErrorHelpFile As String
Dim ErrorHelpContext As Long
Dim ErrorIDofInterfaceWithError As String
Dim i As Integer

	For i = 1 To oPackage.Steps.Count
		If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
			oPackage.Steps(i).GetExecutionErrorInfo ErrorCode, ErrorSource, ErrorDescription, _
					ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError
			MsgBox oPackage.Steps(i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescription
		End If
	Next i

End Sub

'------------- define Task_Sub1 for task DTSTask_DTSExecuteSQLTask_1 (maintain system backup tables)
Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.ExecuteSQLTask2
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
Set oCustomTask1 = oTask.CustomTask

	oCustomTask1.Name = "DTSTask_DTSExecuteSQLTask_1"
	oCustomTask1.Description = "maintain system backup tables"
	oCustomTask1.SQLStatement = "SET DEADLOCK_PRIORITY LOW" & vbCrLf
	oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "declare @OlderThan90Days datetime" & vbCrLf
	oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "set @OlderThan90Days = getdate() - 90" & vbCrLf
	oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "exec msdb.dbo.sp_delete_backuphistory @OlderThan90Days"
	oCustomTask1.ConnectionID = 1
	oCustomTask1.CommandTimeout = 0
	oCustomTask1.OutputAsRecordset = False
	
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing

End Sub

'------------- define Task_Sub2 for task DTSTask_DTSDynamicPropertiesTask_1 (Set Server Name)
Public Sub Task_Sub2(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask2 As DTSCustTasks.DynamicPropertiesTask
Set oTask = goPackage.Tasks.New("DTSDynamicPropertiesTask")
oTask.Name = "DTSTask_DTSDynamicPropertiesTask_1"
Set oCustomTask2 = oTask.CustomTask

	oCustomTask2.Name = "DTSTask_DTSDynamicPropertiesTask_1"
	oCustomTask2.Description = "Set Server Name"
	
	Dim oAssignment As DTSCustTasks.DynamicPropertiesTaskAssignment
	'------- An Assignment is defined here

	Set oAssignment = oCustomTask2.Assignments.New
		oAssignment.SourceType = 2
		oAssignment.SourceQueryConnectionID = -1
		oAssignment.SourceGlobalVariable = "SQL Server"
		oAssignment.DestinationPropertyID = "'Connections';'Microsoft OLE DB Provider for SQL Server';'OLEDBProperties';'Data Source';'Properties';'Value'"
		
	oCustomTask2.Assignments.Add oAssignment
	Set oAssignment = Nothing

goPackage.Tasks.Add oTask
Set oCustomTask2 = Nothing
Set oTask = Nothing

End Sub