I am currently creating dynamic SSIS packages that import/export and access data between a SQL Server and several Access DB files. (Jet files if you want to get technical.)
Anyways, everything is successful during testing, as long as my SSIS packages have hard-coded connection strings to the Access file. This is great and works fine. I am pleased with this.
The problem now begins when I change my VB.NET application to use dynamic connection strings to the Access DB file (the destination file, where the data will be placed). I have the Access DB file stored in my application as an "embedded resource".
Here is the code I use to create my Access destination file:
Public Sub CreateDestinationFile(ByVal path As String)
'Create destination file from embedded project resources
Dim asm = System.Reflection.Assembly.GetExecutingAssembly()
Dim objStream As System.IO.Stream = asm.GetManifestResourceStream("XXX.XXX_Export.mdb")
Dim abytResource(objStream.Length) As [Byte]
Dim intLength As Integer = objStream.Read(abytResource, 0, objStream.Length)
Dim objFileStream = New FileStream(path + "XXX_Export.mdb", FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite)
Try
While intLength > 0
'write filestream to create Access DB file
objFileStream.Write(abytResource, 0, Convert.ToInt32(objStream.Length))
intLength = objStream.Read(abytResource, 0, objStream.Length)
End While
'close the file stream
objFileStream.Close()
Catch ex As Exception
'write error log here - ** omitted
Finally
asm = Nothing
objStream = Nothing
objFileStream = Nothing
End Try
End Sub
This works fine and it does produce the correct results, an Access DB file wherever I provide a path to. This works well when my SSIS packages have hard-coded connection strings.
once I change the connection strings to be dynamic, and re-run the same exact test, I get this error:
"Record(s) cannot be read; no read permission on 'MSysAccessObjects'"
My connection string regular expression in my SSIS package looks like this:
--SQL connection string
"Data Source=" + @[User::sourceDatabaseLocation] + ";User ID=" + @[User::sourceDBUserID] + ";Password=" + @[User::sourceDBPassword] + ";Initial Catalog=" + @[User::sourceDBName] + ";Provider=SQLOLEDB.1;Persist Security Info=True;Auto Translate=False;"
--Access connection string
"Data Source=" + @[User::destinationDatabasePath] + ";Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Password=;"
When I navigate to this file on my local hard drive, and attempt to open it, it prompts me that it is in an unrecoverable state and to repair it, which it never successfully does.
Originally I thought this was a permission error, regarding the user role of the Access DB file and SSIS trying to use it. But I don't think that is it. The user is set to Admin and should (in theory) work.
I think to hack/fix this I will currently try to NOT use embedded resources. I will use FileIO calls to move the file explicitly to the folder I want and populate it from there. Does anyone know why the embedded resource db file would not work, but the same file does work when not produced from an embedded resource? Is there something that is not completing when I create my file from the resource?
Any feedback or suggestions are greatly appreciated. Any questions are welcomed too. Thank you.
**** Update/07/18/2009:**
I modified my [CreateDestinationFile] routine to perform a direct File/IO copy, instead of using an embedded resource.
Here is the code for that:
Dim sPath As String = My.Application.Info.DirectoryPath + "\databasenamehere.mdb"
FileIO.FileSystem.CopyFile(sPath, path + "databasenamehere.mdb", True)
The file is correctly copied from the project, but I now receive this error:
"An OLE DB error has occurred. Error code: 0x80040E09. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80040E09 Description: "Record(s) cannot be read; no read permission on 'TABLE_XXXXX'."
This leads me to believe that SSIS does not have proper permissions to use my local MS Access DB as a destination file.
This is strange to me because the same file works if I hard-code the connection string to it in my SSIS package. What is going on here?
As you can see in my connection string expressions, I have [Admin] as the user. So this should work, right? Also, another possible culprit of this problem is the fact that this is a legacy MS Access DB created in Access 2003, and I am using Access 2007 on my box. Help?
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