How to Set SSIS package dynamic .mdb connection When Running Package from .NET Application

Question

Hello,

I am currently working on a VB.NET desktop application that uses .mdb (Access) database files on the backend. The .mdb files are opened and edited in the VB.NET app. Once editing is completed, the users will need to import the data into our SQL Server database. This is an easy task, until you try to introduce the dynamic filepath scenario.

I have a fair amount of experience with SSIS but am having some trouble with approaching this. I have created a WinForm that allows the user to browse to the .mdb file of their choice. The .mdb filenames have timestamp/date information in them, to make them unique. (I did not develop this part of the app, I recently began working on this, hence the problems. I would not timestamp an .mdb filename but this is what Im forced to use!).

Anyways, I need to pass the filename and path to my SSIS package, dynamically. I see where you create variables for ConnectionString, etc. But I am really unsure of the details and what type of Data Connection I should use for MS Access (Ole DB, ODBC, or Jet 4.0 for Office?? sheesh there are a lot!!)

I also assume that my data connection's connection string must be dynamic, since the package will use the .mdb file as the SOURCE. But how do you create a dynamic data connection in SSIS for .mdb files?

And how do I pass the filename/path string to my SSIS package?

I am currently prototyping with this code:

    'Execute the SSIS_Import package 
    Dim pkgLocation As String
    Dim pkg As New Package
    Dim app As New Microsoft.SqlServer.Dts.Runtime.Application
    Dim pkgResults As DTSExecResult
    Dim eventListener As New EventListener()

    Try
        pkgLocation = "C:\SSIS_DataTransfer\ImportPackage.dtsx"

        'TO-DO: pass databasePath variable to SSIS package here ???
        pkg = app.LoadPackage(pkgLocation, eventListener)
        pkgResults = pkg.Execute(Nothing, Nothing, eventListener, Nothing, Nothing)

        Select Case pkgResults

            Case DTSExecResult.Completion
                MsgBox("Data import completed!")

            Case DTSExecResult.Success
                MsgBox("Data import was successful!")

            Case DTSExecResult.Failure
                MsgBox("Data import was not successful!")

        End Select

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

1. How do I pass the .mdb file location to my SSIS package? LoadPackage maybe?
2. How do I use the dynamic file location in my Data Connection for my SSIS package?

It is quite simple to upload an .mdb file that is NOT dynamic. Setting the source and destination is quite easy until you introduce the DYNAMIC aspect of user selection. How is this possible in SSIS?

any help is greatly appreciated. Thank you.

Answer 1

I don't call SSIS packages from programs, so I don't know how to configure them that way. I do know that when I use an OLEDB Connection Manager and use the "Native OLE DB Jet 4.0 Provider", that I can set the ServerName property of the connection manager to the path to the .MDB file.

Answer 2

This will change some, depending on the connection and execution method you use but it is basically the same to get the variables into the package. It's been a while since I've used SSIS so it may be a little off. But it's mostly straightforward once you get the initial steps figured out.

To get variables into the package:

• Create a variable in the package to hold the file name. Click the designer surface to ensure you are at the package level scope, open the Variables windows, click New and specify the name of the variable ("filePath"), the type ("String"), and give it a default value.
• Set the variable from your VB.Net code:

In VB.NET:

pkg = app.LoadPackage(pkgLocation, eventListener)
' Set the file path variable in the package
' NB: You may need to prefix "User::" to the variable name depending on the execution method
pkg.Variables("filePath").Value = databasePath
pkgResults = pkg.Execute(Nothing, Nothing, eventListener, Nothing, Nothing)

• You should now have access to the variable in the package.

Depending on the connection you are using to import the Access db, you may have to do a few different things. If you are using an OLEDB Connection, there is a connection string and ServerName property. Set the ServerName property to use the variable that has your file path (@[User::filePath]) and the connection string to also use the filePath variable ("Data Source=" + @[User::filePath] + ";Provider=Microsoft.Jet.OLEDB.4.0;")

Check out this text file example for how to use expressions to set the connection string dynamically. This one is for Access but is complicated with connection strings from a database.

Answer 3

IS it possible to use DTS packages(.dts) instead of ssis(.dtsx) in LoadPackage() ????

Answer 4

I approach this by moving the file to an importing.mdb. Then the connection manager is static.