DTS Generates an error


G

Guest

Hello.
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:
EXCEPTION_ACCESS_VIOLATION)
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?

Thanks.

-Dennis

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

System.IO.File.Delete(LogFileName)

' Set up the package
With DtsPackage
.Name = "Copy Northwind"
.Description = "Copies the DB for testing"
.WriteCompletionStatusToNTEventLog = False
.FailOnError = False
.PackagePriorityClass =
DTS.DTSPackagePriorityClass.DTSPriorityClass_Normal
.MaxConcurrentSteps = 4
.LineageOptions = DTS.DTSLineageOptions.DTSLineage_None
.UseTransaction = True
.TransactionIsolationLevel =
DTS.DTSIsolationLevel.DTSIsoLevel_CursorStability
.AutoCommitTransaction = True
.RepositoryMetadataOptions =
DTS.DTSRepositoryMetadataOptions.DTSReposMetadata_Default
.UseOLEDBServiceComponents = True
.LogToSQLServer = False
.LogServerFlags =
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default
.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 =
DTS.DTSStepRelativePriority.DTSStepRelativePriority_Normal
.CloseConnection = False
.ExecuteInMainThread = False
.IsPackageDSORowset = False
.JoinTransactionIfPresent = False
.DisableStep = False
.FailPackageOnError = False
End With
DtsPackage.Steps.Add(PackageStep)

' 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 =
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_Default
' 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
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_Triggers
.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
DtsPackage.Tasks.Add(CastTask)

' Run the package
DtsPackage.Execute()

DtsPackage.UnInitialize()

Process.Start(LogFileName)
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 1
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

Top