DTS Package Schedule

[Luvsql]

I've created a DTS Package to copy all objects from Server A to Server B, not using collation (as Servers are different and want to change sort order). I scheduled it to run at night as the DB is 6Gb (eventually want to be able to schedule all 15 of our databases on the weekend). I checked the job history and the following error:

Executed as user: SQLCLUSTER\SYSTEM. ...art:
DTSStep_DTSTransferObjectsTask_1
DTSRun OnProgress: DTSStep_DTSTransferObjectsTask_1;
Scripting objects for Transfer;
PercentComplete = 0;
ProgressCount = 0 DTSRun OnProgress:
DTSStep_DTSTransferObjectsTask_1;
Scripting Logins; PercentComplete = 0;
ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSTransferObjectsTask_1;
Scripting Users; PercentComplete = 0;
ProgressCount = 0 DTSRun OnProgress:
DTSStep_DTSTransferObjectsTask_1;
Scripting Users : 0 Percent Completed;
PercentComplete = 0;
ProgressCount = 0 DTSRun OnProgress:
DTSStep_DTSTransferObjectsTask_1;
0 Percent Completed;
PercentComplete = 0;
ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSTransferObjectsTask_1;
Scripting Users : 1 Percent Completed;
PercentComplete = 1;
ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSTransferObjectsTask_1;
8 Percent Completed;
PercentComplete = 8;
ProgressCount = 0 DTSRun OnProgress:
DTSStep_DTSTransferObjects...
Process Exit Code 1.
The step failed.

I have absolutely no description of what failed and why. I have it set to create destination and copy data (replace existing) so even if the table already exists, it'll copy into it and if it doesn't, it'll create it, then copy into it.

How can I find out exactly why it failed?

 

[mrdenny]

Enable logging on the DTS package and run it again. The DTS log will give you more info that the job history. Or run the package manually, and see if it fails.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[SQLSister]

More than likely the SQL Server Agent user of Server a does not exist or have the proper rights on Server b. Whne you run a DTS package manually, it runs using your login. When it is scheduled as a job, it runs using the SQL Server Agent login.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[Luvsql]

Our agent services are both running with a domain user with admin rights. It shows that the job actually ran for almost 2 hours as well. I have been checking the properties of the "Copy SQL Server Objects Task' but do not see anywhere to enable logging.

 

[mrdenny]

The option to enable the logging is in the package properties, not the task properties. When the package opened, right click on a white area. Click properties. Logging tab. Check the box at the top, and type in the name of your SQL Server. Save the job, and have the agent run it again. After the agent fails, Click click on the package and select Package Logs.

Hopefully it will give you some better info.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Luvsql]

I have enabled the logging and the error I am receiving (after 3 hours):

Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 42000)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL Server]The login 'BPUser' does not exist.
Step Error code: 80043A9F
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:1131


However, under options, I have removed 'copy db users' and 'copy sql logins,' so why is it trying to copy over a sql login?

 

[dbomrrsm]

is one of the tasks in the package trying to use a login 'BPUser' that doesnt exist on the server you have copied the package to ?

[blue]DBomrrsm[/blue]

 

[Luvsql]

I have one task setup to copy all objects, appending data, not collation, no logins.

 

[Luvsql]

There is no sql login setup on our source server called BPUser. As well, there is no user within the database that I am copying called BPUser.

 

[dbomrrsm]

a step it has tried has resulted in the following :

Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL Server]The login 'BPUser' does not exist.

which means that it has attempted a connection as a user called BPUser - can you save the package as a structured storage file or a VB file and search the code for this string.

[blue]DBomrrsm[/blue]

 

[Luvsql]

Is there also anyway to have those tables that transfer successfully to remain and those that don't to fail. If 1 of 1500 tables does not transfer, the entire job fails, or if Table A does not exist on destination server, it fails. I would like just that one table to fail, not the entire job.

I save the package as vb file. No mention of this user.

