VB Module from DTS how do I run in VB

VB Module from DTS how do I run in VB

Post by Perv » Sat, 16 Nov 2002 02:30:23



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

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

Dim oConnection As DTS.Connection2

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

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

        oConnection.ConnectionProperties("Persist Security Info") =
True
        oConnection.ConnectionProperties("User ID") = "sa"
        oConnection.ConnectionProperties("Initial Catalog") = "test"
        oConnection.ConnectionProperties("Data Source") = "SQL"
        oConnection.ConnectionProperties("Application Name") = "DTS
Designer"

        oConnection.Name = "Microsoft OLE DB Provider for SQL Server"
        oConnection.ID = 1
        oConnection.Reusable = True
        oConnection.ConnectImmediate = False
        oConnection.DataSource = "SQL"
        oConnection.UserID = "sa"
        oConnection.ConnectionTimeout = 60
        oConnection.Catalog = "test"
        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("Microsoft.Jet.OLEDB.4.0")

        oConnection.ConnectionProperties("User ID") = "Admin"
        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.Name = "Microsoft Excel 97-2000"
        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

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

goPackage.Connections.Add oConnection
Set oConnection = Nothing

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

Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint

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

Set oStep = goPackage.Steps.New

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

goPackage.Steps.Add oStep
Set oStep = Nothing

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

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

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

'goPackage.SaveToSQLServer "(local)", "sa", ""
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

Set goPackageOld = Nothing

End Sub

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

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

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

        oCustomTask1.Name = "DTSTask_DTSDataPumpTask_1"
        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_Sub1(oCustomTask1)
Call oCustomTask1_Trans_Sub2(oCustomTask1)
Call oCustomTask1_Trans_Sub3(oCustomTask1)
Call oCustomTask1_Trans_Sub4(oCustomTask1)
Call oCustomTask1_Trans_Sub5(oCustomTask1)
Call oCustomTask1_Trans_Sub6(oCustomTask1)
Call oCustomTask1_Trans_Sub7(oCustomTask1)
Call oCustomTask1_Trans_Sub8(oCustomTask1)
Call oCustomTask1_Trans_Sub9(oCustomTask1)

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

End Sub

Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)

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

                Set oColumn =
oTransformation.SourceColumns.New("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

                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn =
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

                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing

        Set oTransProps = oTransformation.TransformServerProperties

        Set oTransProps = Nothing
...

read more »

 
 
 

VB Module from DTS how do I run in VB

Post by Jason Keat » Sat, 16 Nov 2002 22:13:32



| 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?

Add a reference to the Microsoft DTSPackage Object Library ?

 
 
 

1. Problem with DTS Package Execution with DTS RUn from SQL or VB

Hi all,

    I am having serious problems understanding what is going wrong here. In
T-SQL I can execute a package using xp_xmdshell ...

When I execute using this format it works fine: (may wrap)
0xEF81FBEFE60680AAD8AEF154920F9E2C0CF5A678E4C5D2257CEBCDE64FDDE152F72A800215
65A8BC'
<
i.e. so long as I use the Hex GUID as the package id it works...but I want
to beable to use this syntax:
exec master.dbo.xp_cmdshell 'dtsrun /S LUXSBR5016 /U sa /N
DTS_CSG_ImportFile'
<
but I ALWAYS get the error, this package does not exist. And I get the same
error when I try to run this from VB.

Has anybody seen this error, and how to fix it?

thanks for any ideas or asssistnce

Philip

2. Software Inspection as a SERVICE for SQL Server apps?

3. Running MS Access Module from VB

4. Enmerating databases without logging on?

5. Running an Access module from VB

6. Unwanted ADO Error Messages -- Validation

7. DTS module in VB

8. UNIX ORACLE 8

9. VBS and DTS as a VB module

10. Serious Problem in doing Object transfer using DTS ObjectTransfer via VB

11. running vb 4 and vb 5 simultaneously

12. VB 6.0 DTS to VB.NET?

13. Calling a DTS Custom task from a VB Program - Executable or VB Script