Any DTS examples for Visual Basic??

[aljubicic]

Hi All,

I am trying to encode DTS package within Visual Basic 6. I have been succesful in creating a Database however I always get an error when trying to copy a Database on one server to another server( they are both SQL2000 Servers). The error is...

Start: Copy Database Step

QueryCancel: Copy Database Step

Error: Copy Database Step -2147221499 Microsoft Data Transformation Services (DTS) Package

Start: Copy Database Step

QueryCancel:


My Code is the as Follows..



Private Sub GenericTaskPackage()
'Run the supplied package (*.dts)
Dim oConnection As DTS.Connection
Dim oStep As DTS.Step
Dim oTask As DTS.Task
Dim oCustomTask As DTS.TransferObjectsTask 'TaskObject

'On Error GoTo PackageError

Set oPackageEvents = oPackage

'Create step, tasks
'Connections are not necessary when transfering objects. In general are needed when pumping data
Set oStep = oPackage.Steps.New
Set oTask = oPackage.Tasks.New("DTSTransferObjectsTask&quot
Set oCustomTask = oTask.CustomTask

'Fail the package on errors
oPackage.FailOnError = False

With oStep
.Name = "Copy Database Step"
.ExecuteInMainThread = True
End With

With oTask
.Name = "GenericPkgTask"
End With
'Customize the Task Object

MsgBox "Set config"
With oCustomTask

.Name = "DTSTransferObjectsTask"
'SourceServer property specifies the name of the source server
.SourceServer = sSRVFrom
'SourceUseTrustedConnection property specifies whether the Windows Authentication security mode is to be used
.SourceUseTrustedConnection = False
.SourceLogin = "sa"
.SourcePassword = ""
'SourceDatabase property specifies the name of the source database
.SourceDatabase = "NAR"
'DestinationServer property specifies the name of the destination server when you transfer SQL Server objects
.DestinationServer = sSRVDestination
'DestinationUseTrustedConnection property specifies whether Windows Authentication is used
.DestinationUseTrustedConnection = False
.DestinationLogin = "sa"
.DestinationPassword = ""
'DestinationDatabase property specifies the name of the destination database to use
.DestinationDatabase = "NAR"
'ScriptFileDirectory property specifies the directory to which the script file and log files are written
.ScriptFileDirectory = App.Path
'CopyAllObjects property specifies whether to transfer all objects
.CopyAllObjects = True
'IncludeDependencies property specifies whether dependent objects are scripted and transferred during a transfer
.IncludeDependencies = False
'IncludeLogins property specifies whether the logins on the source are scripted and transferred
.IncludeLogins = False
'IncludeUsers property specifies whether the database users on the source are scripted and transferred
.IncludeUsers = False
'DropDestinationObjectsFirst property specifies whether to drop objects, if they already exist on the destination
.DropDestinationObjectsFirst = True
'CopySchema property specifies whether database objects are copied
.CopySchema = True
'CopyData property specifies whether data is copied and whether existing data is replaced or appended to
.CopyData = DTSTransfer_ReplaceData

End With

oStep.TaskName = oCustomTask.Name

MsgBox "Add Step"
'Add the step
oPackage.Steps.Add oStep
oPackage.Tasks.Add oTask

MsgBox "Execute"
'Run the package and release references.
oPackage.Execute


'Clean up
Set oCustomTask = Nothing
Set oTask = Nothing
Set oStep = Nothing
oPackage.UnInitialize

Exit Sub

PackageError:
Dim sMsg As String
sMsg = "Package failed error: " & sErrorNumConv(Err.Number) & _
vbCrLf & Err.Description & vbCrLf & sAccumStepErrors(oPackage)
MsgBox sMsg, vbExclamation, oPackage.Name
End Sub


I am using SQL authentication to login and copy the Database across. If anyone can tell me why this error is occuring or any code examples would help greatly.


Thanks
Anthony

 

[mkal]

You could try using Enterprise Mangager to design the DTS package to copy the database. Once you've tested and make sure it works you can choose Package from the Menu >> Save As. From the dialog box that appears where it says Location choose to save it as a Visual Basic file. You can then open it up in Visual Studio and view the code.

 

[tb]

You might find this site useful

http://www.sqldts.com

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!