'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: C:\My Documents\SORT ORDER CHANGE\Copy all Tables.bas
'Package Name: Copy all Tables
'Package Description:
'Generated Date: 1/17/2005
'Generated Time: 2:00:48 PM
'****************************************************************

Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
set goPackage = goPackageOld

goPackage.Name = "Copy all Tables"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = True
goPackage.LogServerName = "SQLCLUSTER"
goPackage.LogServerUserName = "sa"
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = True
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0



'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------

Dim oStep as DTS.Step2
Dim oPrecConstraint as DTS.PrecedenceConstraint

'------------- a new step defined below

Set oStep = goPackage.Steps.New

oStep.Name = "DTSStep_DTSTransferObjectsTask_1"
oStep.Description = "Copy SQL Server Objects Task: undefined"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSTransferObjectsTask_1"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

goPackage.Steps.Add oStep
Set oStep = Nothing

'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------

'------------- call Task_Sub1 for task DTSTask_DTSTransferObjectsTask_1 (Copy SQL Server Objects Task: undefined)
Call Task_Sub1( goPackage )

'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------

'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
tracePackageError goPackage
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing package lines above and uncomment the saving package line
set goPackage = Nothing

set goPackageOld = Nothing

End Sub


'-----------------------------------------------------------------------------
' error reporting using step.GetExecutionErrorInfo after execution
'-----------------------------------------------------------------------------
Public Sub tracePackageError(oPackage As DTS.Package)
Dim ErrorCode As Long
Dim ErrorSource As String
Dim ErrorDescription As String
Dim ErrorHelpFile As String
Dim ErrorHelpContext As Long
Dim ErrorIDofInterfaceWithError As String
Dim i As Integer

For i = 1 To oPackage.Steps.Count
If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
oPackage.Steps(i).GetExecutionErrorInfo ErrorCode, ErrorSource, ErrorDescription, _
ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError
MsgBox oPackage.Steps(i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescription
End If
Next i

End Sub

'------------- define Task_Sub1 for task DTSTask_DTSTransferObjectsTask_1 (Copy SQL Server Objects Task: undefined)
Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.TransferObjectsTask2
Set oTask = goPackage.Tasks.New("DTSTransferObjectsTask")
oTask.Name = "DTSTask_DTSTransferObjectsTask_1"
Set oCustomTask1 = oTask.CustomTask

oCustomTask1.Name = "DTSTask_DTSTransferObjectsTask_1"
oCustomTask1.Description = "Copy SQL Server Objects Task: undefined"
oCustomTask1.SourceServer = "SQL-SERVER"
oCustomTask1.SourceLogin = "sa"
oCustomTask1.SourceUseTrustedConnection = False
oCustomTask1.SourceDatabase = "Z"
oCustomTask1.DestinationServer = "SQLCLUSTER"
oCustomTask1.DestinationLogin = "sa"
oCustomTask1.DestinationUseTrustedConnection = False
oCustomTask1.DestinationDatabase = "Z"
oCustomTask1.ScriptFileDirectory = "C:\My Documents\SORT ORDER CHANGE"
oCustomTask1.CopyAllObjects = True
oCustomTask1.IncludeDependencies = False
oCustomTask1.IncludeLogins = False
oCustomTask1.IncludeUsers = False
oCustomTask1.DropDestinationObjectsFirst = False
oCustomTask1.CopySchema = False
oCustomTask1.CopyData = 1
oCustomTask1.ScriptOption = -2146995971
oCustomTask1.ScriptOptionEx = 528400
oCustomTask1.SourceTranslateChar = True
oCustomTask1.DestTranslateChar = True
oCustomTask1.DestUseTransaction = False
oCustomTask1.UseCollation = False

 

[Luvsql]

I am trying just a small database so I can see what the error is. However, it has now been running for over 40 minutes and it is a very small database (150Mb). It's sitting at 22%. If I manually go to All Tasks > Import Data and do the exact same thing this way, it's much, much faster. Why is doing it through a package so much slower?

I have copied 700Mb from an access database into our source sql server in less than 5 minutes as well.