如何防止SSIS上发生CAST错误？ [英] How to prevent CAST errors on SSIS?

问题描述

问题

是否可以要求SSIS 投射值 >和返回NULL ，以防强制转换而不是抛出错误？

？

我的环境

我正在Windows Server 2003上使用Visual Studio 2005和Sql Server 2005。

一般情况

如果您好奇的话，这里是我的用例。我必须将来自通用表（具有历史记录的键/值结构）中某处的数据存储起来，其中包含某种可以是字符串，数字或日期的值。结构是这样的：

 表值{
 Id int，
 Date datetime，-历史记录
键nvarchar（50）不为null，
值nvarchar（50），
 DateValue日期时间，
 NumberValue数字（19,9）
} 
  

我想将原始值放在值列中并尝试输入相同的值

$ b当我能够将其强制转换为Datetime时，将DateValue列中的
$ b

将其转换为数字

这两个类型化的列将使以后的聚合和操作变得更加容易和快捷。

就是这样，现在您知道为什么我要问这个奇怪的问题了。

======== ====

在此先感谢您的帮助。

解决方案

您还可以尝试使用派生列组件并测试潜在日期/数字字段或s的值暗示将其强制转换，并将任何错误重定向为这两个字段的NULL值。

（1）如果每次仅使用以下语句强制转换该字段，则派生列组件：（DT_DATE）[MYPOTENTIALDATE]-您可以重定向未通过此强制类型转换的行并从那里操作数据。

OR

（2）您可以在派生列组件中执行以下操作：ISNULL（[MYPOTENTIALDATE]）？ ‘2099-01-01’：( DT_DATE）[MYPOTENTIALDATE]。我通常会在日期为NULL时发送 2099-01-01，而不是将其弄乱为NULL（与Cubes等配合使用，效果更好）。

当然（2）如果[MYPOTENTIALDATE]字段是通过DATETIME或NULL以外的其他方式来传递的，则该字段将不起作用，即，有时它是一个类似于 hello的词。

是我会探索的选择，祝您好运！

The question

Is it possible to ask SSIS to cast a value and return NULL in case the cast is not allowed instead of throwing an error ?

My environment

I'm using Visual Studio 2005 and Sql Server 2005 on Windows Server 2003.

The general context

Just in case you're curious, here is my use case. I have to store data coming from somewhere in a generic table (key/value structure with history) witch contains some sort of value that can be strings, numbers or dates. The structure is something like this :

table Values {
    Id int,
    Date datetime, -- for history
    Key nvarchar(50) not null,
    Value nvarchar(50),
    DateValue datetime,
    NumberValue numeric(19,9)
}  

I want to put the raw value in the Value column and try to put the same value

• in the DateValue column when i'm able to cast it to Datetime
• in the NumberValue column when i'm able to cast it to a number

Those two typed columns would make all sort of aggregation and manipulation much easier and faster later.

That's it, now you know why i'm asking this strange question.

============

Thanks in advance for your help.

解决方案

You could also try a Derived Column component and test the value of the potential date/number field or simply cast it and redirect any errors as being the NULL values for these two fields.

(1) If you just simply cast the field every time with a statement like this in the Derived Column component: (DT_DATE)[MYPOTENTIALDATE] - you can redirect the rows that fail this cast and manipulate the data from there.

OR

(2) You can do something like this in the Derived Column component: ISNULL([MYPOTENTIALDATE]) ? '2099-01-01' : (DT_DATE)[MYPOTENTIALDATE]. I generally send through '2099-01-01' when a date is NULL rather than messing with NULL (works better with Cubes, etc).

Of course (2) won't work if the [MYPOTENTIALDATE] field comes through as other things other than a DATETIME or NULL, i.e., sometimes it is a word like "hello".

Those are the options I would explore, good luck!

这篇关于如何防止SSIS上发生CAST错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！