Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using SQL DTS .bas file in Excel

Status
Not open for further replies.
rfjaster

rfjaster

Vendor
Joined
May 4, 2009
Messages
4
Location
US
I am not a programmer so am not familiar with VB code.

I am trying to import an Excel spreadsheet into SQL. I was able to do so using SQL DTS but I don't want the user that will be doing this in the future to have to deal with SQL. I saved the DTS package as a .bas VB file and would like to just add a button to the Excel spreadsheet. I was able to get into the Visual Basic portion of Excel and import the file but if I try copying the code to the worksheet object, I get errors when trying to run.

Any help on how to do this would be appreciated (remember that I do not know VB code so was just trying to use the DTS package that was generated in SQL. Thanks in advance.
 
Sort by date Sort by votes



it would sure help to see the VBA code that you refer to.

When you state, "I am trying to import an Excel spreadsheet into SQL," what does that mean? SQL is just a query language, so it does not make sense.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
When I was referring to SQL, I meant importing into MS SQL Server into a database table. Here is the VB code that was created when saving the DTS package from SQL Enterprise Manager:

'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: C:\Documents and Settings\rjaster\Desktop\msfrcfil.bas
'Package Name: msfrcfil
'Package Description: DTS package description
'Generated Date: 7/28/2009
'Generated Time: 8:49:51 AM
'****************************************************************

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

goPackage.Name = "msfrcfil"
goPackage.Description = "DTS package description"
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


Dim oConnProperty As DTS.OleDBProperty

'---------------------------------------------------------------------------
' 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("Data Source") = "C:\Documents and Settings\rjaster\Desktop\msfrcfil.XLS"
oConnection.ConnectionProperties("Extended Properties") = "Excel 8.0;HDR=YES;"

oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "C:\Documents and Settings\rjaster\Desktop\msfrcfil.XLS"
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") = "demodata"
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 = "demodata"
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 = "Delete from Table [demodata].[dbo].[MSFRCFIL_SQL] Step"
oStep.Description = "Delete from Table [demodata].[dbo].[MSFRCFIL_SQL] Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Delete from Table [demodata].[dbo].[MSFRCFIL_SQL] Task"
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 = "Copy Data from MSFRCFIL_SQL$ to [demodata].[dbo].[MSFRCFIL_SQL] Step"
oStep.Description = "Copy Data from MSFRCFIL_SQL$ to [demodata].[dbo].[MSFRCFIL_SQL] Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from MSFRCFIL_SQL$ to [demodata].[dbo].[MSFRCFIL_SQL] Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = True
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

goPackage.Steps.Add oStep
Set oStep = Nothing

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

Set oStep = goPackage.Steps("Copy Data from MSFRCFIL_SQL$ to [demodata].[dbo].[MSFRCFIL_SQL] Step")
Set oPrecConstraint = oStep.precedenceConstraints.New("Delete from Table [demodata].[dbo].[MSFRCFIL_SQL] Step")
oPrecConstraint.StepName = "Delete from Table [demodata].[dbo].[MSFRCFIL_SQL] Step"
oPrecConstraint.PrecedenceBasis = 1
oPrecConstraint.Value = 0

oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

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

'------------- call Task_Sub1 for task Delete from Table [demodata].[dbo].[MSFRCFIL_SQL] Task (Delete from Table [demodata].[dbo].[MSFRCFIL_SQL] Task)
Call Task_Sub1(goPackage)

'------------- call Task_Sub2 for task Copy Data from MSFRCFIL_SQL$ to [demodata].[dbo].[MSFRCFIL_SQL] Task (Copy Data from MSFRCFIL_SQL$ to [demodata].[dbo].[MSFRCFIL_SQL] Task)
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 line 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 Delete from Table [demodata].[dbo].[MSFRCFIL_SQL] Task (Delete from Table [demodata].[dbo].[MSFRCFIL_SQL] Task)
Public Sub Task_Sub1(ByVal goPackage As Object)

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

Dim oCustomTask1 As DTS.ExecuteSQLTask2
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
oTask.Name = "Delete from Table [demodata].[dbo].[MSFRCFIL_SQL] Task"
Set oCustomTask1 = oTask.CustomTask

oCustomTask1.Name = "Delete from Table [demodata].[dbo].[MSFRCFIL_SQL] Task"
oCustomTask1.Description = "Delete from Table [demodata].[dbo].[MSFRCFIL_SQL] Task"
oCustomTask1.SQLStatement = "delete from [demodata].[dbo].[MSFRCFIL_SQL]"
oCustomTask1.ConnectionID = 2
oCustomTask1.CommandTimeout = 0
oCustomTask1.OutputAsRecordset = False

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

End Sub

'------------- define Task_Sub2 for task Copy Data from MSFRCFIL_SQL$ to [demodata].[dbo].[MSFRCFIL_SQL] Task (Copy Data from MSFRCFIL_SQL$ to [demodata].[dbo].[MSFRCFIL_SQL] Task)
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 = "Copy Data from MSFRCFIL_SQL$ to [demodata].[dbo].[MSFRCFIL_SQL] Task"
Set oCustomTask2 = oTask.CustomTask

oCustomTask2.Name = "Copy Data from MSFRCFIL_SQL$ to [demodata].[dbo].[MSFRCFIL_SQL] Task"
oCustomTask2.Description = "Copy Data from MSFRCFIL_SQL$ to [demodata].[dbo].[MSFRCFIL_SQL] Task"
oCustomTask2.SourceConnectionID = 1
oCustomTask2.SourceSQLStatement = "select `item_no`,`item_filler`,`loc`,`ord_no`,`rel_no`,`seq_no`,`byr_plnr`,`alt_item_no`,`alt_item_filler`,`alt_loc`,`due_dt`,`orig_due_dt`,`alt_ord_no`,`alt_seq_no`,`prod_cat`,`p_and_ic_cd`,`commodity_cd`,`vend_no`,`pur_or_mfg`,`mfg_method`,`abc_cd`,`qty"
oCustomTask2.SourceSQLStatement = oCustomTask2.SourceSQLStatement & "`,`cons_ord_type`,`cons_ord_type_sb`,`cons_pkg_id`,`cons_ord_no`,`cons_line_no`,`cons_cus_no`,`cons_dt`,`cons_ord_qty`,`cons_qty`,`orig_qty`,`user_def_fld_1`,`user_def_fld_2`,`user_def_fld_3`,`user_def_fld_4`,`user_def_fld_5`,`expl_feature`,`filler_0001`,"
oCustomTask2.SourceSQLStatement = oCustomTask2.SourceSQLStatement & "`filler_0002`,`A4GLIdentity` from `MSFRCFIL_SQL$`"
oCustomTask2.DestinationConnectionID = 2
oCustomTask2.DestinationObjectName = "[demodata].[dbo].[MSFRCFIL_SQL]"
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 = 2
oCustomTask2.ExceptionFileOptions = 1
oCustomTask2.DataPumpOptions = 0

Call oCustomTask2_Trans_Sub1(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 = "DirectCopyXform"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("item_no", 1)
oColumn.Name = "item_no"
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.SourceColumns.New("item_filler", 2)
oColumn.Name = "item_filler"
oColumn.Ordinal = 2
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.SourceColumns.New("loc", 3)
oColumn.Name = "loc"
oColumn.Ordinal = 3
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.SourceColumns.New("ord_no", 4)
oColumn.Name = "ord_no"
oColumn.Ordinal = 4
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.SourceColumns.New("rel_no", 5)
oColumn.Name = "rel_no"
oColumn.Ordinal = 5
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.SourceColumns.New("seq_no", 6)
oColumn.Name = "seq_no"
oColumn.Ordinal = 6
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.SourceColumns.New("byr_plnr", 7)
oColumn.Name = "byr_plnr"
oColumn.Ordinal = 7
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.SourceColumns.New("alt_item_no", 8)
oColumn.Name = "alt_item_no"
oColumn.Ordinal = 8
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.SourceColumns.New("alt_item_filler", 9)
oColumn.Name = "alt_item_filler"
oColumn.Ordinal = 9
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.SourceColumns.New("alt_loc", 10)
oColumn.Name = "alt_loc"
oColumn.Ordinal = 10
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.SourceColumns.New("due_dt", 11)
oColumn.Name = "due_dt"
oColumn.Ordinal = 11
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.SourceColumns.New("orig_due_dt", 12)
oColumn.Name = "orig_due_dt"
oColumn.Ordinal = 12
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.SourceColumns.New("alt_ord_no", 13)
oColumn.Name = "alt_ord_no"
oColumn.Ordinal = 13
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.SourceColumns.New("alt_seq_no", 14)
oColumn.Name = "alt_seq_no"
oColumn.Ordinal = 14
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.SourceColumns.New("prod_cat", 15)
oColumn.Name = "prod_cat"
oColumn.Ordinal = 15
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.SourceColumns.New("p_and_ic_cd", 16)
oColumn.Name = "p_and_ic_cd"
oColumn.Ordinal = 16
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.SourceColumns.New("commodity_cd", 17)
oColumn.Name = "commodity_cd"
oColumn.Ordinal = 17
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.SourceColumns.New("vend_no", 18)
oColumn.Name = "vend_no"
oColumn.Ordinal = 18
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.SourceColumns.New("pur_or_mfg", 19)
oColumn.Name = "pur_or_mfg"
oColumn.Ordinal = 19
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.SourceColumns.New("mfg_method", 20)
oColumn.Name = "mfg_method"
oColumn.Ordinal = 20
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.SourceColumns.New("abc_cd", 21)
oColumn.Name = "abc_cd"
oColumn.Ordinal = 21
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.SourceColumns.New("qty", 22)
oColumn.Name = "qty"
oColumn.Ordinal = 22
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.SourceColumns.New("cons_ord_type", 23)
oColumn.Name = "cons_ord_type"
oColumn.Ordinal = 23
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.SourceColumns.New("cons_ord_type_sb", 24)
oColumn.Name = "cons_ord_type_sb"
oColumn.Ordinal = 24
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.SourceColumns.New("cons_pkg_id", 25)
oColumn.Name = "cons_pkg_id"
oColumn.Ordinal = 25
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.SourceColumns.New("cons_ord_no", 26)
oColumn.Name = "cons_ord_no"
oColumn.Ordinal = 26
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.SourceColumns.New("cons_line_no", 27)
oColumn.Name = "cons_line_no"
oColumn.Ordinal = 27
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.SourceColumns.New("cons_cus_no", 28)
oColumn.Name = "cons_cus_no"
oColumn.Ordinal = 28
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.SourceColumns.New("cons_dt", 29)
oColumn.Name = "cons_dt"
oColumn.Ordinal = 29
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.SourceColumns.New("cons_ord_qty", 30)
oColumn.Name = "cons_ord_qty"
oColumn.Ordinal = 30
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.SourceColumns.New("cons_qty", 31)
oColumn.Name = "cons_qty"
oColumn.Ordinal = 31
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.SourceColumns.New("orig_qty", 32)
oColumn.Name = "orig_qty"
oColumn.Ordinal = 32
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.SourceColumns.New("user_def_fld_1", 33)
oColumn.Name = "user_def_fld_1"
oColumn.Ordinal = 33
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.SourceColumns.New("user_def_fld_2", 34)
oColumn.Name = "user_def_fld_2"
oColumn.Ordinal = 34
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.SourceColumns.New("user_def_fld_3", 35)
oColumn.Name = "user_def_fld_3"
oColumn.Ordinal = 35
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.SourceColumns.New("user_def_fld_4", 36)
oColumn.Name = "user_def_fld_4"
oColumn.Ordinal = 36
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.SourceColumns.New("user_def_fld_5", 37)
oColumn.Name = "user_def_fld_5"
oColumn.Ordinal = 37
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.SourceColumns.New("expl_feature", 38)
oColumn.Name = "expl_feature"
oColumn.Ordinal = 38
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.SourceColumns.New("filler_0001", 39)
oColumn.Name = "filler_0001"
oColumn.Ordinal = 39
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.SourceColumns.New("filler_0002", 40)
oColumn.Name = "filler_0002"
oColumn.Ordinal = 40
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.SourceColumns.New("A4GLIdentity", 41)
oColumn.Name = "A4GLIdentity"
oColumn.Ordinal = 41
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("item_no", 1)
oColumn.Name = "item_no"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("item_filler", 2)
oColumn.Name = "item_filler"
oColumn.Ordinal = 2
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("loc", 3)
oColumn.Name = "loc"
oColumn.Ordinal = 3
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("ord_no", 4)
oColumn.Name = "ord_no"
oColumn.Ordinal = 4
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

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

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("seq_no", 6)
oColumn.Name = "seq_no"
oColumn.Ordinal = 6
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("byr_plnr", 7)
oColumn.Name = "byr_plnr"
oColumn.Ordinal = 7
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("alt_item_no", 8)
oColumn.Name = "alt_item_no"
oColumn.Ordinal = 8
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("alt_item_filler", 9)
oColumn.Name = "alt_item_filler"
oColumn.Ordinal = 9
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("alt_loc", 10)
oColumn.Name = "alt_loc"
oColumn.Ordinal = 10
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("due_dt", 11)
oColumn.Name = "due_dt"
oColumn.Ordinal = 11
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("orig_due_dt", 12)
oColumn.Name = "orig_due_dt"
oColumn.Ordinal = 12
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("alt_ord_no", 13)
oColumn.Name = "alt_ord_no"
oColumn.Ordinal = 13
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("alt_seq_no", 14)
oColumn.Name = "alt_seq_no"
oColumn.Ordinal = 14
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("prod_cat", 15)
oColumn.Name = "prod_cat"
oColumn.Ordinal = 15
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("p_and_ic_cd", 16)
oColumn.Name = "p_and_ic_cd"
oColumn.Ordinal = 16
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("commodity_cd", 17)
oColumn.Name = "commodity_cd"
oColumn.Ordinal = 17
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("vend_no", 18)
oColumn.Name = "vend_no"
oColumn.Ordinal = 18
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("pur_or_mfg", 19)
oColumn.Name = "pur_or_mfg"
oColumn.Ordinal = 19
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("mfg_method", 20)
oColumn.Name = "mfg_method"
oColumn.Ordinal = 20
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("abc_cd", 21)
oColumn.Name = "abc_cd"
oColumn.Ordinal = 21
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("qty", 22)
oColumn.Name = "qty"
oColumn.Ordinal = 22
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("cons_ord_type", 23)
oColumn.Name = "cons_ord_type"
oColumn.Ordinal = 23
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("cons_ord_type_sb", 24)
oColumn.Name = "cons_ord_type_sb"
oColumn.Ordinal = 24
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("cons_pkg_id", 25)
oColumn.Name = "cons_pkg_id"
oColumn.Ordinal = 25
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("cons_ord_no", 26)
oColumn.Name = "cons_ord_no"
oColumn.Ordinal = 26
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("cons_line_no", 27)
oColumn.Name = "cons_line_no"
oColumn.Ordinal = 27
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("cons_cus_no", 28)
oColumn.Name = "cons_cus_no"
oColumn.Ordinal = 28
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("cons_dt", 29)
oColumn.Name = "cons_dt"
oColumn.Ordinal = 29
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("cons_ord_qty", 30)
oColumn.Name = "cons_ord_qty"
oColumn.Ordinal = 30
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("cons_qty", 31)
oColumn.Name = "cons_qty"
oColumn.Ordinal = 31
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("orig_qty", 32)
oColumn.Name = "orig_qty"
oColumn.Ordinal = 32
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("user_def_fld_1", 33)
oColumn.Name = "user_def_fld_1"
oColumn.Ordinal = 33
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("user_def_fld_2", 34)
oColumn.Name = "user_def_fld_2"
oColumn.Ordinal = 34
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("user_def_fld_3", 35)
oColumn.Name = "user_def_fld_3"
oColumn.Ordinal = 35
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("user_def_fld_4", 36)
oColumn.Name = "user_def_fld_4"
oColumn.Ordinal = 36
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("user_def_fld_5", 37)
oColumn.Name = "user_def_fld_5"
oColumn.Ordinal = 37
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("expl_feature", 38)
oColumn.Name = "expl_feature"
oColumn.Ordinal = 38
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("filler_0001", 39)
oColumn.Name = "filler_0001"
oColumn.Ordinal = 39
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("filler_0002", 40)
oColumn.Name = "filler_0002"
oColumn.Ordinal = 40
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("A4GLIdentity", 41)
oColumn.Name = "A4GLIdentity"
oColumn.Ordinal = 41
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 5
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

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Submeg
  • Question Question
Selecting shapes based on its name and then arranging them in a circle
Replies
2
Views
433
Andrzejek
Andrzejek
Andrzejek
  • Locked
  • Question Question
Copy data between SQL and Oracle
Replies
8
Views
2K
remeng
remeng
2009luca
  • Locked
  • Question Question
put value in input box web page
Replies
0
Views
391
2009luca
2009luca
remeng
  • Locked
  • Question Question
Excel VBA to Pass Var between file macros after var changes
Replies
13
Views
1K
mikrom
mikrom
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
2K
remeng
remeng

Part and Inventory Search

Sponsor

Back
Top