Dear Experts,
This is my scenario:

an excel file gets mailed to me every morning with transactions from a fuel
dispencing unit. This file has the following fields:

Record
Date
Time
Hose No.
Fuel Type
Volume
Vehicle
User No.
User
Odometer
E. Hours
Price
PPV
Mode



Record = unique transaction number, so that I can use as the primary key.

What has to happen:

When the employee get the file in his mail, he then saves this excel file to
a directory on the sql server. At a certain time that I schedule the dts to
run, dts looks if there is an excel file in the directory, if there is,
opens the file, and imports it into a temporary table (the reason to a temp
table first is because the date & time fields has to be combined first to
generate one DateTime field, in other words, from 22/02/2004 and 13:00 to
22/02/2004 13:00)

Then this temp data gets transferred to the main table, with the dates
converted and then the temp table gets cleaned out. Then, this file has to
be moved to a archive directory, so that the directory is clean for the
employee for tomorrow's file.

The whole DTS package works fine up to the point of extracting the
information to the main folder, but what I want to add on is:

1: The mailed file might not always have exactly the same filename, is it
possible to specify something like *.xls as the source meaning any xls file
in that folder needs to be imported?

2: What if there are duplicate (record field) records which I already have,
(because this file is handled by a human before sent to me, this is
possible) How can I tell my dts to just ignore the duplicates, and import
those which are not duplicates, because if I try to run the DTS again, this
fails the package.

3: How do I Move the file, from the one directory to the other? and can I
let it create a filename with the date because most of the time, this
filename will stay the same, and I do not want the system to overwrite the
previous days file.

This is what my DTS package looks like as current:

Thanks alot in advance, I've been batteling alot with this, thanks

Regards
Rudi Groenewald

'************************************************* ***************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: C:\Documents and Settings\RGroenewald\Desktop\AFSFileGobbler.bas
'Package Name: AFSFileGobbler
'Package Description:
'Generated Date: 2004/10/19
'Generated Time: 10:53:45 AM
'************************************************* ***************

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

goPackage.Name = "AFSFileGobbler"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = True
goPackage.LogServerName = "(local)"
goPackage.LogServerUserName = "sa"
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0



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

Dim oConnection as DTS.Connection2

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

Set oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0 ")

oConnection.ConnectionProperties("User ID") = "Admin"
oConnection.ConnectionProperties("Data Source") = "C:\Documents and
Settings\RGroenewald\Desktop\RNI.XLS"
oConnection.ConnectionProperties("Extended Properties") = "Excel
8.0;HDR=YES;"

oConnection.Name = "Microsoft Excel 97-2000"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "C:\Documents and
Settings\RGroenewald\Desktop\RNI.XLS"
oConnection.UserID = "Admin"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False

'If you have a password for this connection, please uncomment and add your
password below.
'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

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

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

oConnection.ConnectionProperties("Integrated Security") = "SSPI"
oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("Initial Catalog") = "MSTRIBOLOGY"
oConnection.ConnectionProperties("Data Source") = "(local)"
oConnection.ConnectionProperties("Application Name") = "DTS Designer"

oConnection.Name = "ZASLDSQL001"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "MSTRIBOLOGY"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False

'If you have a password for this connection, please uncomment and add your
password below.
'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

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

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

oConnection.ConnectionProperties("Integrated Security") = "SSPI"
oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("Initial Catalog") = "MSTRIBOLOGY"
oConnection.ConnectionProperties("Data Source") = "(local)"
oConnection.ConnectionProperties("Application Name") = "DTS Designer"

oConnection.Name = "ZASLDSQL001 Temp"
oConnection.ID = 3
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "MSTRIBOLOGY"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False

'If you have a password for this connection, please uncomment and add your
password below.
'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

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

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

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

Set oStep = goPackage.Steps.New

oStep.Name = "DTSStep_DTSDataPumpTask_1"
oStep.Description = "Excel to TempTable"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSDataPumpTask_1"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = True
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

