从.NET应用程序运行包时如何设置SSIS包动态.mdb连接

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.

我目前正在开发一个在后端使用.mdb(Access)数据库文件的VB.NET桌面应用程序。 .mdb文件在VB.NET应用程序中打开和编辑。编辑完成后,用户需要将数据导入我们的SQL Server数据库。这是一项简单的任务,直到您尝试引入动态文件路径方案。

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!).

我对SSIS有相当多的经验,但在接近这个方面遇到了一些麻烦。我创建了一个WinForm,允许用户浏览他们选择的.mdb文件。 .mdb文件名中包含时间戳/日期信息,以使其唯一。 (我没有开发应用程序的这一部分,我最近开始研究这个问题,因此问题。我不会给.mdb文件名加时间戳,但这是我*使用的!)。

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!!)

无论如何,我需要动态地将文件名和路径传递给我的SSIS包。我看到你在哪里为ConnectionString等创建变量。但我真的不确定细节和我应该用于MS Access的数据连接类型(Ole DB,ODBC或Jet 4.0 for Office ?? sheesh有很多! !)

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?

我还假设我的数据连接的连接字符串必须是动态的,因为包将使用.mdb文件作为SOURCE。但是,如何在SSIS中为.mdb文件创建动态数据连接?

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

如何将文件名/路径字符串传递给我的SSIS包?

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 .mdb file location to my SSIS package? LoadPackage maybe?

如何将.mdb文件位置传递给我的SSIS包? LoadPackage可能吗?

How do I use the dynamic file location in my Data Connection for my SSIS package?

如何在我的数据连接中为我的SSIS包使用动态文件位置?

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?

上传非动态的.mdb文件非常简单。在介绍用户选择的DYNAMIC方面之前,设置源和目标非常容易。这怎么可能在SSIS?

any help is greatly appreciated. Thank you.

任何帮助是极大的赞赏。谢谢。

3 个解决方案

#1

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.

这将改变一些,具体取决于您使用的连接和执行方法,但将变量放入包中基本相同。自从我使用SSIS以来已经有一段时间了,所以它可能有点过时了。但是,一旦你弄清楚了最初的步骤,它就会变得非常简单。

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.

在包中创建一个变量来保存文件名。单击设计器曲面以确保您处于包级别范围,打开“变量”窗口,单击“新建”并指定变量的名称(“filePath”),类型(“String”),并为其指定默认值。

Set the variable from your VB.Net code:

从VB.Net代码设置变量:

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;")

根据您用于导入Access数据库的连接,您可能需要执行一些不同的操作。如果您使用OLEDB连接,则存在连接字符串和ServerName属性。设置ServerName属性以使用具有文件路径的变量(@ [User :: filePath])和连接字符串也使用filePath变量(“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.

查看此文本文件示例,了解如何使用表达式动态设置连接字符串。这个用于Access,但是数据库中的连接字符串很复杂。

#2

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.

我没有从程序中调用SSIS包,所以我不知道如何以这种方式配置它们。我知道当我使用OLEDB连接管理器并使用“Native OLE DB Jet 4.0 Provider”时,我可以将连接管理器的ServerName属性设置为.MDB文件的路径。

#3

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

我通过将文件移动到imports.mdb来处理此问题。然后连接管理器是静态的。

#1