Here's the problem. Pulling over 50 million records from mysql into sql. Going to be a weekly task. Problem DIES. I can do it in chunks of 1 mil no problem though. So, I created a .bas and put it into vb 6 and started adding loops so that it would only pull records in 1 million increments. Problem loop only pulls the first million and then ends rather than looping?
BTW...easier way would be to release the names and descriptions from the first pass then just loop. Unfortunatly, I haven't figured out how to realease all the variables. Even though Set oCustomTask1 = Nothing
Set oTask = Nothing supposedly set everything back to blank, they don't seem too.
Any Ideas?
Thanks,
Sak
'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: C:\Documents and Settings\Administrator\My Documents\emailpull.bas
'Package Name: demodb_nocreatetable
'Package Description: DTS package description
'Generated Date: 7/19/2004
'Generated Time: 7:32:44 PM
'****************************************************************
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Public Greater_Than As Long 'Min Record to Pull
Public Less_Than As Long 'Max Record to Pull
Public count As Long ' To Sequence the Task
Public Loop_Amount As Long 'Amount to add to each loop
Private Sub Main()
Set goPackage = goPackageOld
count = 1 'Set Initial Sequence # for using same task
goPackage.Name = "demodb_nocreatetable"
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 3
goPackage.MaxConcurrentSteps = 1
goPackage.LineageOptions = 0
goPackage.UseTransaction = False
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerName = "(local)"
goPackage.LogServerFlags = 256
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("MSDASQL")
oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("Data Source") = "LocalMySQLDB"
oConnection.ConnectionProperties("Mode") = 1
oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "LocalMySQLDB"
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") = "demodb"
oConnection.ConnectionProperties("Data Source") = "(local)"
oConnection.ConnectionProperties("Application Name") = "DTS Import/Export Wizard"
oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "demodb"
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 = "Copy Data from emails to [demodb].[dbo].[emails] Step" & count 'To Sequence Names with pull
oStep.Description = "Copy Data from emails to [demodb].[dbo].[emails] Task" & count 'To Sequence Names with pull
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from emails to [demodb].[dbo].[emails] Task" & count 'To Sequence Names with pull
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 Copy Data from emails to [demodb].[dbo].[emails] Task (Copy Data from emails to [demodb].[dbo].[emails] Task)
Call Task_SubLoop(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 Looping Procedure
Public Sub Task_SubLoop(goPackage)
Greater_Than = 0
Less_Than = 1000000
Loop_Amount = 1000000
Do Until Greater_Than > 100000000
Call Task_Sub1
Greater_Than = Less_Than + 1
Less_Than = Less_Than + Loop_Amount
count = count + 1
Loop
End Sub
'------------- define Task_Sub1 for task Copy Data from emails to [demodb].[dbo].[emails] Task (Copy Data from emails to [demodb].[dbo].[emails] Task)
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 = "Copy Data from emails to [demodb].[dbo].[emails] Task" & count 'To Sequence Names with pull
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "Copy Data from emails to [demodb].[dbo].[emails] Task" & count 'To Sequence Names with pull
oCustomTask1.Description = "Copy Data from emails to [demodb].[dbo].[emails] Task" & count 'To Sequence Names with pull
oCustomTask1.SourceConnectionID = 1
oCustomTask1.SourceSQLStatement = "select `email_id`,`hash`,`email`,`domain`,`status`,`sub_ip`,`unsub_ip`,`sub_date`,`unsub_date`,`bounce_date`,`bounce_count`,`first_name`,`last_name`,`address`,`city`,`state`,`zip`,`country`,`phone`,`custom_1`,`custom_2`,`custom_3`,`custom_4`,`custom_5`,`custom_6`,"
oCustomTask1.SourceSQLStatement = "select `email_id`,`hash`,`email`,`domain`,`status`,`sub_ip`,`unsub_ip`,`sub_date`,`unsub_date`,`bounce_date`,`bounce_count`,`first_name`,`last_name`,`address`,`city`,`state`,`zip`,`country`,`phone`,`custom_1`,`custom_2`,`custom_3`,`custom_4`,`custom_5`,`custom_6`,"
oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "`custom_7`,`custom_8`,`custom_9`,`custom_10` from `demodb`.`emails` where `email_id` >= " & Greater_Than & " and `email_id` < " & Less_Than
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName = "[demodb].[dbo].[emails]"
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)
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 = "DirectCopyXform"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
Set oColumn = oTransformation.SourceColumns.New("email_id", 1)
oColumn.Name = "email_id"
oColumn.Ordinal = 1
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 19
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("hash", 2)
oColumn.Name = "hash"
oColumn.Ordinal = 2
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
Set oColumn = oTransformation.SourceColumns.New("email", 3)
oColumn.Name = "email"
oColumn.Ordinal = 3
oColumn.Flags = 104
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("domain", 4)
oColumn.Name = "domain"
oColumn.Ordinal = 4
oColumn.Flags = 104
oColumn.Size = 80
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("status", 5)
oColumn.Name = "status"
oColumn.Ordinal = 5
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.SourceColumns.New("sub_ip", 6)
oColumn.Name = "sub_ip"
oColumn.Ordinal = 6
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("unsub_ip", 7)
oColumn.Name = "unsub_ip"
oColumn.Ordinal = 7
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("sub_date", 8)
oColumn.Name = "sub_date"
oColumn.Ordinal = 8
oColumn.Flags = 120
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.SourceColumns.New("unsub_date", 9)
oColumn.Name = "unsub_date"
oColumn.Ordinal = 9
oColumn.Flags = 120
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.SourceColumns.New("bounce_date", 10)
oColumn.Name = "bounce_date"
oColumn.Ordinal = 10
oColumn.Flags = 120
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.SourceColumns.New("bounce_count", 11)
oColumn.Name = "bounce_count"
oColumn.Ordinal = 11
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("first_name", 12)
oColumn.Name = "first_name"
oColumn.Ordinal = 12
oColumn.Flags = 8
oColumn.Size = 80
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("last_name", 13)
oColumn.Name = "last_name"
oColumn.Ordinal = 13
oColumn.Flags = 8
oColumn.Size = 80
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("address", 14)
oColumn.Name = "address"
oColumn.Ordinal = 14
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("city", 15)
oColumn.Name = "city"
oColumn.Ordinal = 15
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("state", 16)
oColumn.Name = "state"
oColumn.Ordinal = 16
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("zip", 17)
oColumn.Name = "zip"
oColumn.Ordinal = 17
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("country", 18)
oColumn.Name = "country"
oColumn.Ordinal = 18
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("phone", 19)
oColumn.Name = "phone"
oColumn.Ordinal = 19
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_1", 20)
oColumn.Name = "custom_1"
oColumn.Ordinal = 20
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_2", 21)
oColumn.Name = "custom_2"
oColumn.Ordinal = 21
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_3", 22)
oColumn.Name = "custom_3"
oColumn.Ordinal = 22
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_4", 23)
oColumn.Name = "custom_4"
oColumn.Ordinal = 23
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_5", 24)
oColumn.Name = "custom_5"
oColumn.Ordinal = 24
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_6", 25)
oColumn.Name = "custom_6"
oColumn.Ordinal = 25
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_7", 26)
oColumn.Name = "custom_7"
oColumn.Ordinal = 26
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_8", 27)
oColumn.Name = "custom_8"
oColumn.Ordinal = 27
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_9", 28)
oColumn.Name = "custom_9"
oColumn.Ordinal = 28
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_10", 29)
oColumn.Name = "custom_10"
oColumn.Ordinal = 29
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("email_id", 1)
oColumn.Name = "email_id"
oColumn.Ordinal = 1
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 131
oColumn.Precision = 10
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("hash", 2)
oColumn.Name = "hash"
oColumn.Ordinal = 2
oColumn.Flags = 104
oColumn.Size = 32
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("email", 3)
oColumn.Name = "email"
oColumn.Ordinal = 3
oColumn.Flags = 104
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("domain", 4)
oColumn.Name = "domain"
oColumn.Ordinal = 4
oColumn.Flags = 104
oColumn.Size = 80
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("status", 5)
oColumn.Name = "status"
oColumn.Ordinal = 5
oColumn.Flags = 120
oColumn.Size = 1
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("sub_ip", 6)
oColumn.Name = "sub_ip"
oColumn.Ordinal = 6
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("unsub_ip", 7)
oColumn.Name = "unsub_ip"
oColumn.Ordinal = 7
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("sub_date", 8)
oColumn.Name = "sub_date"
oColumn.Ordinal = 8
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 oColumn = oTransformation.DestinationColumns.New("unsub_date", 9)
oColumn.Name = "unsub_date"
oColumn.Ordinal = 9
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 oColumn = oTransformation.DestinationColumns.New("bounce_date", 10)
oColumn.Name = "bounce_date"
oColumn.Ordinal = 10
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 oColumn = oTransformation.DestinationColumns.New("bounce_count", 11)
oColumn.Name = "bounce_count"
oColumn.Ordinal = 11
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("first_name", 12)
oColumn.Name = "first_name"
oColumn.Ordinal = 12
oColumn.Flags = 8
oColumn.Size = 80
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("last_name", 13)
oColumn.Name = "last_name"
oColumn.Ordinal = 13
oColumn.Flags = 8
oColumn.Size = 80
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("address", 14)
oColumn.Name = "address"
oColumn.Ordinal = 14
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("city", 15)
oColumn.Name = "city"
oColumn.Ordinal = 15
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("state", 16)
oColumn.Name = "state"
oColumn.Ordinal = 16
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("zip", 17)
oColumn.Name = "zip"
oColumn.Ordinal = 17
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("country", 18)
oColumn.Name = "country"
oColumn.Ordinal = 18
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("phone", 19)
oColumn.Name = "phone"
oColumn.Ordinal = 19
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_1", 20)
oColumn.Name = "custom_1"
oColumn.Ordinal = 20
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_2", 21)
oColumn.Name = "custom_2"
oColumn.Ordinal = 21
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_3", 22)
oColumn.Name = "custom_3"
oColumn.Ordinal = 22
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_4", 23)
oColumn.Name = "custom_4"
oColumn.Ordinal = 23
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_5", 24)
oColumn.Name = "custom_5"
oColumn.Ordinal = 24
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_6", 25)
oColumn.Name = "custom_6"
oColumn.Ordinal = 25
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_7", 26)
oColumn.Name = "custom_7"
oColumn.Ordinal = 26
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_8", 27)
oColumn.Name = "custom_8"
oColumn.Ordinal = 27
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_9", 28)
oColumn.Name = "custom_9"
oColumn.Ordinal = 28
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_10", 29)
oColumn.Name = "custom_10"
oColumn.Ordinal = 29
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oTransProps = oTransformation.TransformServerProperties
Set oTransProps = Nothing
oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing
End Sub
BTW...easier way would be to release the names and descriptions from the first pass then just loop. Unfortunatly, I haven't figured out how to realease all the variables. Even though Set oCustomTask1 = Nothing
Set oTask = Nothing supposedly set everything back to blank, they don't seem too.
Any Ideas?
Thanks,
Sak
'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: C:\Documents and Settings\Administrator\My Documents\emailpull.bas
'Package Name: demodb_nocreatetable
'Package Description: DTS package description
'Generated Date: 7/19/2004
'Generated Time: 7:32:44 PM
'****************************************************************
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Public Greater_Than As Long 'Min Record to Pull
Public Less_Than As Long 'Max Record to Pull
Public count As Long ' To Sequence the Task
Public Loop_Amount As Long 'Amount to add to each loop
Private Sub Main()
Set goPackage = goPackageOld
count = 1 'Set Initial Sequence # for using same task
goPackage.Name = "demodb_nocreatetable"
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 3
goPackage.MaxConcurrentSteps = 1
goPackage.LineageOptions = 0
goPackage.UseTransaction = False
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerName = "(local)"
goPackage.LogServerFlags = 256
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("MSDASQL")
oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("Data Source") = "LocalMySQLDB"
oConnection.ConnectionProperties("Mode") = 1
oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "LocalMySQLDB"
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") = "demodb"
oConnection.ConnectionProperties("Data Source") = "(local)"
oConnection.ConnectionProperties("Application Name") = "DTS Import/Export Wizard"
oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "demodb"
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 = "Copy Data from emails to [demodb].[dbo].[emails] Step" & count 'To Sequence Names with pull
oStep.Description = "Copy Data from emails to [demodb].[dbo].[emails] Task" & count 'To Sequence Names with pull
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from emails to [demodb].[dbo].[emails] Task" & count 'To Sequence Names with pull
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 Copy Data from emails to [demodb].[dbo].[emails] Task (Copy Data from emails to [demodb].[dbo].[emails] Task)
Call Task_SubLoop(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 Looping Procedure
Public Sub Task_SubLoop(goPackage)
Greater_Than = 0
Less_Than = 1000000
Loop_Amount = 1000000
Do Until Greater_Than > 100000000
Call Task_Sub1
Greater_Than = Less_Than + 1
Less_Than = Less_Than + Loop_Amount
count = count + 1
Loop
End Sub
'------------- define Task_Sub1 for task Copy Data from emails to [demodb].[dbo].[emails] Task (Copy Data from emails to [demodb].[dbo].[emails] Task)
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 = "Copy Data from emails to [demodb].[dbo].[emails] Task" & count 'To Sequence Names with pull
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "Copy Data from emails to [demodb].[dbo].[emails] Task" & count 'To Sequence Names with pull
oCustomTask1.Description = "Copy Data from emails to [demodb].[dbo].[emails] Task" & count 'To Sequence Names with pull
oCustomTask1.SourceConnectionID = 1
oCustomTask1.SourceSQLStatement = "select `email_id`,`hash`,`email`,`domain`,`status`,`sub_ip`,`unsub_ip`,`sub_date`,`unsub_date`,`bounce_date`,`bounce_count`,`first_name`,`last_name`,`address`,`city`,`state`,`zip`,`country`,`phone`,`custom_1`,`custom_2`,`custom_3`,`custom_4`,`custom_5`,`custom_6`,"
oCustomTask1.SourceSQLStatement = "select `email_id`,`hash`,`email`,`domain`,`status`,`sub_ip`,`unsub_ip`,`sub_date`,`unsub_date`,`bounce_date`,`bounce_count`,`first_name`,`last_name`,`address`,`city`,`state`,`zip`,`country`,`phone`,`custom_1`,`custom_2`,`custom_3`,`custom_4`,`custom_5`,`custom_6`,"
oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "`custom_7`,`custom_8`,`custom_9`,`custom_10` from `demodb`.`emails` where `email_id` >= " & Greater_Than & " and `email_id` < " & Less_Than
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName = "[demodb].[dbo].[emails]"
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)
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 = "DirectCopyXform"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
Set oColumn = oTransformation.SourceColumns.New("email_id", 1)
oColumn.Name = "email_id"
oColumn.Ordinal = 1
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 19
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("hash", 2)
oColumn.Name = "hash"
oColumn.Ordinal = 2
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
Set oColumn = oTransformation.SourceColumns.New("email", 3)
oColumn.Name = "email"
oColumn.Ordinal = 3
oColumn.Flags = 104
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("domain", 4)
oColumn.Name = "domain"
oColumn.Ordinal = 4
oColumn.Flags = 104
oColumn.Size = 80
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("status", 5)
oColumn.Name = "status"
oColumn.Ordinal = 5
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.SourceColumns.New("sub_ip", 6)
oColumn.Name = "sub_ip"
oColumn.Ordinal = 6
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("unsub_ip", 7)
oColumn.Name = "unsub_ip"
oColumn.Ordinal = 7
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("sub_date", 8)
oColumn.Name = "sub_date"
oColumn.Ordinal = 8
oColumn.Flags = 120
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.SourceColumns.New("unsub_date", 9)
oColumn.Name = "unsub_date"
oColumn.Ordinal = 9
oColumn.Flags = 120
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.SourceColumns.New("bounce_date", 10)
oColumn.Name = "bounce_date"
oColumn.Ordinal = 10
oColumn.Flags = 120
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.SourceColumns.New("bounce_count", 11)
oColumn.Name = "bounce_count"
oColumn.Ordinal = 11
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("first_name", 12)
oColumn.Name = "first_name"
oColumn.Ordinal = 12
oColumn.Flags = 8
oColumn.Size = 80
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("last_name", 13)
oColumn.Name = "last_name"
oColumn.Ordinal = 13
oColumn.Flags = 8
oColumn.Size = 80
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("address", 14)
oColumn.Name = "address"
oColumn.Ordinal = 14
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("city", 15)
oColumn.Name = "city"
oColumn.Ordinal = 15
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("state", 16)
oColumn.Name = "state"
oColumn.Ordinal = 16
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("zip", 17)
oColumn.Name = "zip"
oColumn.Ordinal = 17
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("country", 18)
oColumn.Name = "country"
oColumn.Ordinal = 18
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("phone", 19)
oColumn.Name = "phone"
oColumn.Ordinal = 19
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_1", 20)
oColumn.Name = "custom_1"
oColumn.Ordinal = 20
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_2", 21)
oColumn.Name = "custom_2"
oColumn.Ordinal = 21
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_3", 22)
oColumn.Name = "custom_3"
oColumn.Ordinal = 22
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_4", 23)
oColumn.Name = "custom_4"
oColumn.Ordinal = 23
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_5", 24)
oColumn.Name = "custom_5"
oColumn.Ordinal = 24
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_6", 25)
oColumn.Name = "custom_6"
oColumn.Ordinal = 25
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_7", 26)
oColumn.Name = "custom_7"
oColumn.Ordinal = 26
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_8", 27)
oColumn.Name = "custom_8"
oColumn.Ordinal = 27
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_9", 28)
oColumn.Name = "custom_9"
oColumn.Ordinal = 28
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("custom_10", 29)
oColumn.Name = "custom_10"
oColumn.Ordinal = 29
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("email_id", 1)
oColumn.Name = "email_id"
oColumn.Ordinal = 1
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 131
oColumn.Precision = 10
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("hash", 2)
oColumn.Name = "hash"
oColumn.Ordinal = 2
oColumn.Flags = 104
oColumn.Size = 32
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("email", 3)
oColumn.Name = "email"
oColumn.Ordinal = 3
oColumn.Flags = 104
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("domain", 4)
oColumn.Name = "domain"
oColumn.Ordinal = 4
oColumn.Flags = 104
oColumn.Size = 80
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("status", 5)
oColumn.Name = "status"
oColumn.Ordinal = 5
oColumn.Flags = 120
oColumn.Size = 1
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("sub_ip", 6)
oColumn.Name = "sub_ip"
oColumn.Ordinal = 6
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("unsub_ip", 7)
oColumn.Name = "unsub_ip"
oColumn.Ordinal = 7
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("sub_date", 8)
oColumn.Name = "sub_date"
oColumn.Ordinal = 8
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 oColumn = oTransformation.DestinationColumns.New("unsub_date", 9)
oColumn.Name = "unsub_date"
oColumn.Ordinal = 9
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 oColumn = oTransformation.DestinationColumns.New("bounce_date", 10)
oColumn.Name = "bounce_date"
oColumn.Ordinal = 10
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 oColumn = oTransformation.DestinationColumns.New("bounce_count", 11)
oColumn.Name = "bounce_count"
oColumn.Ordinal = 11
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("first_name", 12)
oColumn.Name = "first_name"
oColumn.Ordinal = 12
oColumn.Flags = 8
oColumn.Size = 80
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("last_name", 13)
oColumn.Name = "last_name"
oColumn.Ordinal = 13
oColumn.Flags = 8
oColumn.Size = 80
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("address", 14)
oColumn.Name = "address"
oColumn.Ordinal = 14
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("city", 15)
oColumn.Name = "city"
oColumn.Ordinal = 15
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("state", 16)
oColumn.Name = "state"
oColumn.Ordinal = 16
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("zip", 17)
oColumn.Name = "zip"
oColumn.Ordinal = 17
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("country", 18)
oColumn.Name = "country"
oColumn.Ordinal = 18
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("phone", 19)
oColumn.Name = "phone"
oColumn.Ordinal = 19
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_1", 20)
oColumn.Name = "custom_1"
oColumn.Ordinal = 20
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_2", 21)
oColumn.Name = "custom_2"
oColumn.Ordinal = 21
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_3", 22)
oColumn.Name = "custom_3"
oColumn.Ordinal = 22
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_4", 23)
oColumn.Name = "custom_4"
oColumn.Ordinal = 23
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_5", 24)
oColumn.Name = "custom_5"
oColumn.Ordinal = 24
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_6", 25)
oColumn.Name = "custom_6"
oColumn.Ordinal = 25
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_7", 26)
oColumn.Name = "custom_7"
oColumn.Ordinal = 26
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_8", 27)
oColumn.Name = "custom_8"
oColumn.Ordinal = 27
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_9", 28)
oColumn.Name = "custom_9"
oColumn.Ordinal = 28
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("custom_10", 29)
oColumn.Name = "custom_10"
oColumn.Ordinal = 29
oColumn.Flags = 8
oColumn.Size = 200
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oTransProps = oTransformation.TransformServerProperties
Set oTransProps = Nothing
oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing
End Sub