BAS file DTS using vbs
-
hi
I am trying to use DTS to copy databases using the transfer database object and after I select the locations tab EM freezes.
I have 2 instances on the same machine named: RIZVIS and RIZVIS\B
I am copying a DB with one table from RIZVIS to RIZVIS\B to test the process which I will then deploy onto our production box to a backup machine (for those wondering: this procedure is not meant to cover High Availabity).
I just want to copy the databases from one server to the next. Because DTS works I intend to use a bas file to run the same process for all databases. I will write a vbs file which will use the code below. The only thing, I am running the vbs using the following command:
cscript cdw.vbs
but its throwing the following error:
U:\cdw.vbs(2, 21) Microsoft
VBScript compilation error: Expected end of statement
Any ideas ???
DTS package VBS CODE:
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
set goPackage = goPackageOld
goPackage.Name = "New Package"
goPackage.Description = "DTS package description"
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
Dim oConnProperty As DTS.OleDBProperty
'---------------------------------------------------------------------------
' 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 = "Copy SQL Server Objects"
oStep.Description = "Copy SQL Server Objects"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy SQL Server Objects"
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
'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------
'------------- call Task_Sub1 for task Copy SQL Server Objects (Copy SQL Server Objects)
Call Task_Sub1( 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 line 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 Copy SQL Server Objects (Copy SQL Server Objects)
Public Sub Task_Sub1(ByVal goPackage As Object)
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.TransferObjectsTask2
Set oTask = goPackage.Tasks.New("DTSTransferObjectsTask")
oTask.Name = "Copy SQL Server Objects"
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "Copy SQL Server Objects"
oCustomTask1.Description = "Copy SQL Server Objects"
oCustomTask1.SourceServer = "(LOCAL)"
oCustomTask1.SourceUseTrustedConnection = True
oCustomTask1.SourceDatabase = "saj01"
oCustomTask1.DestinationServer = "rizvis\b"
oCustomTask1.DestinationUseTrustedConnection = True
oCustomTask1.DestinationDatabase = "saj01"
oCustomTask1.ScriptFileDirectory = "C:\Program Files\Microsoft SQL Server\80\Tools"
oCustomTask1.CopyAllObjects = True
oCustomTask1.IncludeDependencies = True
oCustomTask1.IncludeLogins = True
oCustomTask1.IncludeUsers = True
oCustomTask1.DropDestinationObjectsFirst = True
oCustomTask1.CopySchema = True
oCustomTask1.CopyData = 1
oCustomTask1.ScriptOption = -2146995969
oCustomTask1.ScriptOptionEx = 4722704
oCustomTask1.SourceTranslateChar = True
oCustomTask1.DestTranslateChar = True
oCustomTask1.DestUseTransaction = False
oCustomTask1.UseCollation = True
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
End Sub
Help ???!?!?!?
-
Ok I'm no developer but I'm guessing that you can't run VB code in VB Script. You would need to use VB.
Why wouldn't you have the DTS package stored on the server, or in a structured storage file?
--------------------
Colt 45 - the original point and click interface
-
because I want to be able to send it parameters as I want the database and server name to change
-
That's easily done with a few global variables and a Dynamic Properties task.
--------------------
Colt 45 - the original point and click interface -
There are many differences between VBScript and VB. I'm pretty sure two of them are:
VBScript:
Dim X
VB:
Dim X [as <datatype>]
VBScript:
next
VB:
Next [<variable>]
I always have trouble developing in VBScript. It's powerful but finicky. I often find myself writing legal VB and having VBScript reject it. D'oh!
I agree with philcart, look up how to use Dynamic Properties tasks and Global Variables. They're easy to use and relatively powerful.
It may be ugly technique but I often break down what could be one big VB script into component parts with Dynamic Properties or other tasks in between to handle things that aren't real easy in VBScript.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply