Problem with Execute Method. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Problem with Execute Method.

Author

Topic

sjchandru
Starting Member

2 Posts

Posted - 2003-09-28 : 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

38200 Posts

Posted - 2003-09-28 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources