.NET 嵌入式资源的读取权限问题 - Access DB 文件和数据库 SSIS
我目前正在创建动态 SSIS 包,用于在 SQL Server 和多个 Access DB 文件之间导入/导出和访问数据。 (如果您想了解技术,请使用 Jet 文件。)
无论如何,只要我的 SSIS 包具有到 Access 文件的硬编码连接字符串,测试期间一切都会成功。 这很棒并且工作正常。 我对此很满意。
当我将 VB.NET 应用程序更改为使用 Access DB 文件(数据将放置的目标文件)的动态连接字符串时,问题就开始了。 我将 Access DB 文件作为“嵌入式资源”存储在我的应用程序中。
下面是我用来创建 Access 目标文件的代码:
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
这工作正常,并且确实会产生正确的结果,无论我提供路径,都会生成一个 Access DB 文件。 当我的 SSIS 包具有硬编码连接字符串时,这很有效。
一旦我将连接字符串更改为动态,并重新运行相同的测试,我会收到此错误:
“无法读取记录;‘MSysAccessObjects’没有读取权限”
我的 SSIS 包中的连接字符串正则表达式如下所示:
--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=;"
当我导航到本地硬盘上的此文件并尝试打开它时,它会提示我知道它处于不可恢复的状态并修复它,但它从未成功完成。
- 我是否忽略了有关文件创建的某些内容? (IO?)
- 我是否忽略了有关嵌入式资源的一些事情? (它们对我来说似乎很简单,但也许我忽略了一些明显的事情?)
- 我的文件状态是否已损坏? 我可以在 VS.NET IDE 中打开它,也可以使用 MS Access 在本地打开它。
- 是否值得重新创建此 Access 文件? 我读过您可以将架构复制到新文件以避免修复? 这听起来太危险了!
最初我认为这是一个权限错误,涉及 Access DB 文件的用户角色和 SSIS 尝试使用它。 但我不认为就是这样。 用户设置为管理员并且应该(理论上)工作。
我认为要解决这个问题,我目前将尝试不使用嵌入式资源。 我将使用 FileIO 调用将文件显式移动到我想要的文件夹并从那里填充它。 有谁知道为什么嵌入式资源数据库文件不起作用,但同一个文件在不是从嵌入式资源生成时却可以工作? 当我从资源创建文件时,是否有某些内容未完成?
非常感谢任何反馈或建议。 也欢迎任何问题。 谢谢。
**** 更新/07/18/2009:**
我修改了我的 [CreateDestinationFile] 例程以执行直接文件/IO 复制,而不是使用嵌入式资源。
这是代码:
Dim sPath As String = My.Application.Info.DirectoryPath + "\databasenamehere.mdb"
FileIO.FileSystem.CopyFile(sPath, path + "databasenamehere.mdb", True)
该文件已从项目中正确复制,但我现在收到此错误:
“发生 OLE DB 错误。错误代码:0x80040E09。OLE DB 记录可用。来源:“Microsoft JET 数据库引擎” Hresult:0x80040E09 描述:“无法读取记录; “TABLE_XXXXX”没有读取权限。”
这让我相信 SSIS 没有适当的权限来使用我的本地 MS Access DB 作为目标文件。
这对我来说很奇怪,因为如果我对连接进行硬编码,同一个文件就可以工作 是
正如您在我的连接字符串表达式中看到的那样,这应该可以工作,对吗?另外,这个问题的另一个可能的罪魁祸首 事实上,这是在 Access 2003 中创建的旧版 MS Access DB,而我在我的盒子上使用 Access 2007。 帮助?
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.
- Am I overlooking something regarding my file creation? (IO?)
- Am I overlooking something regarding Embedded Resources?
(they seem pretty straight forward to me,but maybe I'm overlooking something obvious?) - Is the state of my file corrupt? I can open it in VS.NET IDE and locally, with MS Access.
- Is it worthwhile to recreate this Access file? Ive read that you can copy the schema to a new file to avoid repairs? This sounds SOOOOO risky!!
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这不会是一个非常有帮助的答案,我不在你正在工作的环境中工作,所以永远不必这样做,但我想到了一些事情。
您使用 admin 作为用户和空白密码,这是默认的 Jet 登录,但可能使用了错误的工作组文件。 我记得其他 StackOverflow 发布者在通过某些方法连接到 Jet 文件时遇到问题,这些方法的连接字符串中没有工作组文件参数。 您是否尝试过创建 ODBC DSN,然后尝试连接到它? DSN 允许您指定工作组文件,以便可以改善该问题。 然后,您可以尝试将生成的连接字符串转换为无 DSN 连接字符串,然后将其用作参数化连接字符串的模型。
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.
为了解决这个问题,我最终使用了在初始项目收集期间提供给我的模板文件。
我正在使用的 .mdb 文件是针对众多应用程序和测试项目进行开发和测试的。 该文件从第一天起就存在问题。
我遇到的第一个问题是该文件大小为 80 MB。 这对我来说很奇怪,因为里面的数据很少。 当我意识到我需要“压缩和修复”时,它的大小减少到了不到 200 kb。 这让我很困惑。 但我继续使用这个文件进行进一步的开发,现在我意识到这是一个很大的禁忌。
我最终决定从我的电子邮件中挖掘原始 .mdb 文件,该文件是我继承该项目时提供的。 这个原始的 .mdb 仍然不理想,因为它在我要导出到的表中包含数据。 我必须手动从中删除数千条记录。 一旦我这样做了,我的 SSIS 包就奇迹般地工作了。 我现在能够动态地将数据从 SQL 导出到 Access。
下面是我对 SSIS 包的 VB.NET 执行,它使用 [app.config] 配置文件提供的动态连接字符串:
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: