DTS browse different files and run module

DTS browse different files and run module

Post by Perv » Sat, 16 Nov 2002 05:45:36



Hello,

Does anyone know if I can add a browse option to select the file from
the users local desktop and have them pick the file and have the
module run based on that spreadsheet?  If so, what is the VB code for
browse?  I would appreciate the help.  Thanks!

(code from Module that points to a specific excel spreadsheet):
oConnection.ConnectionProperties("Data Source") = "C:\Documents and
Settings\pnadeem\Desktop\current contract list 10-4-02.xls"

Module1 (DTS):

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

        oCustomTask1.Transformations.Add oTransformation
...

read more »

 
 
 

DTS browse different files and run module

Post by Darren Gree » Sat, 16 Nov 2002 17:16:03


If you want to code this yourself in VB, there are some great examples at
http://www.mvps.org/vbnet/ that use API calls and saves and the
redistribution and version clash hassles of the VB Common Dialog Control.

If you just want this in a package then try this -

Open File Dialog Custom Task
http://www.sqldts.com/default.aspx?6,101,226,0,0
--
Darren Green (SQL Server MVP)
http://www.sqldts.com

Check out the PASS Community Summit - Seattle, the largest and only user
event entirely dedicated to SQL Server, November 19-22.
http://www.sqlpass.org/events/seattle/index.cfm

"Pervez" <p_nad...@yahoo.com> wrote in message

news:6ea2f09e.0211141245.50eafad7@posting.google.com...
> Hello,

> Does anyone know if I can add a browse option to select the file from
> the users local desktop and have them pick the file and have the
> module run based on that spreadsheet?  If so, what is the VB code for
> browse?  I would appreciate the help.  Thanks!

> (code from Module that points to a specific excel spreadsheet):
> oConnection.ConnectionProperties("Data Source") = "C:\Documents and
> Settings\pnadeem\Desktop\current contract list 10-4-02.xls"

> Module1 (DTS):

> 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

'---------------------------------------------------------------------------

- Show quoted text -

> 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

'---------------------------------------------------------------------------

- Show quoted text -

> 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

'---------------------------------------------------------------------------

- Show quoted text -

> '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 =

...

read more »

 
 
 

1. Same DTS run time different big different at two PC

I have one DTS package, including several steps.

It took 10 minutes to run in one PC, 10 hours in another
PC.

Both PCs connected to SQL server withthe same user ID (
SQL authentication) in diferent. And they are of the same
hardware configuration.

 What would be the possible factor for such a big
runtime difference?

I would appreciate if any one cam give me some glue.

Thanks

Simon

.

2. BMP or WMF file to Access 2 OLE Field

3. Same DTS package, different run time in different PC

4. Will DBCC reindex chew up lots of space in the data file?

5. DTS performance: Same DTS , similiar data, big run time different

6. Unicode

7. Running a DTS stored in bas module

8. In Memory Table

9. VB Module from DTS how do I run in VB

10. How to compose 1 single file from N different tables or files with a DTS

11. Running Batch file(.bat) through DTS - does not work on scheduling this DTS

12. ActiveX step running different scheduled than on DTS Designer

13. Browse for File in DTS Package