by Hi,
I have saved a vb file from SQL (DTS), I opened the file in VB 6 and
need to run the module when a user clicks on the commandbutton form I
created in forms.
The form has a button called update "Form1" and the module name is
"Module1"
When i run the app it gives me an error that says "goPackageOld As New
DTS.Package" user-defined type not defined
Is there a way i can run this dts module from VB or even from a macro
in excel?
Form 1:
Private Sub CommandButton_Click()
Call Module1
End Sub
Module1:
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
Set goPackage = goPackageOld
goPackage.Name = "ContractList"
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 oConnection As DTS.Connection2 '------------- a new connection defined below. Set oConnection = goPackage.Connections.New("SQLOLEDB") oConnection.ConnectionProperties("Persist Security Info") = oConnection.Name = "Microsoft OLE DB Provider for SQL Server" 'If you have a password for this connection, please uncomment goPackage.Connections.Add oConnection '------------- a new connection defined below. Set oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0") oConnection.ConnectionProperties("User ID") = "Admin" oConnection.Name = "Microsoft Excel 97-2000" 'If you have a password for this connection, please uncomment goPackage.Connections.Add oConnection '-------------------------------------------------------------------------- Dim oStep As DTS.Step2 '------------- a new step defined below Set oStep = goPackage.Steps.New oStep.Name = "DTSStep_DTSDataPumpTask_1" goPackage.Steps.Add oStep '-------------------------------------------------------------------------- '------------- call Task_Sub1 for task DTSTask_DTSDataPumpTask_1 '-------------------------------------------------------------------------- 'goPackage.SaveToSQLServer "(local)", "sa", "" Set goPackageOld = Nothing End Sub '------------- define Task_Sub1 for task DTSTask_DTSDataPumpTask_1 Dim oTask As DTS.Task Dim oCustomTask1 As DTS.DataPumpTask2 oCustomTask1.Name = "DTSTask_DTSDataPumpTask_1" Call oCustomTask1_Trans_Sub1(oCustomTask1) goPackage.Tasks.Add oTask End Sub Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object) Dim oTransformation As DTS.Transformation2 Set oColumn = oTransformation.SourceColumns.Add oColumn Set oColumn = oTransformation.DestinationColumns.Add oColumn Set oTransProps = oTransformation.TransformServerProperties Set oTransProps = Nothing read more »
' create package connection information
'--------------------------------------------------------------------------
'For security purposes, the password is never scripted
True
oConnection.ConnectionProperties("User ID") = "sa"
oConnection.ConnectionProperties("Initial Catalog") = "test"
oConnection.ConnectionProperties("Data Source") = "SQL"
oConnection.ConnectionProperties("Application Name") = "DTS
Designer"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "SQL"
oConnection.UserID = "sa"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "test"
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
and add your password below.
'oConnection.Password = "<put the password here>"
Set oConnection = Nothing
'For security purposes, the password is never scripted
oConnection.ConnectionProperties("Data Source") =
"C:\Documents and Settings\pnadeem\Desktop\current contract list
10-4-02.xls"
oConnection.ConnectionProperties("Extended Properties") =
"Excel 8.0;HDR=YES;"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "C:\Documents and
Settings\pnadeem\Desktop\current contract list 10-4-02.xls"
oConnection.UserID = "Admin"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
and add your password below.
'oConnection.Password = "<put the password here>"
Set oConnection = Nothing
' create package steps information
'--------------------------------------------------------------------------
Dim oPrecConstraint As DTS.PrecedenceConstraint
oStep.Description = "Transform Data Task: undefined"
oStep.ExecutionStatus = 1
oStep.TaskName = "DTSTask_DTSDataPumpTask_1"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = True
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
Set oStep = Nothing
' create package tasks information
'--------------------------------------------------------------------------
(Transform Data Task: undefined)
Call Task_Sub1(goPackage)
' Save or execute package
'--------------------------------------------------------------------------
goPackage.Execute
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
(Transform Data Task: undefined)
Public Sub Task_Sub1(ByVal goPackage As Object)
Dim oLookup As DTS.Lookup
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Description = "Transform Data Task: undefined"
oCustomTask1.SourceConnectionID = 2
oCustomTask1.SourceSQLStatement = "SELECT CLIN, [Cost to
ASRC], [Delivery Time], [Description of Generic Specifications], " &
vbCrLf
oCustomTask1.SourceSQLStatement =
oCustomTask1.SourceSQLStatement & "Manufacturer, [Mfg Part Number],
[Name of Source], [Price to DOE], " & vbCrLf
oCustomTask1.SourceSQLStatement =
oCustomTask1.SourceSQLStatement & " Quantity" &
vbCrLf
oCustomTask1.SourceSQLStatement =
oCustomTask1.SourceSQLStatement & "FROM ['Complete DOE
Contract Item List$']" & vbCrLf
oCustomTask1.SourceSQLStatement =
oCustomTask1.SourceSQLStatement & "WHERE (CLIN IS NOT NULL)"
oCustomTask1.DestinationConnectionID = 1
oCustomTask1.DestinationObjectName = "[test].[dbo].['Complete
DOE Contract Item List$']"
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_Sub2(oCustomTask1)
Call oCustomTask1_Trans_Sub3(oCustomTask1)
Call oCustomTask1_Trans_Sub4(oCustomTask1)
Call oCustomTask1_Trans_Sub5(oCustomTask1)
Call oCustomTask1_Trans_Sub6(oCustomTask1)
Call oCustomTask1_Trans_Sub7(oCustomTask1)
Call oCustomTask1_Trans_Sub8(oCustomTask1)
Call oCustomTask1_Trans_Sub9(oCustomTask1)
Set oCustomTask1 = Nothing
Set oTask = Nothing
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
oTransformation.Name = "DTSTransformation__1"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
oTransformation.SourceColumns.New("CLIN", 1)
oColumn.Name = "CLIN"
oColumn.Ordinal = 1
oColumn.Flags = 118
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
Set oColumn = Nothing
oTransformation.DestinationColumns.New("CLIN", 1)
oColumn.Name = "CLIN"
oColumn.Ordinal = 1
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
Set oColumn = Nothing
...