goPackage.Steps.Add oStep
Set oStep = Nothing

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

Set oStep = goPackage.Steps.New

oStep.Name = "DTSStep_DTSDataPumpTask_2"
oStep.Description = "Transform Data Task: undefined"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSDataPumpTask_2"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

goPackage.Steps.Add oStep
Set oStep = Nothing

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

Set oStep = goPackage.Steps.New

oStep.Name = "DTSStep_DTSExecuteSQLTask_1"
oStep.Description = "Execute SQL Task: undefined"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSExecuteSQLTask_1"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

goPackage.Steps.Add oStep
Set oStep = Nothing

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

Set oStep = goPackage.Steps.New

oStep.Name = "DTSStep_DTSFTPTask_1"
oStep.Description = "File Transfer Protocol Task: undefined"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSFTPTask_1"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

goPackage.Steps.Add oStep
Set oStep = Nothing

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

Set oStep = goPackage.Steps("DTSStep_DTSDataPumpTask_2")
Set oPrecConstraint =
oStep.PrecedenceConstraints.New("DTSStep_DTSDataPu mpTask_1")
oPrecConstraint.StepName = "DTSStep_DTSDataPumpTask_1"
oPrecConstraint.PrecedenceBasis = 1
oPrecConstraint.Value = 0

oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

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

Set oStep = goPackage.Steps("DTSStep_DTSExecuteSQLTask_1")
Set oPrecConstraint =
oStep.PrecedenceConstraints.New("DTSStep_DTSDataPu mpTask_2")
oPrecConstraint.StepName = "DTSStep_DTSDataPumpTask_2"
oPrecConstraint.PrecedenceBasis = 0
oPrecConstraint.Value = 4

oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

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

Set oStep = goPackage.Steps("DTSStep_DTSFTPTask_1")
Set oPrecConstraint =
oStep.PrecedenceConstraints.New("DTSStep_DTSExecut eSQLTask_1")
oPrecConstraint.StepName = "DTSStep_DTSExecuteSQLTask_1"
oPrecConstraint.PrecedenceBasis = 0
oPrecConstraint.Value = 4

oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

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

'------------- call Task_Sub1 for task DTSTask_DTSDataPumpTask_1 (Excel to
TempTable)
Call Task_Sub1( goPackage )

'------------- call Task_Sub2 for task DTSTask_DTSDataPumpTask_2 (Transform
Data Task: undefined)
Call Task_Sub2( goPackage )

'------------- call Task_Sub3 for task DTSTask_DTSExecuteSQLTask_1 (Execute
SQL Task: undefined)
Call Task_Sub3( goPackage )

'------------- call Task_Sub4 for task DTSTask_DTSFTPTask_1 (File Transfer
Protocol Task: undefined)
Call Task_Sub4( goPackage )

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

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

set goPackageOld = Nothing

End Sub


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

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

End Sub

'------------- define Task_Sub1 for task DTSTask_DTSDataPumpTask_1 (Excel to
TempTable)
Public Sub Task_Sub1(ByVal goPackage As Object)

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

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

