DTS Generates an error



I have a VB .NET program that uses SQL's DTS to copy a database. (I
created it by exporting from SQL server itself, so it *should* work...)

When I run this program, it gives me this error:
Step 'Copy SQL Server Objects' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Need to run the object to perform this operation
(Microsoft Data Transformation Services (DTS) Package (80040005): Need to
run the object to perform this operation
) (Microsoft SQL-DMO (80004005): [SQL-DMO]Code execution exception:
Step Error code: 80040005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

I searched a bunch of places, and it seems this happens if you do not have
SP 4 installed, but I upgraded to SP4 and I still get this error.
Any ideas how to resolve this?



Source code follows:
Module Module1

Const LogFileName As String = "C:\DTSLog.txt"
Const SourceServer As String = "(local)"
Const SourceDb As String = "Northwind"
Const DestServer As String = "(local)"
Const DestDb As String = "Northwind2"

Sub Main()
Dim DtsPackage As New DTS.Package2
Dim ConnProperty As DTS.OleDBProperties
Dim PackageStep As DTS.Step2
Dim CastTask As DTS.Task
Dim PackageTask As DTS.TransferObjectsTask2


' Set up the package
With DtsPackage
.Name = "Copy Northwind"
.Description = "Copies the DB for testing"
.WriteCompletionStatusToNTEventLog = False
.FailOnError = False
.PackagePriorityClass =
.MaxConcurrentSteps = 4
.LineageOptions = DTS.DTSLineageOptions.DTSLineage_None
.UseTransaction = True
.TransactionIsolationLevel =
.AutoCommitTransaction = True
.RepositoryMetadataOptions =
.UseOLEDBServiceComponents = True
.LogToSQLServer = False
.LogServerFlags =
.FailPackageOnLogFailure = False
.ExplicitGlobalVariables = False
.PackageType = DTS.DTSPackageType.DTSPkgType_Default

.LogFileName = LogFileName
End With

' Create the steps

PackageStep = DtsPackage.Steps.[New]
With PackageStep
.Name = "Copy SQL Server Objects"
.Description = "Copies the objects"
.ExecutionStatus = DTS.DTSStepExecStatus.DTSStepExecStat_Waiting
.ExecuteInMainThread = True
.TaskName = .Name
.CommitSuccess = False
.RollbackFailure = False
.ScriptLanguage = "VBScript"
.AddGlobalVariables = True
.RelativePriority =
.CloseConnection = False
.ExecuteInMainThread = False
.IsPackageDSORowset = False
.JoinTransactionIfPresent = False
.DisableStep = False
.FailPackageOnError = False
End With

' This task does the copy
CastTask = DtsPackage.Tasks.[New]("DTSTransferObjectsTask")

PackageTask = CType(CastTask.CustomTask, DTS.TransferObjectsTask2)
With PackageTask
.Name = "Copy SQL Server Objects"
.Description = "Copies the objects"
.SourceServer = SourceServer
.SourceUseTrustedConnection = True
.SourceDatabase = SourceDb
.DestinationServer = DestServer
.DestinationUseTrustedConnection = True
.DestinationDatabase = DestDb
.ScriptFileDirectory = "C:\Temp" ' TODO: Make that dynamic
.CopyAllObjects = True
.IncludeDependencies = True
.IncludeLogins = False
.IncludeUsers = False
.DropDestinationObjectsFirst = True
.CopySchema = True
.CopyData = DTS.DTSTransfer_CopyDataOption.DTSTransfer_ReplaceData
' Note: If I use this .ScriptOption instead of the next one, the
copy seems to work,
' but it doesn't properly copy indexes and such...
'.ScriptOption =
' Using this .ScriptOption always fails
.ScriptOption =
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_ClusteredIndexes And
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_DatabasePermissions And
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_DRI_AllConstraints And
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_Drops And
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_IncludeIfNotExists And
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_Indexes And
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_NonClusteredIndexes And
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_ObjectPermissions And
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_Permissions And
.ScriptOptionEx = 528400 ' Not sure what this number is, but
it's what DTS set it to when I exported...

.SourceTranslateChar = True
.DestTranslateChar = True
.DestUseTransaction = False
.UseCollation = False
End With

' Run the package


End Sub

End Module

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

dts and vb.net 1
DTS and VB.NET 2
DTS VB .Net application error 1
Substring generates error 3
DTS Event Handlers 1
Executing a DTS package 2
DTS + ExecuteInMainThread + Events in a winform... 1