By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,815 Members | 1,066 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,815 IT Pros & Developers. It's quick & easy.

DTS Generates an error

P: n/a
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_Norma l
.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.DTSStepRelativePriorit y_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_Replace Data
' 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_De fault
' Using this .ScriptOption always fails
.ScriptOption =
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_Cl usteredIndexes And
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_Da tabasePermissions And
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_DR I_AllConstraints And
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_Dr ops And
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_In cludeIfNotExists And
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_In dexes And
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_No nClusteredIndexes And
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_Ob jectPermissions And
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_Pe rmissions And
DTS.DTSTransfer_ScriptOption.DTSTransfer_Script_Tr iggers
.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

Nov 21 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.