oCustomTask1.Name = "DTSTask_DTSDataPumpTask_1"
oCustomTask1.Description = "Excel to TempTable"
oCustomTask1.SourceConnectionID = 1
oCustomTask1.SourceSQLStatement = "SELECT [Date], [E# Hours], [Fuel
Type], [Hose No#], Mode, " & vbCrLf
oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement &
"Odometer, PPV, Price, Record, [Time], [User], [User No#], Vehicle, " &
vbCrLf
oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement &
"Volume" & vbCrLf
oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "FROM
[Sheet1$]"
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName =
"[MSTRIBOLOGY].[dbo].[Tbl_DieselConsumptionAFSTemp]"
oCustomTask1.ProgressRowCount = 1000
oCustomTask1.MaximumErrorCount = 0
oCustomTask1.FetchBufferSize = 1
oCustomTask1.UseFastLoad = True
oCustomTask1.InsertCommitSize = 0
oCustomTask1.ExceptionFileColumnDelimiter = "|"
oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
oCustomTask1.AllowIdentityInserts = False
oCustomTask1.FirstRow = "0"
oCustomTask1.LastRow = "0"
oCustomTask1.FastLoadOptions = 2
oCustomTask1.ExceptionFileOptions = 1
oCustomTask1.DataPumpOptions = 0

Call oCustomTask1_Trans_Sub1( oCustomTask1 )
Call oCustomTask1_Trans_Sub2( oCustomTask1 )
Call oCustomTask1_Trans_Sub3( oCustomTask1 )
Call oCustomTask1_Trans_Sub4( oCustomTask1 )
Call oCustomTask1_Trans_Sub5( oCustomTask1 )
Call oCustomTask1_Trans_Sub6( oCustomTask1 )
Call oCustomTask1_Trans_Sub7( oCustomTask1 )
Call oCustomTask1_Trans_Sub8( oCustomTask1 )
Call oCustomTask1_Trans_Sub9( oCustomTask1 )
Call oCustomTask1_Trans_Sub10( oCustomTask1 )
Call oCustomTask1_Trans_Sub11( oCustomTask1 )
Call oCustomTask1_Trans_Sub12( oCustomTask1 )
Call oCustomTask1_Trans_Sub13( oCustomTask1 )
Call oCustomTask1_Trans_Sub14( oCustomTask1 )


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

End Sub

Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__1"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Date" , 1)
oColumn.Name = "Date"
oColumn.Ordinal = 1
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 7
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Date" , 1)
oColumn.Name = "Date"
oColumn.Ordinal = 1
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub2(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__2"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Mode" , 1)
oColumn.Name = "Mode"
oColumn.Ordinal = 1
oColumn.Flags = 102
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Mode" , 1)
oColumn.Name = "Mode"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub3(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__3"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Odometer" , 1)
oColumn.Name = "Odometer"
oColumn.Ordinal = 1
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Odometer" , 1)
oColumn.Name = "Odometer"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub4(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__4"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("PPV" , 1)
oColumn.Name = "PPV"
oColumn.Ordinal = 1
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("PPV" , 1)
oColumn.Name = "PPV"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub5(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__5"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Price" , 1)
oColumn.Name = "Price"
oColumn.Ordinal = 1
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Price" , 1)
oColumn.Name = "Price"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub6(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__6"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Record" , 1)
oColumn.Name = "Record"
oColumn.Ordinal = 1
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Record" , 1)
oColumn.Name = "Record"
oColumn.Ordinal = 1
oColumn.Flags = 8
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub7(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__7"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Time" , 1)
oColumn.Name = "Time"
oColumn.Ordinal = 1
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 7
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Time" , 1)
oColumn.Name = "Time"
oColumn.Ordinal = 1
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub8(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__8"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Vehicle" , 1)
oColumn.Name = "Vehicle"
oColumn.Ordinal = 1
oColumn.Flags = 102
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Vehicle" , 1)
oColumn.Name = "Vehicle"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub9(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__9"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Volume" , 1)
oColumn.Name = "Volume"
oColumn.Ordinal = 1
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Volume" , 1)
oColumn.Name = "Volume"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub10(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__10"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("E# Hours" , 1)
oColumn.Name = "E# Hours"
oColumn.Ordinal = 1
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("EHours" , 1)
oColumn.Name = "EHours"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub11(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__11"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Fuel Type" , 1)
oColumn.Name = "Fuel Type"
oColumn.Ordinal = 1
oColumn.Flags = 102
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("FuelType" , 1)
oColumn.Name = "FuelType"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub12(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__12"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Hose No#" , 1)
oColumn.Name = "Hose No#"
oColumn.Ordinal = 1
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("HoseNo" , 1)
oColumn.Name = "HoseNo"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub13(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__13"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("User" , 1)
oColumn.Name = "User"
oColumn.Ordinal = 1
oColumn.Flags = 102
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Username" , 1)
oColumn.Name = "Username"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub14(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__14"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("User No#" , 1)
oColumn.Name = "User No#"
oColumn.Ordinal = 1
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("UserNo" , 1)
oColumn.Name = "UserNo"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

'------------- define Task_Sub2 for task DTSTask_DTSDataPumpTask_2
(Transform Data Task: undefined)
Public Sub Task_Sub2(ByVal goPackage As Object)

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

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

oCustomTask2.Name = "DTSTask_DTSDataPumpTask_2"
oCustomTask2.Description = "Transform Data Task: undefined"
oCustomTask2.SourceConnectionID = 2
oCustomTask2.SourceObjectName = "[MSTRIBOLOGY].[dbo].[AFSViewTEMP]"
oCustomTask2.DestinationConnectionID = 3
oCustomTask2.DestinationObjectName =
"[MSTRIBOLOGY].[dbo].[Tbl_DieselConsumptionAFS]"
oCustomTask2.ProgressRowCount = 1000
oCustomTask2.MaximumErrorCount = 0
oCustomTask2.FetchBufferSize = 1
oCustomTask2.UseFastLoad = True
oCustomTask2.InsertCommitSize = 0
oCustomTask2.ExceptionFileColumnDelimiter = "|"
oCustomTask2.ExceptionFileRowDelimiter = vbCrLf
oCustomTask2.AllowIdentityInserts = False
oCustomTask2.FirstRow = "0"
oCustomTask2.LastRow = "0"
oCustomTask2.FastLoadOptions = 0
oCustomTask2.ExceptionFileOptions = 1
oCustomTask2.DataPumpOptions = 0

Call oCustomTask2_Trans_Sub1( oCustomTask2 )
Call oCustomTask2_Trans_Sub2( oCustomTask2 )
Call oCustomTask2_Trans_Sub3( oCustomTask2 )
Call oCustomTask2_Trans_Sub4( oCustomTask2 )
Call oCustomTask2_Trans_Sub5( oCustomTask2 )
Call oCustomTask2_Trans_Sub6( oCustomTask2 )
Call oCustomTask2_Trans_Sub7( oCustomTask2 )
Call oCustomTask2_Trans_Sub8( oCustomTask2 )
Call oCustomTask2_Trans_Sub9( oCustomTask2 )
Call oCustomTask2_Trans_Sub10( oCustomTask2 )
Call oCustomTask2_Trans_Sub11( oCustomTask2 )
Call oCustomTask2_Trans_Sub12( oCustomTask2 )
Call oCustomTask2_Trans_Sub13( oCustomTask2 )


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

End Sub

Public Sub oCustomTask2_Trans_Sub1(ByVal oCustomTask2 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask2.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__1"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("DateTime" , 1)
oColumn.Name = "DateTime"
oColumn.Ordinal = 1
oColumn.Flags = 112
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("DateTime" , 1)
oColumn.Name = "DateTime"
oColumn.Ordinal = 1
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask2.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub2(ByVal oCustomTask2 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask2.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__2"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Record" , 1)
oColumn.Name = "Record"
oColumn.Ordinal = 1
oColumn.Flags = 8
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Record" , 1)
oColumn.Name = "Record"
oColumn.Ordinal = 1
oColumn.Flags = 8
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask2.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub3(ByVal oCustomTask2 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask2.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__3"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("EHours" , 1)
oColumn.Name = "EHours"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("EHours" , 1)
oColumn.Name = "EHours"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask2.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub4(ByVal oCustomTask2 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask2.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__4"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("FuelType" , 1)
oColumn.Name = "FuelType"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("FuelType" , 1)
oColumn.Name = "FuelType"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask2.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub5(ByVal oCustomTask2 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask2.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__5"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("HoseNo" , 1)
oColumn.Name = "HoseNo"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("HoseNo" , 1)
oColumn.Name = "HoseNo"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask2.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub6(ByVal oCustomTask2 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask2.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__6"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Mode" , 1)
oColumn.Name = "Mode"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Mode" , 1)
oColumn.Name = "Mode"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask2.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub7(ByVal oCustomTask2 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask2.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__7"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Odometer" , 1)
oColumn.Name = "Odometer"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Odometer" , 1)
oColumn.Name = "Odometer"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask2.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub8(ByVal oCustomTask2 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask2.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__8"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("PPV" , 1)
oColumn.Name = "PPV"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("PPV" , 1)
oColumn.Name = "PPV"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask2.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub9(ByVal oCustomTask2 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask2.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__9"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Price" , 1)
oColumn.Name = "Price"
oColumn.Ordinal = 1
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 6
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Price" , 1)
oColumn.Name = "Price"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask2.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub10(ByVal oCustomTask2 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask2.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__10"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Username" , 1)
oColumn.Name = "Username"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Username" , 1)
oColumn.Name = "Username"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask2.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub11(ByVal oCustomTask2 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask2.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__11"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("UserNo" , 1)
oColumn.Name = "UserNo"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("UserNo" , 1)
oColumn.Name = "UserNo"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask2.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub12(ByVal oCustomTask2 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask2.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__12"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Vehicle" , 1)
oColumn.Name = "Vehicle"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Vehicle" , 1)
oColumn.Name = "Vehicle"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask2.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub13(ByVal oCustomTask2 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask2.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DTSTransformation__13"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Volume" , 1)
oColumn.Name = "Volume"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Volume" , 1)
oColumn.Name = "Volume"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask2.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

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

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

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

oCustomTask3.Name = "DTSTask_DTSExecuteSQLTask_1"
oCustomTask3.Description = "Execute SQL Task: undefined"
oCustomTask3.SQLStatement = "DELETE " & vbCrLf
oCustomTask3.SQLStatement = oCustomTask3.SQLStatement & "FROM
Tbl_DieselConsumptionAFSTemp"
oCustomTask3.ConnectionID = 3
oCustomTask3.CommandTimeout = 0
oCustomTask3.OutputAsRecordset = False

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

End Sub

'------------- define Task_Sub4 for task DTSTask_DTSFTPTask_1 (File Transfer
Protocol Task: undefined)
Public Sub Task_Sub4(ByVal goPackage As Object)

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

Dim oCustomTask4 As DTSCustTasks.DTSFTPTask
Set oTask = goPackage.Tasks.New("DTSFTPTask")
oTask.Name = "DTSTask_DTSFTPTask_1"
Set oCustomTask4 = oTask.CustomTask

oCustomTask4.Name = "DTSTask_DTSFTPTask_1"
oCustomTask4.Description = "File Transfer Protocol Task: undefined"
oCustomTask4.SourceLocation = 1
oCustomTask4.SourceSite = "C:\Documents and Settings\RGroenewald\Desktop"
oCustomTask4.SourceFilename = "'RNI.XLS';'';'49152';"
oCustomTask4.DestSite = "C:\Documents and Settings\RGroenewald\My
Documents"
oCustomTask4.NonOverwritable = True
oCustomTask4.NumRetriesOnSource = 0

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

End Sub

This article will help with the import any excel file issue, and also the
archive step.

Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)

For the duplicate point I would handle this in the in teh insert from the
temp table into the main table, something like

INSERT MainTable (Columns)
SELECT Columns
FROM #Temptable
WHERE RecordID NOT IN (SELECT RecordID FROM MainTable)


--
Darren Green
http://www.sqldts.com

"Rudi Groenewald" <noone (AT) paflof (DOT) com> wrote

--
--