[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 678
  • Last Modified:

DTS workflow is not working as told to

Hello experts.

DTS package has a task that on failure should lead to another task reporting an error.
But on failure the package stops instead of following the "OnFailure" rule. Why ?
Flag "Fail package on step failure" on Task2 is not selected !!!

Some details:

START ->
   Task1 (dynamic properties)
         -[OnSuccess]->
                Task2 (transformation)
                        -[OnFailure]->
                            Task 3 (Error report)

Error report task is not being run after Task2 error.

Thanks
Gregor


0
gregajesih
Asked:
gregajesih
  • 6
  • 4
1 Solution
 
SashPCommented:
Hi gregajesih

Open the DTS package, right click on the desktop and select "Package Properties".  Check that "Fail on First Error" is not selected on the "Logging" tab.

Fail package on first error

Specify that a first step failure stops the entire package. Any remaining steps or tasks are not run. If this option is not selected, then the package will continue to run regardless of any failures in any of the steps. The package will complete with a successful status.

Cheers Sash
0
 
gregajesihAuthor Commented:
Sash,

"Fail package on first error" was not checked as well. And yet package failed.
And what I wanted was the package to continue even if Task2 failed.
Particularly because of that. Now since the only step to do after failure is to do a proper report of an error, I might try do this report some other way. But this is not what the tools promises. It should continue and it did not. If you would be so kind to give me your email, I  can send you the package or its graphic interpretation.

BR
Gregor
0
 
SashPCommented:
Greg

Save the DTS package as a VB File.  Post the contents somewhere.  I will have a look.

Cheers Sash
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
gregajesihAuthor Commented:
'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: C:\ZL_Upload_PoberiStanje.bas
'Package Name: ZL_Upload_PoberiStanje
'Package Description: ZL_Upload_PoberiStanje
'Generated Date: 3.11.2004
'Generated Time: 13:09:01
'****************************************************************

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

      goPackage.Name = "ZL_Upload_PoberiStanje"
      goPackage.Description = "ZL_Upload_PoberiStanje"
      goPackage.WriteCompletionStatusToNTEventLog = True
      goPackage.LogFileName = "c:\Distribucija.txt"
      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 = "10.100.10.30\MPSCENTER"
      goPackage.LogServerUserName = "remote"
      goPackage.LogServerFlags = 0
      goPackage.FailPackageOnLogFailure = False
      goPackage.ExplicitGlobalVariables = False
      goPackage.PackageType = 0
      

'---------------------------------------------------------------------------
' begin to write package global variables information
'---------------------------------------------------------------------------

      Dim oGlobal As DTS.GlobalVariable

      Set oGlobal = goPackage.GlobalVariables.New("DestinationServer")
      oGlobal = "127.0.0.1"
      goPackage.GlobalVariables.Add oGlobal
      set oGlobal = Nothing

      Set oGlobal = goPackage.GlobalVariables.New("my_odprema")
      oGlobal = 1847

      goPackage.GlobalVariables.Add oGlobal
      set oGlobal = Nothing

      Set oGlobal = goPackage.GlobalVariables.New("my_mpsposiljatelj")
      oGlobal = "' '"
      goPackage.GlobalVariables.Add oGlobal
      set oGlobal = Nothing


'---------------------------------------------------------------------------
' create package connection information
'---------------------------------------------------------------------------

Dim oConnection as DTS.Connection2

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("SQLOLEDB")

      oConnection.ConnectionProperties("Persist Security Info") = True
      oConnection.ConnectionProperties("User ID") = "remote"
      oConnection.ConnectionProperties("Initial Catalog") = "MPS_OMV_TEST"
      oConnection.ConnectionProperties("Data Source") = "10.100.10.30\MPSCENTER"
      oConnection.ConnectionProperties("Application Name") = "DTS Designer"
      
      oConnection.Name = "MPSCENTER"
      oConnection.ID = 2
      oConnection.Reusable = True
      oConnection.ConnectImmediate = False
      oConnection.DataSource = "10.100.10.30\MPSCENTER"
      oConnection.UserID = "remote"
      oConnection.ConnectionTimeout = 60
      oConnection.Catalog = "MPS_OMV_TEST"
      oConnection.UseTrustedConnection = False
      oConnection.UseDSL = False
      
      'If you have a password for this connection, please uncomment and add your password below.
      'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("SQLOLEDB")

      oConnection.ConnectionProperties("Persist Security Info") = True
      oConnection.ConnectionProperties("User ID") = "remote"
      oConnection.ConnectionProperties("Initial Catalog") = "IPS_OMV"
      oConnection.ConnectionProperties("Data Source") = "127.0.0.1"
      oConnection.ConnectionProperties("Application Name") = "DTS Designer"
      
      oConnection.Name = "Remote"
      oConnection.ID = 1
      oConnection.Reusable = True
      oConnection.ConnectImmediate = False
      oConnection.DataSource = "127.0.0.1"
      oConnection.UserID = "remote"
      oConnection.ConnectionTimeout = 60
      oConnection.Catalog = "IPS_OMV"
      oConnection.UseTrustedConnection = False
      oConnection.UseDSL = False
      
      'If you have a password for this connection, please uncomment and add your password below.
      'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'---------------------------------------------------------------------------
' 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_DTSDynamicPropertiesTask_1"
      oStep.Description = "declare Remote site"
      oStep.ExecutionStatus = 1
      oStep.TaskName = "DTSTask_DTSDynamicPropertiesTask_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

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

Set oStep = goPackage.Steps.New

      oStep.Name = "DTSStep_DTSDataPumpTask_1"
      oStep.Description = "PoberiStanjeBS"
      oStep.ExecutionStatus = 1
      oStep.TaskName = "DTSTask_DTSDataPumpTask_1"
      oStep.CommitSuccess = True
      oStep.RollbackFailure = True
      oStep.ScriptLanguage = "VBScript"
      oStep.AddGlobalVariables = True
      oStep.RelativePriority = 3
      oStep.CloseConnection = False
      oStep.ExecuteInMainThread = False
      oStep.IsPackageDSORowset = False
      oStep.JoinTransactionIfPresent = True
      oStep.DisableStep = False
      oStep.FailPackageOnError = False
      
goPackage.Steps.Add oStep
Set oStep = Nothing

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

Set oStep = goPackage.Steps.New

      oStep.Name = "DTSStep_DTSSendMailTask_1"
      oStep.Description = "Poro&#269;ilo o napaki"
      oStep.ExecutionStatus = 1
      oStep.TaskName = "DTSTask_DTSSendMailTask_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

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

Set oStep = goPackage.Steps.New

      oStep.Name = "DTSStep_DTSCreateProcessTask_1"
      oStep.Description = "Error report"
      oStep.ExecutionStatus = 1
      oStep.TaskName = "DTSTask_DTSCreateProcessTask_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

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

Set oStep = goPackage.Steps.New

      oStep.Name = "DTSStep_DTSExecuteSQLTask_8"
      oStep.Description = "Zapiši napako"
      oStep.ExecutionStatus = 1
      oStep.TaskName = "DTSTask_DTSExecuteSQLTask_8"
      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

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

Set oStep = goPackage.Steps.New

      oStep.Name = "DTSStep_DTSExecuteSQLTask_1"
      oStep.Description = "Execute SQL Task: undefined"
      oStep.ExecutionStatus = 1
      oStep.TaskName = "DTSTask_DTSExecuteSQLTask_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

'------------- a precedence constraint for steps defined below

Set oStep = goPackage.Steps("DTSStep_DTSDataPumpTask_1")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSExecuteSQLTask_1")
      oPrecConstraint.StepName = "DTSStep_DTSExecuteSQLTask_1"
      oPrecConstraint.PrecedenceBasis = 1
      oPrecConstraint.Value = 0
      
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

'------------- a precedence constraint for steps defined below

Set oStep = goPackage.Steps("DTSStep_DTSSendMailTask_1")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSDynamicPropertiesTask_1")
      oPrecConstraint.StepName = "DTSStep_DTSDynamicPropertiesTask_1"
      oPrecConstraint.PrecedenceBasis = 1
      oPrecConstraint.Value = 1
      
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

'------------- a precedence constraint for steps defined below

Set oStep = goPackage.Steps("DTSStep_DTSSendMailTask_1")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSDataPumpTask_1")
      oPrecConstraint.StepName = "DTSStep_DTSDataPumpTask_1"
      oPrecConstraint.PrecedenceBasis = 1
      oPrecConstraint.Value = 1
      
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

'------------- a precedence constraint for steps defined below

Set oStep = goPackage.Steps("DTSStep_DTSSendMailTask_1")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSExecuteSQLTask_1")
      oPrecConstraint.StepName = "DTSStep_DTSExecuteSQLTask_1"
      oPrecConstraint.PrecedenceBasis = 1
      oPrecConstraint.Value = 1
      
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

'------------- a precedence constraint for steps defined below

Set oStep = goPackage.Steps("DTSStep_DTSCreateProcessTask_1")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSSendMailTask_1")
      oPrecConstraint.StepName = "DTSStep_DTSSendMailTask_1"
      oPrecConstraint.PrecedenceBasis = 0
      oPrecConstraint.Value = 4
      
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

'------------- a precedence constraint for steps defined below

Set oStep = goPackage.Steps("DTSStep_DTSExecuteSQLTask_8")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSSendMailTask_1")
      oPrecConstraint.StepName = "DTSStep_DTSSendMailTask_1"
      oPrecConstraint.PrecedenceBasis = 0
      oPrecConstraint.Value = 4
      
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

'------------- a precedence constraint for steps defined below

Set oStep = goPackage.Steps("DTSStep_DTSExecuteSQLTask_1")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSDynamicPropertiesTask_1")
      oPrecConstraint.StepName = "DTSStep_DTSDynamicPropertiesTask_1"
      oPrecConstraint.PrecedenceBasis = 1
      oPrecConstraint.Value = 0
      
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

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

'------------- call Task_Sub1 for task DTSTask_DTSDynamicPropertiesTask_1 (declare Remote site)
Call Task_Sub1( goPackage      )

'------------- call Task_Sub2 for task DTSTask_DTSDataPumpTask_1 (PoberiStanjeBS)
Call Task_Sub2( goPackage      )

'------------- call Task_Sub3 for task DTSTask_DTSSendMailTask_1 (Poro&#269;ilo o napaki)
Call Task_Sub3( goPackage      )

'------------- call Task_Sub4 for task DTSTask_DTSCreateProcessTask_1 (Error report)
Call Task_Sub4( goPackage      )

'------------- call Task_Sub5 for task DTSTask_DTSExecuteSQLTask_8 (Zapiši napako)
Call Task_Sub5( goPackage      )

'------------- call Task_Sub6 for task DTSTask_DTSExecuteSQLTask_1 (Execute SQL Task: undefined)
Call Task_Sub6( 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_DTSDynamicPropertiesTask_1 (declare Remote site)
Public Sub Task_Sub1(ByVal goPackage As Object)

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

Dim oCustomTask1 As DTSCustTasks.DynamicPropertiesTask
Set oTask = goPackage.Tasks.New("DTSDynamicPropertiesTask")
oTask.Name = "DTSTask_DTSDynamicPropertiesTask_1"
Set oCustomTask1 = oTask.CustomTask

      oCustomTask1.Name = "DTSTask_DTSDynamicPropertiesTask_1"
      oCustomTask1.Description = "declare Remote site"
      
      Dim oAssignment As DTSCustTasks.DynamicPropertiesTaskAssignment
      '------- An Assignment is defined here

      Set oAssignment = oCustomTask1.Assignments.New
            oAssignment.SourceType = 2
            oAssignment.SourceQueryConnectionID = -1
            oAssignment.SourceGlobalVariable = "DestinationServer"
            oAssignment.DestinationPropertyID = "'Connections';'Remote';'OLEDBProperties';'Data Source';'Properties';'Value'"
            
      oCustomTask1.Assignments.Add oAssignment
      Set oAssignment = Nothing

goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing

End Sub

'------------- define Task_Sub2 for task DTSTask_DTSDataPumpTask_1 (PoberiStanjeBS)
Public Sub Task_Sub2(ByVal goPackage As Object)

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

Dim oCustomTask2 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
oTask.Name = "DTSTask_DTSDataPumpTask_1"
Set oCustomTask2 = oTask.CustomTask

      oCustomTask2.Name = "DTSTask_DTSDataPumpTask_1"
      oCustomTask2.Description = "PoberiStanjeBS"
      oCustomTask2.SourceConnectionID = 1
      oCustomTask2.SourceSQLStatement = "select SITE," & vbCrLf
      oCustomTask2.SourceSQLStatement = oCustomTask2.SourceSQLStatement & "       getdate() as D_STANJA," & vbCrLf
      oCustomTask2.SourceSQLStatement = oCustomTask2.SourceSQLStatement & "       DS04OSTOP_LISTA," & vbCrLf
      oCustomTask2.SourceSQLStatement = oCustomTask2.SourceSQLStatement & "       DS04ONACPL" & vbCrLf
      oCustomTask2.SourceSQLStatement = oCustomTask2.SourceSQLStatement & "from  D_S_04O,ZMADAT" & vbCrLf
      oCustomTask2.SourceSQLStatement = oCustomTask2.SourceSQLStatement & "where DS04ONACPL = ?" & vbCrLf
      oCustomTask2.SourceSQLStatement = oCustomTask2.SourceSQLStatement & "          and DS04OSTOP_LISTA is not null"
      oCustomTask2.DestinationConnectionID = 2
      oCustomTask2.DestinationObjectName = "[MPS_OMV_TEST].[dbo].[ZL_TMP_STANJE_BS]"
      oCustomTask2.ProgressRowCount = 1000
      oCustomTask2.MaximumErrorCount = 0
      oCustomTask2.FetchBufferSize = 1
      oCustomTask2.UseFastLoad = True
      oCustomTask2.InsertCommitSize = 0
      oCustomTask2.ExceptionFileColumnDelimiter = "|"
      oCustomTask2.ExceptionFileRowDelimiter = vbCrLf
      oCustomTask2.AllowIdentityInserts = False
      oCustomTask2.FirstRow = "0"
      oCustomTask2.LastRow = "0"
      oCustomTask2.FastLoadOptions = 2
      oCustomTask2.ExceptionFileOptions = 1
      oCustomTask2.InputGlobalVariableNames = """my_mpsposiljatelj"""
      oCustomTask2.DataPumpOptions = 0
      
Call oCustomTask2_Trans_Sub1( oCustomTask2      )
Call oCustomTask2_Trans_Sub2( oCustomTask2      )
Call oCustomTask2_Trans_Sub3( oCustomTask2      )
Call oCustomTask2_Trans_Sub4( oCustomTask2      )
goPackage.Tasks.Add oTask
Set oCustomTask2 = Nothing
Set oTask = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub1(ByVal oCustomTask2 As Object)

      Dim oTransformation As DTS.Transformation2
      Dim oTransProps as DTS.Properties
      Dim oColumn As DTS.Column
      Set oTransformation = oCustomTask2.Transformations.New("DTSPump.DataPumpTransformCopy")
            oTransformation.Name = "DS04OSTOP_LISTA->ID_HOST"
            oTransformation.TransformFlags = 63
            oTransformation.ForceSourceBlobsBuffered = 0
            oTransformation.ForceBlobsInMemory = False
            oTransformation.InMemoryBlobSize = 1048576
            oTransformation.TransformPhases = 4
            
            Set oColumn = oTransformation.SourceColumns.New("DS04OSTOP_LISTA" , 1)
                  oColumn.Name = "DS04OSTOP_LISTA"
                  oColumn.Ordinal = 1
                  oColumn.Flags = 120
                  oColumn.Size = 6
                  oColumn.DataType = 129
                  oColumn.Precision = 0
                  oColumn.NumericScale = 0
                  oColumn.Nullable = True
                  
            oTransformation.SourceColumns.Add oColumn
            Set oColumn = Nothing

            Set oColumn = oTransformation.DestinationColumns.New("ID_HOST" , 1)
                  oColumn.Name = "ID_HOST"
                  oColumn.Ordinal = 1
                  oColumn.Flags = 120
                  oColumn.Size = 0
                  oColumn.DataType = 3
                  oColumn.Precision = 0
                  oColumn.NumericScale = 0
                  oColumn.Nullable = True
                  
            oTransformation.DestinationColumns.Add oColumn
            Set oColumn = Nothing

      Set oTransProps = oTransformation.TransformServerProperties

            
      Set oTransProps = Nothing

      oCustomTask2.Transformations.Add oTransformation
      Set oTransformation = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub2(ByVal oCustomTask2 As Object)

      Dim oTransformation As DTS.Transformation2
      Dim oTransProps as DTS.Properties
      Dim oColumn As DTS.Column
      Set oTransformation = oCustomTask2.Transformations.New("DTSPump.DataPumpTransformCopy")
            oTransformation.Name = "DS04ONACPL->MPSPOSILJATELJ"
            oTransformation.TransformFlags = 63
            oTransformation.ForceSourceBlobsBuffered = 0
            oTransformation.ForceBlobsInMemory = False
            oTransformation.InMemoryBlobSize = 1048576
            oTransformation.TransformPhases = 4
            
            Set oColumn = oTransformation.SourceColumns.New("DS04ONACPL" , 1)
                  oColumn.Name = "DS04ONACPL"
                  oColumn.Ordinal = 1
                  oColumn.Flags = 32792
                  oColumn.Size = 2
                  oColumn.DataType = 129
                  oColumn.Precision = 0
                  oColumn.NumericScale = 0
                  oColumn.Nullable = False
                  
            oTransformation.SourceColumns.Add oColumn
            Set oColumn = Nothing

            Set oColumn = oTransformation.DestinationColumns.New("MPSPOSILJATELJ" , 1)
                  oColumn.Name = "MPSPOSILJATELJ"
                  oColumn.Ordinal = 1
                  oColumn.Flags = 24
                  oColumn.Size = 2
                  oColumn.DataType = 129
                  oColumn.Precision = 0
                  oColumn.NumericScale = 0
                  oColumn.Nullable = False
                  
            oTransformation.DestinationColumns.Add oColumn
            Set oColumn = Nothing

      Set oTransProps = oTransformation.TransformServerProperties

            
      Set oTransProps = Nothing

      oCustomTask2.Transformations.Add oTransformation
      Set oTransformation = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub3(ByVal oCustomTask2 As Object)

      Dim oTransformation As DTS.Transformation2
      Dim oTransProps as DTS.Properties
      Dim oColumn As DTS.Column
      Set oTransformation = oCustomTask2.Transformations.New("DTSPump.DataPumpTransformCopy")
            oTransformation.Name = "SITE"
            oTransformation.TransformFlags = 63
            oTransformation.ForceSourceBlobsBuffered = 0
            oTransformation.ForceBlobsInMemory = False
            oTransformation.InMemoryBlobSize = 1048576
            oTransformation.TransformPhases = 4
            
            Set oColumn = oTransformation.SourceColumns.New("SITE" , 1)
                  oColumn.Name = "SITE"
                  oColumn.Ordinal = 1
                  oColumn.Flags = 32792
                  oColumn.Size = 4
                  oColumn.DataType = 129
                  oColumn.Precision = 0
                  oColumn.NumericScale = 0
                  oColumn.Nullable = False
                  
            oTransformation.SourceColumns.Add oColumn
            Set oColumn = Nothing

            Set oColumn = oTransformation.DestinationColumns.New("SITE" , 1)
                  oColumn.Name = "SITE"
                  oColumn.Ordinal = 1
                  oColumn.Flags = 24
                  oColumn.Size = 0
                  oColumn.DataType = 2
                  oColumn.Precision = 0
                  oColumn.NumericScale = 0
                  oColumn.Nullable = False
                  
            oTransformation.DestinationColumns.Add oColumn
            Set oColumn = Nothing

      Set oTransProps = oTransformation.TransformServerProperties

            
      Set oTransProps = Nothing

      oCustomTask2.Transformations.Add oTransformation
      Set oTransformation = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub4(ByVal oCustomTask2 As Object)

      Dim oTransformation As DTS.Transformation2
      Dim oTransProps as DTS.Properties
      Dim oColumn As DTS.Column
      Set oTransformation = oCustomTask2.Transformations.New("DTSPump.DataPumpTransformCopy")
            oTransformation.Name = "D_STANJA"
            oTransformation.TransformFlags = 63
            oTransformation.ForceSourceBlobsBuffered = 0
            oTransformation.ForceBlobsInMemory = False
            oTransformation.InMemoryBlobSize = 1048576
            oTransformation.TransformPhases = 4
            
            Set oColumn = oTransformation.SourceColumns.New("D_STANJA" , 1)
                  oColumn.Name = "D_STANJA"
                  oColumn.Ordinal = 1
                  oColumn.Flags = 16
                  oColumn.Size = 0
                  oColumn.DataType = 135
                  oColumn.Precision = 0
                  oColumn.NumericScale = 0
                  oColumn.Nullable = False
                  
            oTransformation.SourceColumns.Add oColumn
            Set oColumn = Nothing

            Set oColumn = oTransformation.DestinationColumns.New("D_STANJA" , 1)
                  oColumn.Name = "D_STANJA"
                  oColumn.Ordinal = 1
                  oColumn.Flags = 24
                  oColumn.Size = 0
                  oColumn.DataType = 135
                  oColumn.Precision = 0
                  oColumn.NumericScale = 0
                  oColumn.Nullable = False
                  
            oTransformation.DestinationColumns.Add oColumn
            Set oColumn = Nothing

      Set oTransProps = oTransformation.TransformServerProperties

            
      Set oTransProps = Nothing

      oCustomTask2.Transformations.Add oTransformation
      Set oTransformation = Nothing

End Sub

'------------- define Task_Sub3 for task DTSTask_DTSSendMailTask_1 (Poro&#269;ilo o napaki)
Public Sub Task_Sub3(ByVal goPackage As Object)

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

Dim oCustomTask3 As DTS.SendMailTask
Set oTask = goPackage.Tasks.New("DTSSendMailTask")
oTask.Name = "DTSTask_DTSSendMailTask_1"
Set oCustomTask3 = oTask.CustomTask

      oCustomTask3.Name = "DTSTask_DTSSendMailTask_1"
      oCustomTask3.Description = "Poro&#269;ilo o napaki"
      oCustomTask3.Profile = "Actual"
      oCustomTask3.ToLine = "Grega Jesih"
      oCustomTask3.Subject = "DTS Upload neuspel"
      oCustomTask3.IsNTService = False
      oCustomTask3.SaveMailInSentItemsFolder = True
      
goPackage.Tasks.Add oTask
Set oCustomTask3 = Nothing
Set oTask = Nothing

End Sub

'------------- define Task_Sub4 for task DTSTask_DTSCreateProcessTask_1 (Error report)
Public Sub Task_Sub4(ByVal goPackage As Object)

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

Dim oCustomTask4 As DTS.CreateProcessTask2
Set oTask = goPackage.Tasks.New("DTSCreateProcessTask")
oTask.Name = "DTSTask_DTSCreateProcessTask_1"
Set oCustomTask4 = oTask.CustomTask

      oCustomTask4.Name = "DTSTask_DTSCreateProcessTask_1"
      oCustomTask4.Description = "Error report"
      oCustomTask4.ProcessCommandLine = "c:\winnt\system32\BLAT.EXE -to=grega.jesih@actual-it.si -server=smtp.poslovno.local  -body""blat report of DTS problem"""
      oCustomTask4.SuccessReturnCode = 0
      oCustomTask4.Timeout = 0
      oCustomTask4.TerminateProcessAfterTimeout = False
      oCustomTask4.FailPackageOnTimeout = True
      
goPackage.Tasks.Add oTask
Set oCustomTask4 = Nothing
Set oTask = Nothing

End Sub

'------------- define Task_Sub5 for task DTSTask_DTSExecuteSQLTask_8 (Zapiši napako)
Public Sub Task_Sub5(ByVal goPackage As Object)

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

Dim oCustomTask5 As DTS.ExecuteSQLTask2
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_8"
Set oCustomTask5 = oTask.CustomTask

      oCustomTask5.Name = "DTSTask_DTSExecuteSQLTask_8"
      oCustomTask5.Description = "Zapiši napako"
      oCustomTask5.SQLStatement = "insert ZL_LOG (site,kdaj,proces,status,opis)" & vbCrLf
      oCustomTask5.SQLStatement = oCustomTask5.SQLStatement & "values ('9002',getdate(),'D',1,'Napaka v DTSu')"
      oCustomTask5.ConnectionID = 2
      oCustomTask5.CommandTimeout = 0
      oCustomTask5.OutputAsRecordset = False
      
goPackage.Tasks.Add oTask
Set oCustomTask5 = Nothing
Set oTask = Nothing

End Sub

'------------- define Task_Sub6 for task DTSTask_DTSExecuteSQLTask_1 (Execute SQL Task: undefined)
Public Sub Task_Sub6(ByVal goPackage As Object)

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

Dim oCustomTask6 As DTS.ExecuteSQLTask2
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
oTask.Name = "DTSTask_DTSExecuteSQLTask_1"
Set oCustomTask6 = oTask.CustomTask

      oCustomTask6.Name = "DTSTask_DTSExecuteSQLTask_1"
      oCustomTask6.Description = "Execute SQL Task: undefined"
      oCustomTask6.SQLStatement = "select * from d_s_04O" & vbCrLf
      oCustomTask6.SQLStatement = oCustomTask6.SQLStatement & "select top 1 * from d_o_kk"
      oCustomTask6.ConnectionID = 1
      oCustomTask6.CommandTimeout = 0
      oCustomTask6.OutputAsRecordset = False
      
goPackage.Tasks.Add oTask
Set oCustomTask6 = Nothing
Set oTask = Nothing

End Sub

0
 
SashPCommented:
gregajesih,

Ok your going to need to give me some time to absorb this.
0
 
gregajesihAuthor Commented:
I think there is a bug inside the package. I will try to import it again.
I tried a test branching task and it worked.

..

Hold on, please.

G
0
 
gregajesihAuthor Commented:
I don't get it.

I did a new test DTS with 2 SQL qry steps. 1st on failure goes to error report.
This works.
Then I started designing a clone of my original DTS and it again doesn't work.
Mistery.
If you can take the look on my mega-source (it is not that big in object graphics..)
I will appreciate it. Points increased.

G
0
 
gregajesihAuthor Commented:
Sash, give it a rest.

I choose the alternative approach.
I will end DTS package on 1st error and use sp_OAGetErrorInfo to log the error and branch my queryes.

I am disappointed with DTS designer though. It says A, it does B.

Thanks anyway.

BR
Gregor
0
 
SashPCommented:
Gregor request a refund.  I did not help you.

Cheers Sash
0
 
gregajesihAuthor Commented:
Never mind, Sash.

I might have helped you..so you owe me one. This is a paid service for me.

G
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now