SSIS 连接 PostgreSQL

因为工作需要,得把psql的表放到SQL Server, 找到一个PGNP(http://www.pgoledb.com/)  的适配器,不过一看要300$就没有去尝试了. 官方倒是有ODBC的驱动.以下使用ODBC操作的过程记录.

1. 下载PostgreSQL ODBC驱动

http://www.postgresql.org/ftp/odbc/versions/msi/

2. 配置ODBC  Source
安装好以后 ODBC Data Sources 配置工具里面,会多出 PostgreSQL ANSI和 PostgreSQL Unicode 两个驱动.  从字面意思.我选了Unicode.

配置好相关登陆信息

3. SSIS 配置

本来使用ODBC Source 适配器(官方号称新版本推荐用这个= = ) ,连接到odbc连接以后提示我表中有个lob字段,读取的时候只能一行行处理. 后来执行还报错,预览没报错,执行报错,也看不出什么问题= =.

SSIS package "E:\File\Dooioo\ETL\Dooioo\Tableau\Tableau.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC0014020 at Tableau, Connection manager "Tableau": An ODBC error -1 has occurred.
Error: 0xC0014009 at Tableau, Connection manager "Tableau": There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server.
Error: 0x20F at Data Flow Task, Tableau Source [74]: The AcquireConnection method call to the connection manager Tableau failed with error code 0xC0014009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: Tableau Source failed validation and returned error code 0x80004005.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "E:\File\Dooioo\ETL\Dooioo\Tableau\Tableau.dtsx" finished: Failure.
The program '[14216] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

最后使用了 搞定.

4. 碰到的问题

如果各位小伙伴能解答还请赐教

    a) 像LOB字段这种导致ODBC Source 要一行行读取的,在不改动源数据库的情况下有什么办法处理么?

    b) 如果Destination 也用ODBC, 我发现用 SQL Server Native Client 10.0和11.0 会报日期溢出错误 (数据源日期类型为  timestamp without time zone ). 但是最老的SQL Server ODBC驱动不会报错.