通过 Jet 的 SSIS 平面文件访问 [英] SSIS FlatFile Access via Jet

问题描述

有没有办法在 SSIS 中使用 Microsoft.Jet.OLEDB.4.0 驱动程序访问 FlatFiles?

通过 FlatFile Source 的访问要好得多，这只是关于是否有办法使用 Jet 驱动程序来做到这一点.

解决方案

这似乎是一个有趣的问题，所以我在它周围闲逛了一下.是的，您绝对可以使用 JET 驱动程序读取平面文件.如何:使用 Jet OLE DB Provider 4.0 连接到 ISAM 数据库请参阅开放文本部分

默认情况下，它期望文件为 CSV，但您可以在 Schema.INI 将与连接管理器指向的文件夹位于同一文件夹中.

关于 CM 需要注意的一点是，它指向文本文件的文件夹，而不是特定文件.

创建连接管理器时，您需要进入全部"选项卡(在选择本机 OLE DB\Microsoft Jet 4.0 OLE DB 提供程序后)，然后添加扩展属性.我能够使它与 CSVDelimited 的 FMT 一起工作，并且只是 Delimited(因为我的示例文件是一个 csv).

在源文件中交换制表符的逗号并在 TabDelimited 设置 FMT 似乎在连接管理器属性中不起作用，但我没有尝试按照 BOL 文章的指示创建 schema.ini 文件.

<块引用>

您不能通过连接字符串.例如，如果你想打开一个固定宽度的文件，或者您想使用逗号以外的分隔符，您必须在 Schema.INI 文件中指定所有这些设置.

我的 CM 上 ConnectionString 的完整值如下

Data Source=C:\tmp\so\;Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="text;HDR=Yes;FMT=CSVDelimited;";

如果程序包在设计时运行良好，但一旦运行就停止运行，则 JET 驱动程序仅在 64 位机器上以 32 位可用，如错误消息所示.

<块引用>

SSIS 错误代码 DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.对连接管理器的 AcquireConnection 方法调用OLEDB_JET"失败，错误代码为 0xC0209303.可能有错误在此之前发布的消息包含更多关于为什么AcquireConnection 方法调用失败.

解决这个问题的方法是在 32 位模式下从命令行运行它

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn>.\dtexec/file C:\sandbox\SSISHackAndSlash\SSISHackAndSlash\so_JetFlatFile.dtsx

Is there a way to access FlatFiles with the Microsoft.Jet.OLEDB.4.0 driver in SSIS ?

The acces via the FlatFile Source is much better, it´s just about if there exists a way to do it with the Jet driver.

解决方案

This seemed an interesting question so I piddled around a bit with it. Yes, you can definitely use the JET driver to read a flat file. HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases See Open Text section

By default, it expects the file to be a CSV but you can specify the formatting in a Schema.INI which would be in the same folder as the connection manager is pointing to.

One thing to note about the CM, it points to the folder of the text files, not a particular file.

When you create your Connection Manager, you will need to go into the All tab (after selecting the Native OLE DB\Microsoft Jet 4.0 OLE DB Provider) and then add Extended Properties. I was able to make it work with a FMT of CSVDelimited and just Delimited (as my sample file was a csv).

Exchanging the commas for tabs in the source file and setting the FMT at TabDelimited did not appear to work in the connection manager property but I did not try creating a schema.ini file as the BOL article indicated.

You cannot define all characteristics of a text file through the connection string. For example, if you want to open a fixed-width file, or you want to use a delimiter other than the comma, you must specify all these settings in a Schema.INI file.

The full value of the ConnectionString on my CM is below

Data Source=C:\tmp\so\;Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="text;HDR=Yes;FMT=CSVDelimited;";

If the package works fine at design time but goes belly up once it runs, the JET driver is only available as 32 bit so on a 64bit machine as the error message would indicate.

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "OLEDB_JET" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

The solution to this is to run it from the command-line in 32bit mode like

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn>.\dtexec /file C:\sandbox\SSISHackAndSlash\SSISHackAndSlash\so_JetFlatFile.dtsx

这篇关于通过 Jet 的 SSIS 平面文件访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！