Member 11732139 Ответов: 2

Как интегрировать старый код DTS в SSIS

У меня есть код для создания DTS pacakge вручную, который довольно стар и амортизирован начиная с sql 2008 R2. Теперь мне нужна поддержка SQL express 2016.
Может ли кто - нибудь помочь перенести код?ниже приведен код VB
'#########################################################################
'#
'# Create the package
'#
'#########################################################################
Function CreatePackage(ByRef inServer, _
                     ByRef inDB,  _
                     ByRef strRecordType, _
                     ByRef oPackage, ByRef errMsg)

  Dim rc
  Dim inFullFileName

  On Error Resume Next

  NQExt.QTrace "<<ciscocm::createpackage>> Entry" 

  rc = RET_SUCCESS
  inFullFileName = "dummy_file"
  
  'On Error Resume Next

  '# 
  '# Create DTS Package object
  '#
  Set oPackage = CreateObject("DTS.Package2")
  If (Err.Number <> 0) Then
      rc = RET_ERROR
      errMsg = "Error: CreateObject DTS.Package2. ErrNo=0x"& Hex(Err.Number) & vbCrLf _
               & ": " & Err.Description
  End If

  '# 
  '# Initialize DTS Package object
  '#
  If (rc = RET_SUCCESS) Then
      oPackage.Name = "DTSPackage_Import" & strRecordType
      oPackage.Description = "Import " & strRecordType & " records."
      oPackage.WriteCompletionStatusToNTEventLog = False
      oPackage.FailOnError = False
      oPackage.PackagePriorityClass = 2
      oPackage.MaxConcurrentSteps = 4 
      oPackage.LineageOptions = 0
      oPackage.UseTransaction = True
      oPackage.TransactionIsolationLevel = 4096
      oPackage.AutoCommitTransaction = True
      oPackage.RepositoryMetadataOptions = 0
      oPackage.UseOLEDBServiceComponents = True
      oPackage.LogToSQLServer = False
      oPackage.LogServerFlags = 0
      oPackage.FailPackageOnLogFailure = False
      oPackage.ExplicitGlobalVariables = False
      oPackage.PackageType = 0
  End If

  '# 
  '# Create package connection information.
  '#
  If (rc = RET_SUCCESS) Then
      rc = DoDefineConnection(oPackage, strRecordType, inFullFileName, _
                              inServer, inDB, errMsg)
  End If

  '# 
  '# Create package step information.
  '#
  If (rc = RET_SUCCESS) Then
      rc = DoDefineStep(oPackage, strRecordType, errMsg)
  End If

  '# 
  '# Create package task information.
  '#
  If (rc = RET_SUCCESS) Then
      rc = DoDefineTask(oPackage, strRecordType, inFullFileName, inDB, errMsg)
  End If

  If (Err.Number <> 0) Then
      errMsg = "ErrNo=" & Err.Number & ": " & Err.Description
      Err.Clear
  End If

  CreatePackage = rc

  NQExt.QTrace "<<ciscocm::createpackage>> Exit" 

End Function

