I am trying to change the file that is imported using DTS. Here is my code so far:
[vb]
'change DTS Package
        Dim oPKG As DTS.Package, oStep As DTS.Step
        oPKG = New DTS.Package
        Dim oConn As DTS.Connection

        Dim sServer As String, sUsername As String, sPassword As String
        Dim sPackageName As String, sMessage As String
        Dim lErr As Long, sSource As String, sDesc As String

        ' Set Parameter Values
        sServer = "CRSERVER"
        sUsername = "brenda"
        sPassword = "g_Password"
        sPackageName = "ImportNewAccurintDataHigh"

        ' Load Package
        oPKG.LoadFromSQLServer(sServer, sUsername, sPassword, _
            DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrus tedConnection, , , , sPackageName)

        'Set Filename
        Dim ofd As New OpenFileDialog
        With ofd
            .Title = "Choose An Excel File"
            .InitialDirectory = "C:\Accurint\"
            .Filter = "XLS (*.xls)|*.xls|All files (*.*)|*.*"
            .FilterIndex = 2
            .RestoreDirectory = True
            .Multiselect = False
        End With
        ofd.ShowDialog()
        oPKG.Connections.Item("Connection 1").DataSource = ofd.FileName
        oConn = Nothing

        ' Set Exec on Main Thread
        For Each oStep In oPKG.Steps
            oStep.ExecuteInMainThread = True
        Next

        ' Execute
        oPKG.Execute()

        ' Get Status and Error Message
        For Each oStep In oPKG.Steps
            If oStep.ExecutionResult = DTS.DTSStepExecResult.DTSStepExecResult_Failure Then
                oStep.GetExecutionErrorInfo(lErr, sSource, sDesc)
                sMessage = sMessage & "Step """ & oStep.Name & _
                    """ Failed" & vbCrLf & _
                    vbTab & "Error: " & lErr & vbCrLf & _
                    vbTab & "Source: " & sSource & vbCrLf & _
                    vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
            Else
                sMessage = sMessage & "Step """ & oStep.Name & _
                    """ Succeeded" & vbCrLf & vbCrLf
            End If
        Next

        oPKG.UnInitialize()

        oStep = Nothing
        oPKG = Nothing

        ' Display Results
        MsgBox(sMessage)
[/vb]

It is not changing the file though. It is just keeping the same file that is saved in the package already. Anyone have any ideas why?

__________________
Brenda

If it weren't for you guys, where would I be?

I started troubleshooting this and ran into some problems even before I got to the DTS stuff.

You might want to look at the Help File on FileDialog. This example will give you an idea of the syntax:

Dim ofd As FileDialog

Set ofd = Application.FileDialog(msoFileDialogOpen)

        With ofd
            .Title = "Choose An Excel File"
            .InitialFileName = "C:\"
            'Add a filter that includes Excel files
            .Filters.Add "Excel file", "*.xls; *.xlt", 1

            .InitialView = msoFileDialogViewDetails
            'Allow the selection of multiple files.
            .AllowMultiSelect = True

            'Declare a variable to contain the path
            'of each selected item. Even though the path is a String,
            'the variable must be a Variant because For Each...Next
            'routines only work with Variants and Objects.
            Dim vrtSelectedItem As Variant


            'Use the Show method to display the File Picker dialog box and return the user's action.
            'The user pressed the action button.
            If .Show = True Then

                'Step through each string in the FileDialogSelectedItems collection.
                For Each vrtSelectedItem In .SelectedItems

                    'vrtSelectedItem is a String that contains the path of each selected item.
                    'You can use any file I/O functions that you want to work with this path.
                    'This example simply displays the path in a message box.

                    MsgBox "The path is: " & vrtSelectedItem

                Next vrtSelectedItem
            'The user pressed Cancel.
            Else
                Exit Sub
            End If '.Show = True
        End With 'ofd

=========
You would substitute my Msgbox with your procedure to run the DTS actions (substitute the names somehow).
I am trying to steer away from passing global variables.

I would like to know what you find out ... I have a DTS package that copies 3 Excel files to a SQL-Server table then proceeds to process them. Everytime, I have to manually go in and change the name of the the 3 DTS elements so that they point to the new (ever changing) Excel files.

I am trying to use VBA in Excel or MS-Access to select the file names, substitute the names, then execute the DTS package.

Thanks,



--- Tom