How to integrate old DTS code to SSIS

Question

0.00/5 (No votes)

See more:

I have the code to create DTS pacakge manually which is pretty old and depreciated from sql 2008 R2 onwards. Now i need to support for SQL express 2016.
Can any one help to migrate the code?below is the VB code

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

What I have tried:

i searched to manually convert but am not finding any article to migrate

Posted 12-Jul-18 0:34am

Member 11732139

Updated 12-Jul-18 18:24pm

CHill60

v2

Add a Solution

2 solutions

Solution 2

if it not supported SQL express then how can i integrate my code to support to SQL express

Posted 12-Jul-18 18:24pm

Member 11732139

Comments

CHill60 13-Jul-18 3:38am

I found this response by accident. If you want to respond to someone then use the "Have a Question or Comment?" link next to their post.
If you want your code to support SQL Express then you have to buy the Standard version (or higher) - it will still "support" a database created via SQL Express, although I don't see why you would want to when you will have the full blown version.
If you want all the bells and whistles (such as SSIS, SSRS, SQL Agent) then you have to pay for them

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2018-07-12T06:34:00

There is a series of MS articles on creating SSIS packages programmatically - Building Packages Programmatically | Microsoft Docs[^]

However, you mention using SQL Server Express 2016 - as far as I am aware you will have to purchase the Standard version (or higher) in order to use/develop SSIS packages.