Execute DTS package from VB - Taking so long....

[sqldevrequiretektips]

Using Microsoft DTS Package object library I am executing DTS package with in VB environment

WLActCat_WLDay – table has 5000000 Records (SQL Server Tabel)
More detail on WLActCat_WLDay – No Index define on any field

Custtb – table has 45000 Records (SQL Server Table)
Cluster Index define on Cust_Life_Num

Source = Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo " & _
"from WLActCat_WLDay A " & _
"Inner Join (select distinct (cust_life_num) from CUSTTB where " & where_sqldb & " ) B" & _
" On A.Cust_Life_Num = B.Cust_Life_num"

(sqldb – string variable - what user selected from VB Form)
Destination Table: tblWLActCatId (Local Access Table



In the package I am just transferring records, which I received from above query to Ms access table

Ms Access table Name:

tblWLActCatId : - No Index on any field

CustLifeNo
WLActCatId
WLDayId
WeekNo

When execution complete local ms access table(tblWLActCatId) has more then 2750000 Records..


But it’s taking more then 15 minutes when I call package.execute.

So, Is anybody can help me out to improve performance and reduce the time

Any Suggesion would really appriciate..

AD

 

[fredericofonseca]

First of all, does
Custtb – table has 45000 Records (SQL Server Table)
Cluster Index define on Cust_Life_Num

have duplicates on cust_life_num ?

If so I would try and create a temporary table with the distinct records first, and then join that table with WLActCat_WLDay.
If not then remove the select distinct and just do a direct join.

Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo " & _
"from WLActCat_WLDay A " & _
"Inner Join CUSTTB B " & _
" On A.Cust_Life_Num = B.Cust_Life_num" & _
" where_sqldb"

Just for testing purposes I would try and do the DTS through Enterprise Manager as a DTS package, and see if performance improves. I would try this with both your original SQL and with my sugestion(s).

Note that with DTS you can also mess with the number of records used to perform a commit (batch size), and this will affect performance. Too low or too high and performance will suffer. I normally use arount 9000-10000 records, but this can vary.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[sqldevrequiretektips]

Thanks for your reply...

I try your sql which doesn't make any diffrence,,

In my case when i running sql doesn't take long, Let say if user select everything ( I mean Where_Sqldb = so many criteria) then Maximum time to complete i would say around 40 - 50 seconds...

It's taking so much time to insert in Local Access table.

And becuase of large amount of data...

oCustomTask1.UseFastLoad = True

oCustomTask1.InsertCommitSize = 1000 - This property Before i had 0 and chage it to 1000 but It doesn't effect really...


perform a commit (batch size) - Means InsertCommitSIze? Property ?

Also I am Is there any way I can execute two task together in VB? Please advise me ...

Thx
AD

 

[fredericofonseca]

Yes that is the property.

Try and do that with Enterprise manager, including the update to Access.

Then you will know if it is your VB code or just the Access updates.

Also as you are defining everything from the VB side can you please post your full code for the DTS bit.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[sqldevrequiretektips]

Here Is my code in vb
I am just calling MainThread Method from my vb application

Please let me know if i need any modification

I am updating two tables on access
tblWLActCat_WLDay
tblWLEmpType_WLDay
****
Here what i did in command click event

DowloadProcess.strWhere = sqlDB
DowloadProcess.MainThread
***



Module Name DowloadProcess
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Public strWhere As String
Public Sub MainThread()
Set goPackage = goPackageOld

goPackage.Name = "DowloadProcess"
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 = False
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("SQLOLEDB")

oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("User ID") = "terrredesign"
oConnection.ConnectionProperties("Initial Catalog") = "cswm"
oConnection.ConnectionProperties("Data Source") = "(local)"
oConnection.ConnectionProperties("Application Name") = "DTS Designer"

oConnection.Name = "Microsoft OLE DB Provider for SQL Server"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.UserId = "terrredesign"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "cswm"
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
oConnection.Password = "terrredesign"

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("Microsoft.Jet.OLEDB.4.0")

oConnection.ConnectionProperties("User ID") = "Admin"
oConnection.ConnectionProperties("Data Source") = App.Path & "\model_2.mdb"

oConnection.Name = "Microsoft Access"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = True
oConnection.DataSource = App.Path & "\model_2.mdb"
oConnection.UserId = "Admin"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False


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 = "Transform Data Task: undefined"
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 = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

goPackage.Steps.Add oStep
Set oStep = Nothing

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

Set oStep = goPackage.Steps.New

oStep.Name = "DTSStep_DTSDataPumpTask_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

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

'------------- call Task_Sub1 for task DTSTask_DTSDataPumpTask_1 (Transform Data Task: undefined)
Call Task_Sub1(goPackage)

'------------- call Task_Sub2 for task DTSTask_DTSDataPumpTask_2 (Transform Data Task: undefined)
Call Task_Sub2(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 (Transform Data Task: undefined)
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 = "Transform Data Task: undefined"
oCustomTask1.SourceConnectionID = 1
oCustomTask1.SourceSQLStatement = "Select A.Cust_Life_Num, A.WLActCatId, A.WLDayId, A.WeekNo " & vbCrLf
oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "from WLActCat_WLDay A " & vbCrLf
oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "Inner Join (select distinct (cust_life_num) from CUSTTB Where " & strWhere & " ) B " & vbCrLf
oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "On A.Cust_Life_Num = B.Cust_Life_num"
oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & " And A.WeekNo In " & " " & SetWeekRange & ""
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName = "tblWLActCat_WLDay"
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 = 0
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)


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.DataPumpTransformCopy")
oTransformation.Name = "DTSTransformation__1"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("WLActCatId", 1)
oColumn.Name = "WLActCatId"
oColumn.Ordinal = 1
oColumn.FLAGS = 120
oColumn.Size = 2
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("WLActCatId", 1)
oColumn.Name = "WLActCatId"
oColumn.Ordinal = 1
oColumn.FLAGS = 102
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_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.DataPumpTransformCopy")
oTransformation.Name = "DTSTransformation__2"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("WLDayId", 1)
oColumn.Name = "WLDayId"
oColumn.Ordinal = 1
oColumn.FLAGS = 24
oColumn.Size = 3
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("WLDayId", 1)
oColumn.Name = "WLDayId"
oColumn.Ordinal = 1
oColumn.FLAGS = 102
oColumn.Size = 3
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.DataPumpTransformCopy")
oTransformation.Name = "DTSTransformation__3"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("WeekNo", 1)
oColumn.Name = "WeekNo"
oColumn.Ordinal = 1
oColumn.FLAGS = 24
oColumn.Size = 0
oColumn.DataType = 2
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("WeekNo", 1)
oColumn.Name = "WeekNo"
oColumn.Ordinal = 1
oColumn.FLAGS = 118
oColumn.Size = 0
oColumn.DataType = 2
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.DataPumpTransformCopy")
oTransformation.Name = "DTSTransformation__4"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Cust_Life_Num", 1)
oColumn.Name = "Cust_Life_Num"
oColumn.Ordinal = 1
oColumn.FLAGS = 24
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("CustLifeNo", 1)
oColumn.Name = "CustLifeNo"
oColumn.Ordinal = 1
oColumn.FLAGS = 118
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

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 = 1
oCustomTask2.SourceSQLStatement = "Select A.Cust_Life_Num, A.WLEmpTypeId, A.WLDayId, A.WeekNo " & vbCrLf
oCustomTask2.SourceSQLStatement = oCustomTask2.SourceSQLStatement & "from WLEmpType_WLDay A " & vbCrLf
oCustomTask2.SourceSQLStatement = oCustomTask2.SourceSQLStatement & "Inner Join (select distinct (cust_life_num) from CUSTTB Where " & strWhere & " ) B " & vbCrLf
oCustomTask2.SourceSQLStatement = oCustomTask2.SourceSQLStatement & "On A.Cust_Life_Num = B.Cust_Life_num"
oCustomTask2.SourceSQLStatement = oCustomTask2.SourceSQLStatement & " And A.WeekNo In " & " " & SetWeekRange & ""
oCustomTask2.DestinationConnectionID = 2
oCustomTask2.DestinationObjectName = "tblWLEmpType_WLDay"
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)


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.DataPumpTransformCopy")
oTransformation.Name = "DTSTransformation__1"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("WLEmpTypeId", 1)
oColumn.Name = "WLEmpTypeId"
oColumn.Ordinal = 1
oColumn.FLAGS = 120
oColumn.Size = 1
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("WLEmpTypeId", 1)
oColumn.Name = "WLEmpTypeId"
oColumn.Ordinal = 1
oColumn.FLAGS = 102
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_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.DataPumpTransformCopy")
oTransformation.Name = "DTSTransformation__2"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("WLDayId", 1)
oColumn.Name = "WLDayId"
oColumn.Ordinal = 1
oColumn.FLAGS = 24
oColumn.Size = 3
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("WLDayId", 1)
oColumn.Name = "WLDayId"
oColumn.Ordinal = 1
oColumn.FLAGS = 102
oColumn.Size = 3
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_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.DataPumpTransformCopy")
oTransformation.Name = "DTSTransformation__3"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("WeekNo", 1)
oColumn.Name = "WeekNo"
oColumn.Ordinal = 1
oColumn.FLAGS = 24
oColumn.Size = 0
oColumn.DataType = 2
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("WeekNo", 1)
oColumn.Name = "WeekNo"
oColumn.Ordinal = 1
oColumn.FLAGS = 118
oColumn.Size = 0
oColumn.DataType = 2
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.DataPumpTransformCopy")
oTransformation.Name = "DTSTransformation__4"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("Cust_Life_Num", 1)
oColumn.Name = "Cust_Life_Num"
oColumn.Ordinal = 1
oColumn.FLAGS = 24
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("CustLifeNo", 1)
oColumn.Name = "CustLifeNo"
oColumn.Ordinal = 1
oColumn.FLAGS = 118
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask2.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub
Public Function SetWeekRange() As String
Dim TotalWeekNInAccontingMonth As String
Dim x As New clsAccountingPeriodValues
x.InitWithDate Date
TotalWeekNInAccontingMonth = x.WeeksInPeriod
If (TotalWeekNInAccontingMonth = 4) Then
SetWeekRange = "(" & "1," & "2," & "3," & "4" & ")"
ElseIf (TotalWeekNInAccontingMonth = 5) Then
SetWeekRange = "(" & "1," & "2," & "3," & "4," & "5" & ")"
ElseIf (TotalWeekNInAccontingMonth = 6) Then
SetWeekRange = "(" & "1," & "2," & "3," & "4," & "5," & "6" & ")"
End If
End Function

 

[fredericofonseca]

Hi,

As your code is really big and was cutoff, can you please post another reply with all code after and including

Public Sub oCustomTask2_Trans_Sub4(ByVal oCustomTask2 As Object)



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

And if possible also include the form you have with this code (.frm), and the sql code required to create the two tables involved, including indexes. Just to make life easier

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com