| Author |
 Topic  |
|
|
sjchandru
Starting Member
2 Posts |
 Posted - 09/28/2003 : 01:58:25
|
Hi,
I would like to copy some tables from one database to another.
(Both are SQL server 7).
I tried creating a task object and execute. (the code for the above has been copied from msdn sample).
But, I got an error in the execute method, which I could not resolve. I have given the entire code below: Can any one of u help me solving this?
Regards,
Chandramohan.
------------------------------------------------------------
Code
------------------------------------------------------------
Option Explicit
Dim objTest As DTS.Package
Private Sub Command1_Click()
Set objTest = New DTS.Package
RunTransfer objTest
MsgBox "Task successfully executed.", vbInformation
End Sub
Private Sub RunTransfer(ByVal objPackage As DTS.Package)
Dim objStep As DTS.Step
Dim objTask As DTS.Task
Dim objXferObj As DTS.TransferObjectsTask
'create step and task
Set objStep = objPackage.Steps.New
Set objTask = objPackage.Tasks.New("DTSTransferObjectsTask")
Set objXferObj = objTask.CustomTask
'configure transfer objects task
With objXferObj
.Name = "XferObjTask"
.SourceServer = "(www)"
.SourceUseTrustedConnection = True
.SourceDatabase = "bluebird"
.DestinationServer = "(www)"
.DestinationUseTrustedConnection = True
.DestinationDatabase = "bluebirdtest"
.ScriptFileDirectory = "D:\dts"
.CopyAllObjects = False
.IncludeDependencies = True
.IncludeLogins = False
.IncludeUsers = False
.DropDestinationObjectsFirst = True
.CopySchema = True
.CopyData = DTSTransfer_AppendData
.AddObjectForTransfer "employee", "dbo", DTSSQLObj_UserTable
.AddObjectForTransfer "customer", "dbo", DTSSQLObj_UserTable
End With
'link step to task
objStep.TaskName = objXferObj.Name
objStep.Name = "XferObjStep"
objPackage.Steps.Add objStep
objPackage.Tasks.Add objTask
Dim objEv As EventInfo
Dim objPl As PackageLog
'Execute
objTask.Execute objPackage, objEv, objPl, DTSTaskExecResult_Success
End Sub
--------------------------------------------------------
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 09/28/2003 : 02:06:54
|
Instead of doing it this way, why don't you just use INSERT INTO/SELECT FROM? If the destination database is on another server, then you can do it with linked servers.
Here is an example of when the databases are on the same server:
INSERT INTO DestDB.dbo.Table1 (Column1, Column2)
SELECT Column1, Column2
FROM SourceDB.dbo.Table1
Here is an example of when the databases are on different servers:
INSERT INTO LinkedServer1.DestDB.dbo.Table1 (Column1, Column2)
SELECT Column1, Column2
FROM SourceDB.dbo.Table1
If no one answers your question and you still want to use this method, then I would suggest creating the DTS package using the DTS UI. Then take a look at the code by saving it to a VB file. You can do this from the SAVE AS screen of the DTS package. Then compare your code to the code that the DTS UI created.
Tara |
Edited by - tkizer on 09/28/2003 02:07:22 |
 |
|
| |
Topic |
|
|
|
Problem with Execute Method.
