SSIS将Varchar2转换为DT_STR [英] SSIS converts Varchar2 to DT_STR

问题描述

我们有一个SSIS包，用于将数据从Oracle数据库下载到SQL Server数据仓库.对于该数据仓库，设置了几个环境；这些环境包括:开发，测试和生产.开发和测试共享一台机器，Prod是独立的.

在PROD计算机上运行SSIS程序包时，它将varchar2列从我们的Oracle源数据库下载到DT_WSTR格式的MSSQL，并将其保存到NVarchar列. IE.所有涉及的步骤都支持Unicode.

当在DEV/Test框上对相同的源数据库运行相同的程序包时，它将以某种方式将外部列视为Varchar，将其派生到数据流中的DT_STR，并拒绝将其存储在NVarchar列中. /p>

所有操作系统均为Win2K8r2，MSSQL 2008 64位.该程序包以32位模式运行，从BIDS或SQL Agent运行时，会看到相同的行为.

有人在乎为什么吗?我已经看到了禁用验证外部元数据的建议( https://stackoverflow.com/a/18383598/2903056 )，但这不是针对我们情况的实际建议.

解决方案

我知道一个老问题，但似乎仍然很重要.而且由于我在过去的三个月中一直找不到合适的答案，因此我认为现在是发布我的发现的最佳时机.

我也有同样的好奇行为，终于能够解决它. 我的布局看起来像这样:

• Windows 2003 Server上的Oracle 10g R2数据库(我们称其为ORA)
• 装有Windows 8，Visual Studio 2012 + SSDT，Sql Express 2012， ODT 12.1.0.21(让其称为DEV)
• Windows 2012 Server，Oracle Client 11.2上的Sql Server 2012(我们称其为TEST)

DEV和TEST都连接到ORA. DEV将VARCHAR2列报告为DT_WSTR，而TEST则坚持认为它们是DT_STR.

然后我在TEST上安装了ODT 12.1.0.21，问题得以解决.值得注意的是，我在安装过程中使用了机器范围"选项.我不确定会带来多大的影响.

Oracle OleDb提供程序在不同版本的客户端组件之间返回的数据类型似乎有所不同.

We have an SSIS package downloading data from an Oracle database to an SQL Server datawarehouse. For this datawarehouse, several environments are set up; Development, Test and Production. Dev and test share a machine, Prod is stand-alone.

When the SSIS package is run on the PROD machine, it downloads the Varchar2 columns from our Oracle source database to MSSQL in DT_WSTR format and saves this to a NVarchar column. I.E. all steps involved support Unicode.

When this same package is run against the same source database on the DEV/Test box, it somehow sees the external columns as being Varchar, derives this to DT_STR in the data flow and refuses to store this in an NVarchar column.

All OS's are Win2K8r2, MSSQL 2008 64 bits. The package is run in 32bits mode, same behaviour is seen when run from BIDS or from SQL Agent.

Anyone care to guess why? I've already seen the suggestion to disable validating external metadata (https://stackoverflow.com/a/18383598/2903056), but that's not a practical suggestion for our situation.

解决方案

An old question I know, but seems to still be relevant. And since I could not find a suitable answer in the last 3 months I have been searching, I figure now is as good a time as any to post my findings.

I have had the same curious behaviour and have finally been able to resolve it. My layout looked like this:

• Oracle 10g R2 database on Windows 2003 Server (lets call it ORA)
• Dev machine with Windows 8, Visual Studio 2012 + SSDT, Sql Express 2012, ODT 12.1.0.21 (lets call that DEV)
• Sql Server 2012 on Windows 2012 Server, Oracle Client 11.2 (lets call that TEST)

Both DEV and TEST were connecting to ORA. DEV was reporting VARCHAR2 columns as DT_WSTR while TEST would insist that they are DT_STR.

I then installed ODT 12.1.0.21 on TEST and the problem was solved. Notably, I used the "machine wide" option during the install. I am not sure how much of an impact that had.

There seems to be a difference in the datatypes that are returned by the Oracle OleDb providers across the different versions of the client side components.

这篇关于SSIS将Varchar2转换为DT_STR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！