Read Permission problem on .NET Embedded Resource - Access DB file & SSIS

Answer 1

This is not going to be a terribly helpful answer, I don't work in the environment you're working in so never have to do this, but a couple of things come to mind.

You are using admin as user and blank password, which is the default Jet logon, but maybe it's using the wrong workgroup file. I recall other StackOverflow posters having problems with connecting to Jet files by certain methods that have no workgroup file argument in their connect string. Have you tried created an ODBC DSN and then trying to connect to that? The DSN allows you to specify the workgroup file so that might ameliorate the problem. You could then try converting the resulting connect string to a DSN-less connect string and then using that as your model for the parameterized connect string.

Answer 2

To resolve this problem, I ended up using a template file that was provided to me during initial project gathering.

The .mdb file I was working with had been developed against and tested against by numerous applications and test projects. This file has been problematic since day one.

My first problem with the file was that it was 80 mb in size. This was very odd to me, because there is very little data in it. Once I realized I needed to "compress and repair", it reduced its size to less then 200 kb. This baffled me. But I continued to use this file for further development, which now I realize was a big no-no.

I finally decided to dig out of my emails the original .mdb file, provided when I inherited this project. This original .mdb is still not ideal, because it has data in it in the tables I am exporting to. I had to manually remove thousands of records from it. Once I did that, my SSIS package magically worked. I now am able to export data from SQL to Access, dynamically.

Here is my VB.NET execution of the SSIS package, which uses dynamic connection strings, provided by the [app.config] configuration file:

Public Function ExecuteSSISExportPackage(ByVal parameterValue1 As String, ByVal destinationDatabasePath As String) As Integer

    Dim pkg As New Package
    Dim app As New Microsoft.SqlServer.Dts.Runtime.Application
    Dim pkgResults As DTSExecResult
    Dim result As Integer = 1 'defaults to failure
    Dim eventListener As New EventListener()

    'create SSIS variables for dynamic parameters, retrieved from the appSettings in the [app.config] file
    Dim SSISPackagePassword As String = ConfigurationManager.AppSettings.Item("SSISPackagePassword")
    Dim SSISExportPackagePath As String = ConfigurationManager.AppSettings.Item("SSISExportPackagePath")
    Dim SSISExportPackageServerName As String = ConfigurationManager.AppSettings.Item("SSISExportPackageServerName")
    Dim SSISExportPackageServerUserName As String = ConfigurationManager.AppSettings.Item("SSISExportPackageServerUserName")
    Dim SSISExportPackageServerPassword As String = ConfigurationManager.AppSettings.Item("SSISExportPackageServerPassword")
    Dim SSISExportPackageDestinationDBName As String = ConfigurationManager.AppSettings.Item("SSISExportPackageDestinationDBName")

    Try
        'set package password
        app.PackagePassword = SSISPackagePassword
        pkg.PackagePassword = SSISPackagePassword

        'load package from SQL server
        pkg = app.LoadFromSqlServer(SSISExportPackagePath, SSISExportPackageServerName, SSISExportPackageServerUserName, SSISExportPackageServerPassword, eventListener)

        'set package-level variables, to supply to the stored procedure parameters/sql calls in the SSIS Export package
        pkg.Variables("xxxx").Value = parameterValue1

        'set the package-level variable to supply the Access DB's (SSIS destination) file path
        Dim databaseName As String = ConfigurationManager.AppSettings.Item("XXXClientDatabaseName")
        pkg.Variables("destinationDatabasePath").Value = "C:\" + databaseName 

        'Dynamic SQL source connection string values
        pkg.Variables("sourceDatabaseLocation").Value = SSISExportPackageServerName
        pkg.Variables("sourceDBUserID").Value = SSISExportPackageServerUserName
        pkg.Variables("sourceDBName").Value = SSISExportPackageDestinationDBName
        pkg.Variables("sourceDBPassword").Value = SSISExportPackageServerPassword

        'Execute the Import SSIS package, add an eventListener object for SSIS reflection
        pkgResults = pkg.Execute(Nothing, Nothing, eventListener, Nothing, Nothing)

        'Package execution results
        Select Case pkgResults

            Case DTSExecResult.Success
                result = 0

            Case DTSExecResult.Failure
                result = 1

        End Select

    Catch ex As Exception

        'Log the exception error here - omitted

    Finally
        app = Nothing
        pkg = Nothing
    End Try

    Return result

End Function