How to establish a dynamic .sdb connection of an SSIS package when launching a package from a .NET application
I am currently working on a VB.NET desktop application that uses .mdb (Access) database files on the backend. .Mdb files are opened and edited in a VB.NET application. After finishing editing, users will need to import data into our SQL Server database. This is not an easy task until you try to imagine a dynamic file policy scenario.
I have a lot of experience with SSIS, but I am having trouble approximating this. I have created a WinForm that allows the user to view the .mdb file of their choice. The .mdb file names contain a timestamp / date in them to make them unique. (I didn't develop this part of the application, I recently started working on it, hence the problems. I won't mark the name of the .mdb file, but that's what I forced to use!).
Anyway, I need to dynamically pass the filename and path to my SSIS package. I see where you create variables for ConnectionString etc. But I'm really not sure about the details and about what type of data connection I should be using for MS Access (Ole DB, ODBC, or Jet 4.0 for Office), there's a lot out there !!)
I also assume that my data connection connection string needs to be dynamic as the package will use the .mdb file as SOURCE. But how do you create a dynamic data connection in SSIS for .mdb files?
And how do I pass the filename / pathname 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
- How do I pass the location of the .mdb file to my SSIS package? LoadPackage maybe?
- How do I use dynamic file locations in my data connection for my SSIS package?
It is quite easy to load a .mdb file that is not dynamic. Setting up the source and destination is pretty straightforward, as long as you don't understand the DYNAMIC aspect of user selection. How is this possible in SSIS?
any help is appreciated. Thanks.
a source to share
This will change some of them, depending on the connection and execution method you are using, but it is mainly about getting the variables in the package. It has been a while since I have been using SSIS, so there may be a little. But this is mostly straight forward once you get the initial steps.
To get the variables in a package:
- Create a variable in the package to store the filename. Click on the design surface to make sure you are in the package level area, open the Variables windows, click New and specify a variable name ("filePath"), type ("String") and give it a default value.
- Set the variable from 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 might have to do a few different things. If you are using an OLEDB connection, there is a connection string and a ServerName property. Set the ServerName property to a variable that has a file path ( @[User::filePath]
) and a connection string to also use the filePath ( "Data Source=" + @[User::filePath] + ";Provider=Microsoft.Jet.OLEDB.4.0;"
) variable
Check this example text file to use expressions to dynamically set the connection string. This one is for Access , but tricky with connection strings from the database.
a source to share
I am not calling SSIS packages from programs, so I don’t know how to configure them this way. I know that when I use the OLEDB Connection Manager and use the "Native OLE DB Jet 4.0 Provider", I can set the ServerName property of the Connection Manager to the path to the .MDB file.
a source to share