'#######################################################################################
'#
'# Define the Connection
'#
'#######################################################################################
Function DoDefineConnection(ByRef oPackage, _
                          ByRef strRecordType, _
                          ByRef inFullFileName, _
                          ByRef inServer, ByRef inDB,  _
                          ByRef errMsg)

  Dim rc
  Dim oConnection

  On Error Resume Next

  NQExt.QTrace "<<ciscocm::dodefineconnection>> Entry" 

  rc = RET_SUCCESS

  '# 
  '# Create source connection object.
  '#
  Set oConnection = oPackage.Connections.New("DTSFlatFile")
  If (Err.Number <> 0) Then
      rc = RET_ERROR
      errMsg = "Error: Creating connection DTSFlatFile. ErrNo=0x"& Hex(Err.Number) & vbCrLf _
               & ": " & Err.Description
  End If

  '# 
  '# Add source properties.
  '#
  If (rc = RET_SUCCESS) Then
      oConnection.ConnectionProperties("Data Source").value = inFullFileName
      oConnection.ConnectionProperties("Mode").value = 1
      oConnection.ConnectionProperties("Row Delimiter").value = vbLf
	     oConnection.ConnectionProperties("File Format").value = 1
	     oConnection.ConnectionProperties("Column Delimiter").value = ","
	     oConnection.ConnectionProperties("File Type").value = 1
	     oConnection.ConnectionProperties("Skip Rows").value = 1
	     oConnection.ConnectionProperties("Text Qualifier").value = """"
	     oConnection.ConnectionProperties("First Row Column Name").value = True
	     oConnection.ConnectionProperties("Number of Column").value = 0
	     oConnection.ConnectionProperties("Max characters per delimited column").value = 8000

      oConnection.Name = strRecordType & "Connection 1"
      oConnection.ID = 1
      oConnection.Reusable = True
      oConnection.ConnectImmediate = False
      oConnection.DataSource = inFullFileName
      oConnection.ConnectionTimeout = 300
      oConnection.UseTrustedConnection = False
      oConnection.UseDSL = False
      
      '# 
      '# Add connection to package.
      '#
      oPackage.Connections.Add oConnection
      Set oConnection = Nothing
  End If

  '# 
  '# Create destination connection object.
  '#
  If (rc = RET_SUCCESS) Then
      Set oConnection = oPackage.Connections.New("SQLOLEDB")
      If (Err.Number <> 0) Then
          rc = RET_ERROR
          errMsg = "Error: Creating connections SQLOLEDB. ErrNo=0x"& Hex(Err.Number) & vbCrLf _
                   & ": " & Err.Description
      End If
  End If

  '# 
  '# Add destination properties.
  '#
  If (rc = RET_SUCCESS) Then
	     oConnection.ConnectionProperties("Integrated Security").value = "SSPI"
	     oConnection.ConnectionProperties("Persist Security Info").value = True
	     oConnection.ConnectionProperties("Initial Catalog").value = inDB
	     oConnection.ConnectionProperties("Data Source").value = inServer
	     'oConnection.ConnectionProperties("Application Name").value = "DTS  Import/Export Wizard"
      oConnection.ConnectionProperties("Application Name").value = "DTS Designer"
      
      oConnection.Name = strRecordType & "Connection 2"
      oConnection.ID = 2
      oConnection.Reusable = True
      oConnection.ConnectImmediate = False
      oConnection.DataSource = inServer
      oConnection.ConnectionTimeout = 300
      oConnection.Catalog = inDB
      oConnection.UseTrustedConnection = True
      oConnection.UseDSL = False
      
      '# 
      '# Add connection to package.
      '#
      oPackage.Connections.Add oConnection
      Set oConnection = Nothing
  End If

  NQExt.QTrace "<<ciscocm::dodefineconnection>> Exit" 

  DoDefineConnection = rc

End Function

'##############################################################################
'#
'# Define the Step
'#
'##############################################################################
Function DoDefineStep(ByRef oPackage, ByRef strRecordType, ByRef errMsg)

  Dim rc
  Dim oStep

  On Error Resume Next
  rc = RET_SUCCESS

  NQExt.QTrace "<<ciscocm::dodefinestep>> Entry" 

  If (rc = RET_SUCCESS) Then
      Set oStep = oPackage.Steps.New
      If (Err.Number <> 0) Then
          rc = RET_ERROR
          errMsg = "Error: Creating package step. ErrNo=0x"& Hex(Err.Number) & vbCrLf _
                   & ": " & Err.Description
      End If
  End If
  
  If (rc = RET_SUCCESS) Then
      oStep.Name = "DTSStep_" & strRecordType & "DataPumpTask"
      oStep.Description = strRecordType & " Transfer"
      oStep.ExecutionStatus = DTSStepExecStat_Waiting
      oStep.TaskName = "DTSTask_" & strRecordType & "DataPumpTask"
      oStep.CommitSuccess = False
      oStep.RollbackFailure = False
      oStep.ScriptLanguage = "VBScript"
      oStep.AddGlobalVariables = True
      oStep.RelativePriority = 3
      oStep.CloseConnection = False 
      oStep.ExecuteInMainThread = True
      oStep.IsPackageDSORowset = False
      oStep.JoinTransactionIfPresent = False
      oStep.DisableStep = False
      oStep.FailPackageOnError = False

      oPackage.Steps.Add oStep
      Set oStep = Nothing
  End If

  NQExt.QTrace "<<ciscocm::dodefinestep>> Exit" 

  DoDefineStep = rc

End Function

'###################################################################################
'#
'# Define the Task
'#
'###################################################################################
Function DoDefineTask(ByRef oPackage, ByRef strRecordType, ByRef inFullFileName, _
                    ByRef inDB, ByRef errMsg)

  Dim rc
  Dim oTask 
  Dim oLookup 
  Dim oCustomTask 

  NQExt.QTrace "<<ciscocm::dodefinetask>> Entry" 

  On Error Resume Next
  rc = RET_SUCCESS
     
  Set oTask = oPackage.Tasks.New("DTSDataPumpTask")
  If (Err.Number <> 0) Then
      rc = RET_ERROR
      errMsg = "Error: Creating DTSDataPumpTask. ErrNo=0x"& Hex(Err.Number) & vbCrLf _
               & ": " & Err.Description
  End If
  
  If (rc = RET_SUCCESS) Then
      oTask.Name = "DTSTask_" & strRecordType & "DataPumpTask"
      Set oCustomTask = oTask.CustomTask

      oCustomTask.Name = "DTSTask_" & strRecordType & "DataPumpTask"
      oCustomTask.Description = strRecordType & " Transfer"
      oCustomTask.SourceConnectionID = 1
      oCustomTask.SourceObjectName = inFullFileName
      oCustomTask.DestinationConnectionID = 2
      oCustomTask.DestinationObjectName = "[" & inDB & "].[dbo].[" & "Stage" & strRecordType & "]"
      oCustomTask.ProgressRowCount = 1000
      oCustomTask.MaximumErrorCount = 0
      oCustomTask.FetchBufferSize = 1
      oCustomTask.UseFastLoad = True
      oCustomTask.InsertCommitSize = 0
      oCustomTask.ExceptionFileColumnDelimiter = "|"
      oCustomTask.ExceptionFileRowDelimiter = vbCrLf
      oCustomTask.AllowIdentityInserts = False
      oCustomTask.FirstRow = 0
      oCustomTask.LastRow = 0
      oCustomTask.FastLoadOptions = 2
      oCustomTask.ExceptionFileOptions = 1
      oCustomTask.DataPumpOptions = 0

  End If

  If (StrComp(strRecordType, "CDR", vbTextCompare) = 0) Then
      rc = DoDefineCDRCustomTransform(oCustomTask, errMsg)
  Else 
      rc = DoDefineCMRCustomTransform(oCustomTask, errMsg)
  End If

  If (rc = RET_SUCCESS) Then
      oPackage.Tasks.Add oTask
  End If
    
  Set oCustomTask = Nothing
  Set oTask = Nothing

  DoDefineTask = rc

  NQExt.QTrace "<<ciscocm::dodefinetask>> Exit" 

End Function

'##############################################################################
'#
'# Define the Custom Transformation for the CDR Staging Table.
'#
'##############################################################################
Function DoDefineCDRCustomTransform(ByRef oCustomTask, ByRef errMsg)

  Dim rc
  Dim oTransformation 
  Dim oTransProps
  Dim oColumn

  On Error Resume Next

  NQExt.QTrace "<<ciscocm::dodefinecdrcustomtransform>> Entry" 

  rc = RET_SUCCESS
     
  Set oTransformation = oCustomTask.Transformations.New("DTSPump.DataPumpTransformCopy")
  oTransformation.Name = "CopyColumns"
  oTransformation.TransformFlags = 63
  oTransformation.ForceSourceBlobsBuffered = 0
  oTransformation.ForceBlobsInMemory = False
  oTransformation.InMemoryBlobSize = 1048576
  oTransformation.TransformPhases = 4

  ' Source Column 1  
  Set oColumn = oTransformation.SourceColumns.New("pkid" , 1)
  oColumn.Name = "pkid"
  oColumn.Ordinal = 1
  oColumn.Flags = 8
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = False
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 2
  Set oColumn = oTransformation.SourceColumns.New("globalCallID_callManagerId" , 2)
  oColumn.Name = "globalCallID_callManagerId"
  oColumn.Ordinal = 2
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True       
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 3
  Set oColumn = oTransformation.SourceColumns.New("globalCallID_callId" , 3)
  oColumn.Name = "globalCallID_callId"
  oColumn.Ordinal = 3
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 4
  Set oColumn = oTransformation.SourceColumns.New("origLegCallIdentifier" , 4)
  oColumn.Name = "origLegCallIdentifier"
  oColumn.Ordinal = 4
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True        
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 5
  Set oColumn = oTransformation.SourceColumns.New("dateTimeOrigination" , 5)
  oColumn.Name = "dateTimeOrigination"
  oColumn.Ordinal = 5
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 6
  Set oColumn = oTransformation.SourceColumns.New("origNodeId" , 6)
  oColumn.Name = "origNodeId"
  oColumn.Ordinal = 6
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 7
  Set oColumn = oTransformation.SourceColumns.New("origSpan" , 7)
  oColumn.Name = "origSpan"
  oColumn.Ordinal = 7
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 8
  Set oColumn = oTransformation.SourceColumns.New("origIpAddr" , 8)
  oColumn.Name = "origIpAddr"
  oColumn.Ordinal = 8
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 9
  Set oColumn = oTransformation.SourceColumns.New("callingPartyNumber" , 9)
  oColumn.Name = "callingPartyNumber"
  oColumn.Ordinal = 9
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 10
  Set oColumn = oTransformation.SourceColumns.New("origCause_value" , 10)
  oColumn.Name = "origCause_value"
  oColumn.Ordinal = 10
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 11
  Set oColumn = oTransformation.SourceColumns.New("origMediaTransportAddress_IP" , 11)
  oColumn.Name = "origMediaTransportAddress_IP"
  oColumn.Ordinal = 11
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 12
  Set oColumn = oTransformation.SourceColumns.New("origMediaTransportAddress_Port" , 12)
  oColumn.Name = "origMediaTransportAddress_Port"
  oColumn.Ordinal = 12
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 13
  Set oColumn = oTransformation.SourceColumns.New("origMediaCap_payloadCapability" , 13)
  oColumn.Name = "origMediaCap_payloadCapability"
  oColumn.Ordinal = 13
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 14
  Set oColumn = oTransformation.SourceColumns.New("origVideoTransportAddress_IP" , 14)
  oColumn.Name = "origVideoTransportAddress_IP"
  oColumn.Ordinal = 14
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 15
  Set oColumn = oTransformation.SourceColumns.New("origVideoTransportAddress_Port" , 15)
  oColumn.Name = "origVideoTransportAddress_Port"
  oColumn.Ordinal = 15
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 16
  Set oColumn = oTransformation.SourceColumns.New("destLegIdentifier" , 16)
  oColumn.Name = "destLegIdentifier"
  oColumn.Ordinal = 16
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 17
  Set oColumn = oTransformation.SourceColumns.New("destNodeId" , 17)
  oColumn.Name = "destNodeId"
  oColumn.Ordinal = 17
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 18
  Set oColumn = oTransformation.SourceColumns.New("destSpan" , 18)
  oColumn.Name = "destSpan"
  oColumn.Ordinal = 18
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 19
  Set oColumn = oTransformation.SourceColumns.New("destIpAddr" , 19)
  oColumn.Name = "destIpAddr"
  oColumn.Ordinal = 19
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 20
  Set oColumn = oTransformation.SourceColumns.New("originalCalledPartyNumber" , 20)
  oColumn.Name = "originalCalledPartyNumber"
  oColumn.Ordinal = 20
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 21
  Set oColumn = oTransformation.SourceColumns.New("finalCalledPartyNumber" , 21)
  oColumn.Name = "finalCalledPartyNumber"
  oColumn.Ordinal = 21
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 22
  Set oColumn = oTransformation.SourceColumns.New("destCause_value" , 22)
  oColumn.Name = "destCause_value"
  oColumn.Ordinal = 22
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 23
  Set oColumn = oTransformation.SourceColumns.New("destMediaTransportAddress_IP" , 23)
  oColumn.Name = "destMediaTransportAddress_IP"
  oColumn.Ordinal = 23
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 24
  Set oColumn = oTransformation.SourceColumns.New("destMediaTransportAddress_Port" , 24)
  oColumn.Name = "destMediaTransportAddress_Port"
  oColumn.Ordinal = 24
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

   ' Source Column 25
  Set oColumn = oTransformation.SourceColumns.New("destMediaCap_payloadCapability" , 25)
  oColumn.Name = "destMediaCap_payloadCapability"
  oColumn.Ordinal = 25
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

   ' Source Column 26
  Set oColumn = oTransformation.SourceColumns.New("destVideoTransportAddress_IP" , 26)
  oColumn.Name = "destVideoTransportAddress_IP"
  oColumn.Ordinal = 26
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

   ' Source Column 27
  Set oColumn = oTransformation.SourceColumns.New("destVideoTransportAddress_Port" , 27)
  oColumn.Name = "destVideoTransportAddress_Port"
  oColumn.Ordinal = 27
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 28
  Set oColumn = oTransformation.SourceColumns.New("dateTimeConnect" , 28)
  oColumn.Name = "dateTimeConnect"
  oColumn.Ordinal = 28
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 29
  Set oColumn = oTransformation.SourceColumns.New("dateTimeDisconnect" , 29)
  oColumn.Name = "dateTimeDisconnect"
  oColumn.Ordinal = 29
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 30
  Set oColumn = oTransformation.SourceColumns.New("originalCalledPartyNumberPartition" , 30)
  oColumn.Name = "originalCalledPartyNumberPartition"
  oColumn.Ordinal = 30
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 31
  Set oColumn = oTransformation.SourceColumns.New("callingPartyNumberPartition" , 31)
  oColumn.Name = "callingPartyNumberPartition"
  oColumn.Ordinal = 31
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 32
  Set oColumn = oTransformation.SourceColumns.New("finalCalledPartyNumberPartition" , 32)
  oColumn.Name = "finalCalledPartyNumberPartition"
  oColumn.Ordinal = 32
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 33
  Set oColumn = oTransformation.SourceColumns.New("duration" , 33)
  oColumn.Name = "duration"
  oColumn.Ordinal = 33
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 34
  Set oColumn = oTransformation.SourceColumns.New("origDeviceName" , 34)
  oColumn.Name = "origDeviceName"
  oColumn.Ordinal = 34
  oColumn.Flags = 104
  oColumn.Size = 129
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 35
  Set oColumn = oTransformation.SourceColumns.New("destDeviceName" , 35)
  oColumn.Name = "destDeviceName"
  oColumn.Ordinal = 35
  oColumn.Flags = 104
  oColumn.Size = 129
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 36
  Set oColumn = oTransformation.SourceColumns.New("destConversationId" , 36)
  oColumn.Name = "destConversationId"
  oColumn.Ordinal = 36
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 37
  Set oColumn = oTransformation.SourceColumns.New("comment" , 37)
  oColumn.Name = "comment"
  oColumn.Ordinal = 37
  oColumn.Flags = 104
  oColumn.Size = 2048
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 38
  Set oColumn = oTransformation.SourceColumns.New("authCodeDescription" , 38)
  oColumn.Name = "authCodeDescription"
  oColumn.Ordinal = 38
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 39
  Set oColumn = oTransformation.SourceColumns.New("authorizationLevel" , 39)
  oColumn.Name = "authorizationLevel"
  oColumn.Ordinal = 39
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 40
  Set oColumn = oTransformation.SourceColumns.New("clientMatterCode" , 40)
  oColumn.Name = "clientMatterCode"
  oColumn.Ordinal = 40
  oColumn.Flags = 104
  oColumn.Size = 32
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing


  ' Destination Column 1
  Set oColumn = oTransformation.DestinationColumns.New("pkid" , 1)
  oColumn.Name = "pkid"
  oColumn.Ordinal = 1
  oColumn.Flags = 8
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = False
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing

  ' Destination Column 2
  Set oColumn = oTransformation.DestinationColumns.New("globalCallID_callManagerId" , 2)
  oColumn.Name = "globalCallID_callManagerId"
  oColumn.Ordinal = 2
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing

  ' Destination Column 3
  Set oColumn = oTransformation.DestinationColumns.New("globalCallID_callId" , 3)
  oColumn.Name = "globalCallID_callId"
  oColumn.Ordinal = 3
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing

  ' Destination Column 4
  Set oColumn = oTransformation.DestinationColumns.New("origLegCallIdentifier" , 4)
  oColumn.Name = "origLegCallIdentifier"
  oColumn.Ordinal = 4
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing

  ' Destination Column 5
  Set oColumn = oTransformation.DestinationColumns.New("dateTimeOrigination" , 5)
  oColumn.Name = "dateTimeOrigination"
  oColumn.Ordinal = 5
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing

  ' Destination Column 6
  Set oColumn = oTransformation.DestinationColumns.New("origNodeId" , 6)
  oColumn.Name = "origNodeId"
  oColumn.Ordinal = 6
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 7
  Set oColumn = oTransformation.DestinationColumns.New("origSpan" , 7)
  oColumn.Name = "origSpan"
  oColumn.Ordinal = 7
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 8
  Set oColumn = oTransformation.DestinationColumns.New("origIpAddr" , 8)
  oColumn.Name = "origIpAddr"
  oColumn.Ordinal = 8
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 9
  Set oColumn = oTransformation.DestinationColumns.New("callingPartyNumber" , 9)
  oColumn.Name = "callingPartyNumber"
  oColumn.Ordinal = 9
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 10
  Set oColumn = oTransformation.DestinationColumns.New("origCause_value" , 10)
  oColumn.Name = "origCause_value"
  oColumn.Ordinal = 10
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 11
  Set oColumn = oTransformation.DestinationColumns.New("origMediaTransportAddress_IP" , 11)
  oColumn.Name = "origMediaTransportAddress_IP"
  oColumn.Ordinal = 11
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 12
  Set oColumn = oTransformation.DestinationColumns.New("origMediaTransportAddress_Port" , 12)
  oColumn.Name = "origMediaTransportAddress_Port"
  oColumn.Ordinal = 12
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 13
  Set oColumn = oTransformation.DestinationColumns.New("origMediaCap_payloadCapability" , 13)
  oColumn.Name = "origMediaCap_payloadCapability"
  oColumn.Ordinal = 13
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 14
  Set oColumn = oTransformation.DestinationColumns.New("origVideoTransportAddress_IP" , 14)
  oColumn.Name = "origVideoTransportAddress_IP"
  oColumn.Ordinal = 14
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 15
  Set oColumn = oTransformation.DestinationColumns.New("origVideoTransportAddress_Port" , 15)
  oColumn.Name = "origVideoTransportAddress_Port"
  oColumn.Ordinal = 15
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 16
  Set oColumn = oTransformation.DestinationColumns.New("destLegIdentifier" , 16)
  oColumn.Name = "destLegIdentifier"
  oColumn.Ordinal = 16
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 17
  Set oColumn = oTransformation.DestinationColumns.New("destNodeId" , 17)
  oColumn.Name = "destNodeId"
  oColumn.Ordinal = 17
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 18
  Set oColumn = oTransformation.DestinationColumns.New("destSpan" , 18)
  oColumn.Name = "destSpan"
  oColumn.Ordinal = 18
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 19
  Set oColumn = oTransformation.DestinationColumns.New("destIpAddr" , 19)
  oColumn.Name = "destIpAddr"
  oColumn.Ordinal = 19
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 20
  Set oColumn = oTransformation.DestinationColumns.New("originalCalledPartyNumber" , 20)
  oColumn.Name = "originalCalledPartyNumber"
  oColumn.Ordinal = 20
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 21
  Set oColumn = oTransformation.DestinationColumns.New("finalCalledPartyNumber" , 21)
  oColumn.Name = "finalCalledPartyNumber"
  oColumn.Ordinal = 21
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 22
  Set oColumn = oTransformation.DestinationColumns.New("destCause_value" , 22)
  oColumn.Name = "destCause_value"
  oColumn.Ordinal = 22
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 23
  Set oColumn = oTransformation.DestinationColumns.New("destMediaTransportAddress_IP" , 23)
  oColumn.Name = "destMediaTransportAddress_IP"
  oColumn.Ordinal = 23
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 24
  Set oColumn = oTransformation.DestinationColumns.New("destMediaTransportAddress_Port" , 24)
  oColumn.Name = "destMediaTransportAddress_Port"
  oColumn.Ordinal = 24
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 25
  Set oColumn = oTransformation.DestinationColumns.New("destMediaCap_payloadCapability" , 25)
  oColumn.Name = "destMediaCap_payloadCapability"
  oColumn.Ordinal = 25
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 26
  Set oColumn = oTransformation.DestinationColumns.New("destVideoTransportAddress_IP" , 26)
  oColumn.Name = "destVideoTransportAddress_IP"
  oColumn.Ordinal = 26
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 27
  Set oColumn = oTransformation.DestinationColumns.New("destVideoTransportAddress_Port" , 27)
  oColumn.Name = "destVideoTransportAddress_Port"
  oColumn.Ordinal = 27
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 28
  Set oColumn = oTransformation.DestinationColumns.New("dateTimeConnect" , 28)
  oColumn.Name = "dateTimeConnect"
  oColumn.Ordinal = 28
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 29
  Set oColumn = oTransformation.DestinationColumns.New("dateTimeDisconnect" , 29)
  oColumn.Name = "dateTimeDisconnect"
  oColumn.Ordinal = 29
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 30
  Set oColumn = oTransformation.DestinationColumns.New("originalCalledPartyNumberPartition" , 30)
  oColumn.Name = "originalCalledPartyNumberPartition"
  oColumn.Ordinal = 30
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 31
  Set oColumn = oTransformation.DestinationColumns.New("callingPartyNumberPartition" , 31)
  oColumn.Name = "callingPartyNumberPartition"
  oColumn.Ordinal = 31
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 32
  Set oColumn = oTransformation.DestinationColumns.New("finalCalledPartyNumberPartition" , 32)
  oColumn.Name = "finalCalledPartyNumberPartition"
  oColumn.Ordinal = 32
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 33
  Set oColumn = oTransformation.DestinationColumns.New("duration" , 33)
  oColumn.Name = "duration"
  oColumn.Ordinal = 33
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 34
  Set oColumn = oTransformation.DestinationColumns.New("origDeviceName" , 34)
  oColumn.Name = "origDeviceName"
  oColumn.Ordinal = 34
  oColumn.Flags = 104
  oColumn.Size = 129
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 35
  Set oColumn = oTransformation.DestinationColumns.New("destDeviceName" , 35)
  oColumn.Name = "destDeviceName"
  oColumn.Ordinal = 35
  oColumn.Flags = 104
  oColumn.Size = 129
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 36
  Set oColumn = oTransformation.DestinationColumns.New("destConversationId" , 36)
  oColumn.Name = "destConversationId"
  oColumn.Ordinal = 36
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 37
  Set oColumn = oTransformation.DestinationColumns.New("comment" , 37)
  oColumn.Name = "comment"
  oColumn.Ordinal = 37
  oColumn.Flags = 104
  oColumn.Size = 2048
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 38
  Set oColumn = oTransformation.DestinationColumns.New("authCodeDescription" , 38)
  oColumn.Name = "authCodeDescription"
  oColumn.Ordinal = 38
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 39
  Set oColumn = oTransformation.DestinationColumns.New("authorizationLevel" , 39)
  oColumn.Name = "authorizationLevel"
  oColumn.Ordinal = 39
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 40
  Set oColumn = oTransformation.DestinationColumns.New("clientMatterCode" , 40)
  oColumn.Name = "clientMatterCode"
  oColumn.Ordinal = 40
  oColumn.Flags = 104
  oColumn.Size = 32
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' No transformation Properties
  Set oTransProps = oTransformation.TransformServerProperties
  Set oTransProps = Nothing

  If (Err.Number <> 0) Then
      rc = RET_ERROR
      errMsg = "Error: Creating Custom Transformation. ErrNo=0x"& Hex(Err.Number) & vbCrLf _
               & Err.Description
  End If
  
  If (rc = RET_SUCCESS) Then
      oCustomTask.Transformations.Add oTransformation
  End If
  
  Set oTransformation = Nothing

  NQExt.QTrace "<<ciscocm::dodefinecdrcustomtransform>> Exit" 

  DoDefineCDRCustomTransform = rc

End Function

'##############################################################################
'#
'# Define the Custom Transformation for the CMR Staging Table.
'#
'##############################################################################
Function DoDefineCMRCustomTransform(ByRef oCustomTask, ByRef errMsg)

  Dim rc
  Dim oTransformation 
  Dim oTransProps
  Dim oColumn

  On Error Resume Next

  NQExt.QTrace "<<ciscocm::dodefinecmrcustomtransform>> Entry" 

  rc = RET_SUCCESS 
    
  Set oTransformation = oCustomTask.Transformations.New("DTSPump.DataPumpTransformCopy")
  oTransformation.Name = "CopyColumns"
  oTransformation.TransformFlags = 63
  oTransformation.ForceSourceBlobsBuffered = 0
  oTransformation.ForceBlobsInMemory = False
  oTransformation.InMemoryBlobSize = 1048576
  oTransformation.TransformPhases = 4

  ' Source Column 1  
  Set oColumn = oTransformation.SourceColumns.New("pkid" , 1)
  oColumn.Name = "pkid"
  oColumn.Ordinal = 1
  oColumn.Flags = 8
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = False
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing
     
  ' Source Column 2  
  Set oColumn = oTransformation.SourceColumns.New("globalCallID_callManagerId" , 2)
  oColumn.Name = "globalCallID_callManagerId"
  oColumn.Ordinal = 2
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 3
  Set oColumn = oTransformation.SourceColumns.New("globalCallID_callId" , 3)
  oColumn.Name = "globalCallID_callId"
  oColumn.Ordinal = 3
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True         
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 4
  Set oColumn = oTransformation.SourceColumns.New("nodeId" , 4)
  oColumn.Name = "nodeId"
  oColumn.Ordinal = 4
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True        
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 5
  Set oColumn = oTransformation.SourceColumns.New("directoryNum" , 5)
  oColumn.Name = "directoryNum"
  oColumn.Ordinal = 5
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 6
  Set oColumn = oTransformation.SourceColumns.New("callIdentifier" , 6)
  oColumn.Name = "callIdentifier"
  oColumn.Ordinal = 6
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 7
  Set oColumn = oTransformation.SourceColumns.New("dateTimeStamp" , 7)
  oColumn.Name = "dateTimeStamp"
  oColumn.Ordinal = 7
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 8
  Set oColumn = oTransformation.SourceColumns.New("numberPacketsSent" , 8)
  oColumn.Name = "numberPacketsSent"
  oColumn.Ordinal = 8
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 9
  Set oColumn = oTransformation.SourceColumns.New("numberPacketsReceived" , 9)
  oColumn.Name = "numberPacketsReceived"
  oColumn.Ordinal = 9
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 10
  Set oColumn = oTransformation.SourceColumns.New("numberPacketsLost" , 10)
  oColumn.Name = "numberPacketsLost"
  oColumn.Ordinal = 10
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 11
  Set oColumn = oTransformation.SourceColumns.New("jitter" , 11)
  oColumn.Name = "jitter"
  oColumn.Ordinal = 11
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 12
  Set oColumn = oTransformation.SourceColumns.New("latency" , 12)
  oColumn.Name = "latency"
  oColumn.Ordinal = 12
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 13
  Set oColumn = oTransformation.SourceColumns.New("directoryNumPartition" , 12)
  oColumn.Name = "directoryNumPartition"
  oColumn.Ordinal = 12
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 14
  Set oColumn = oTransformation.SourceColumns.New("deviceName" , 12)
  oColumn.Name = "deviceName"
  oColumn.Ordinal = 12
  oColumn.Flags = 104
  oColumn.Size = 129
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

  ' Source Column 15
  Set oColumn = oTransformation.SourceColumns.New("varVQMetrics" , 15)
  oColumn.Name = "varVQMetrics"
  oColumn.Ordinal = 15
  oColumn.Flags = 104
  oColumn.Size = 600
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.SourceColumns.Add oColumn
  Set oColumn = Nothing

   ' Destination Column 1
  Set oColumn = oTransformation.DestinationColumns.New("pkid" , 1)
  oColumn.Name = "pkid"
  oColumn.Ordinal = 1
  oColumn.Flags = 8
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = False
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing
        
  ' Destination Column 2
  Set oColumn = oTransformation.DestinationColumns.New("globalCallID_callManagerId" , 2)
  oColumn.Name = "globalCallID_callManagerId"
  oColumn.Ordinal = 2
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True       
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing

  ' Destination Column 3
  Set oColumn = oTransformation.DestinationColumns.New("globalCallID_callId" , 3)
  oColumn.Name = "globalCallID_callId"
  oColumn.Ordinal = 3
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing

  ' Destination Column 4
  Set oColumn = oTransformation.DestinationColumns.New("nodeId" , 4)
  oColumn.Name = "nodeId"
  oColumn.Ordinal = 4
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing

  ' Destination Column 5
  Set oColumn = oTransformation.DestinationColumns.New("directoryNum" , 5)
  oColumn.Name = "directoryNum"
  oColumn.Ordinal = 5
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing


  ' Destination Column 6
  Set oColumn = oTransformation.DestinationColumns.New("callIdentifier" , 6)
  oColumn.Name = "callIdentifier"
  oColumn.Ordinal = 6
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn

  ' Destination Column 7
  Set oColumn = oTransformation.DestinationColumns.New("dateTimeStamp" , 7)
  oColumn.Name = "dateTimeStamp"
  oColumn.Ordinal = 7
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing

  ' Destination Column 8
  Set oColumn = oTransformation.DestinationColumns.New("numberPacketsSent" , 8)
  oColumn.Name = "numberPacketsSent"
  oColumn.Ordinal = 8
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing

  ' Destination Column 9
  Set oColumn = oTransformation.DestinationColumns.New("numberPacketsReceived" , 9)
  oColumn.Name = "numberPacketsReceived"
  oColumn.Ordinal = 9
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing

  ' Destination Column 10
  Set oColumn = oTransformation.DestinationColumns.New("numberPacketsLost" , 10)
  oColumn.Name = "numberPacketsLost"
  oColumn.Ordinal = 10
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing

  ' Destination Column 11
  Set oColumn = oTransformation.DestinationColumns.New("jitter" , 11)
  oColumn.Name = "jitter"
  oColumn.Ordinal = 11
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing

  ' Destination Column 12
  Set oColumn = oTransformation.DestinationColumns.New("latency" , 12)
  oColumn.Name = "latency"
  oColumn.Ordinal = 12
  oColumn.Flags = 104
  oColumn.Size = 0
  oColumn.DataType = 3
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing

  ' Destination Column 13
  Set oColumn = oTransformation.DestinationColumns.New("directoryNumPartition" , 13)
  oColumn.Name = "directoryNumPartition"
  oColumn.Ordinal = 13
  oColumn.Flags = 104
  oColumn.Size = 50
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing

  ' Destination Column 14
  Set oColumn = oTransformation.DestinationColumns.New("deviceName" , 14)
  oColumn.Name = "deviceName"
  oColumn.Ordinal = 14
  oColumn.Flags = 104
  oColumn.Size = 129
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing

  ' Destination Column 15
  Set oColumn = oTransformation.DestinationColumns.New("varVQMetrics" , 15)
  oColumn.Name = "varVQMetrics"
  oColumn.Ordinal = 15
  oColumn.Flags = 104
  oColumn.Size = 600
  oColumn.DataType = 129
  oColumn.Precision = 0
  oColumn.NumericScale = 0
  oColumn.Nullable = True  
  oTransformation.DestinationColumns.Add oColumn
  Set oColumn = Nothing

  ' No transformation Properties
  Set oTransProps = oTransformation.TransformServerProperties
  Set oTransProps = Nothing

  If (Err.Number <> 0) Then
      rc = RET_ERROR
      errMsg = "Error: Creating Custom Transformation. ErrNo=0x"& Hex(Err.Number) & vbCrLf _
               & ": " & Err.Description
  End If
  
  If (rc = RET_SUCCESS) Then
      oCustomTask.Transformations.Add oTransformation
  End If
  
  Set oTransformation = Nothing

  NQExt.QTrace "<<ciscocm::dodefinecmrcustomtransform>> Exit" 

  DoDefineCMRCustomTransform = rc

End Function

'##############################################################################
'#
'# Error reporting using step.GetExecutionErrorInfo after execution
'#
'##############################################################################
Public Sub tracePackageError(oPackage, errMsg)

  Dim oStep

  On Error Resume Next

  For Each oStep in oPackage.Steps
      If (oStep.ExecutionResult = DTSStepExecResult_Failure) Then
          errMsg = errMsg & oPackage.Steps(i).Name & " failed" 
      End If
  Next

  Set oStep = Nothing

End Sub

'##############################################################################
'#
'# ExecutePackage
'#
'##############################################################################
Function ExecutePackage(oPackage, strRecordType, strFileName, errMsg)

  Dim rc
  Dim i
  Dim tempErrMsg
  
  On Error Resume Next

  rc         = RET_SUCCESS
  tempErrMsg = ""
  Err.Clear

  If (oPackage Is Nothing) Then
      rc = RET_ERROR
      errMsg = errMsg & strRecordType & " DTS Package not created"
  End If

  '# 
  '# Set source file.
  '#    
  Dim oConection
  Set oConection = oPackage.Connections(strRecordType & "Connection 1")

  If (Err.Number <> 0) Then
      rc = RET_ERROR
      errMsg = errMsg & "Error: ProcessCDRFile." & vbCrLf &_
               "ErrNo=0x" & Hex(Err.Number) & ": " & Err.Description
  Else
      oConection.DataSource = strFileName
      oConection.ConnectionProperties("Data Source").value = strFileName
  End If

  '# 
  '# Make sure step state is waiting (necessary for loops).
  '#
  Dim oStep
	 Set oStep = oPackage.Steps("DTSStep_" & strRecordType & "DataPumpTask")

  If (Err.Number <> 0) Then
      rc = RET_ERROR
      errMsg = errMsg & "Error setting Step ExecutionStatus. " & vbCrLf &_ 
               "ErrNo=0x" & Hex(Err.Number) & ": " & Err.Description
  Else
      oStep.ExecutionStatus = DTSStepExecStat_Waiting
      oStep.DisableStep = False
  End If

  '# 
  '# Execute package now.
  '#
  If (rc = RET_SUCCESS) Then
      oPackage.Execute
      tracePackageError oPackage, tempErrMsg
  End If

  If (Err.Number <> 0) Or (StrComp(tempErrMsg, "", vbTextCompare) <> 0) Then
      rc = RET_ERROR
      tempErrMsg = "Error executing DTS package for file " & strFileName & vbCrLf &_ 
                   tempErrMsg & vbCrLf &_ 
                   "ErrNo=0x"& Hex(Err.Number) & ": " & vbCrLf & Err.Description
      NQExt.QTrace tempErrMsg
  End If

  ExecutePackage = rc

End Function


Что я уже пробовал:

я искал, чтобы вручную конвертировать, но не нашел ни одной статьи для миграции

2 Ответов

Рейтинг:
20

CHill60

Есть целая серия МС: статьи о создании пакетов служб SSIS программно - Создание Пакетов Программно | Microsoft Docs[^]

Однако вы упомянули об использовании SQL Server Экспресс 2016 - насколько мне известно, вам придется приобрести стандартную версию (или выше), чтобы использовать/разрабатывать пакеты SSIS.

Рейтинг:
1

Member 11732139

если он не поддерживает SQL express то как я могу интегрировать свой код для поддержки SQL express

CHill60

Я нашел этот ответ случайно. Если вы хотите ответить кому-то, то используйте "есть вопрос или комментарий?" ссылка рядом с их постом.
Если вы хотите, чтобы ваш код поддерживал SQL Express, то вам нужно купить стандартную версию (или выше) - она все равно будет "поддерживать" базу данных, созданную с помощью SQL Express, хотя я не понимаю, зачем вам это нужно, когда у вас будет полномасштабная версия.
Если вам нужны все колокола и свистки (такие как SSIS, SSRS, SQL Agent), то вы должны заплатить за них