SSIS 中作为源的 Oracle 查询定义了错误的数据类型 [英] Oracle query as source in SSIS defines wrong data types

问题描述

我有一个有点复杂的查询，我想将其用作 SSIS 包中的源.我创建了我的 OLE DB 源，将访问模式指定为 SQL，并将我的查询粘贴到命令文本框中.当我点击预览时，示例数据回来了，一切看起来都很好.

I have a somewhat complex query that I want to use as a source in an SSIS package. I create my OLE DB Source, specify the access mode as SQL, and paste my query in the command textbox. When I click Preview, sample data comes back and everything looks good.

但是，当我尝试运行该程序包时，我返回外部列与数据源列不同步"和一堆需要从外部列中删除外部列‘xxx’"的错误消息.请注意，在设计模式下，错误列表选项卡中没有条目.

However, when I try to run the package I get back "external columns are out of sync with data source columns" and a mess of "external column 'xxx' needs to be removed from the external columns" error messages. Note that in design mode there are no entries in the Error List tab.

在我的研究中，我发现外部、输出和错误列被自动定义为错误的数据类型.一些明显的整数列被定义为字符串，而其他的则被设置为 unicode 字符串.

Doing my research I see that external, output, and error columns are being automatically defined with the wrong data types. Some obvious integer columns are being defined as strings whereas others are set as unicode strings.

我尝试通过高级编辑器手动修复它们，但您似乎无法更改错误列.但是，如果我修复了外部和输出源列，我会收到有关不同步的消息……您想要修复它吗?我点击是，它把它放回字符串...

I tried to manually fix them via the advanced editor but it seems that you cannot change the error columns. However, if I fix the external and output source columns I get that message about being out of sync...do you want it fixed? I click yes and it puts it back to strings...

感谢您的任何见解.

推荐答案

您遇到的问题可以通过多种方法之一解决.

The problem you are having can be solved from one of several methods.

首先，如果您尝试使用高级编辑器来更改此设置(我不建议这样做，因为维护人员很难看到您所做的工作)，您必须同时更改外部和输出源列的设置同步.当您单击是"时，修复"是两者之间的不匹配.

First if you are trying to use the advanced editor to change this (which I recommend against, as it is difficult for maintainers to see what you have done), you must change the settings for both the external and output source columns in sync. What it is 'Fixing' when you click yes is the mismatch between the two.

其次，您可以将它们全部保留为字符串，并使用数据转换组件转换为您需要的类型.这对未来的维护者来说更容易.

Second, you can leave them all as strings and use the Data Conversion component to convert to the types you need. This is easier for future maintainers.

我更喜欢使用 oracle sql 语句来设置我在 pl/sql 中需要的类型，以便 SSIS 创建具有正确类型的字段.这允许定义是明确的，因此维护人员可以看到做了什么.为此，我使用 sql 查询从目标表的结构构建我的 oracle select 语句(在我为其构建的项目中，我们在应用转换之前将所有数据/结构从 Oracle 完整地导入到暂存 Sql Server 机器中.)
https://docs.google.com/leaf?id=0B4aVrSS2ke2IZGVkYWJkOWYtY2Y3Yy00MDI5LTkyMDctYjgwMGY2YzZiODRm&hl=zh

I prefer to use an oracle sql statement that sets the types I need in the pl/sql so that SSIS creates the fields with the proper types. That allows the definition to be explicit so maintainers can see what was done. To that end I use a sql query to build my oracle select statement from the structure of the destination table (In the project I built this for, we bring all the data/structure intact from Oracle into a staging Sql Server machine before transformations are applied.)
https://docs.google.com/leaf?id=0B4aVrSS2ke2IZGVkYWJkOWYtY2Y3Yy00MDI5LTkyMDctYjgwMGY2YzZiODRm&hl=en

这篇关于SSIS 中作为源的 Oracle 查询定义了错误的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！