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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Problem with Execute Method.
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 09/28/2003 :  01:58:25  Show Profile  Reply with Quote
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?


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

objTask.Execute objPackage, objEv, objPl, DTSTaskExecResult_Success
End Sub


Almighty SQL Goddess

38200 Posts

Posted - 09/28/2003 :  02:06:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
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.


Edited by - tkizer on 09/28/2003 02:07:22
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000