Very Computer

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 »

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

news:6ea2f09e.0211141245.50eafad7@posting.google.com...
'---------------------------------------------------------------------------
'---------------------------------------------------------------------------

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

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

...

read more »