SSIS - 将多列值转换为空 [英] SSIS - Convert Multiple Column Values To Null

问题描述

我正在使用 SSIS (SQL Server 2008 R2) 将输入的 CSV 文件转换为 SQL 表.输入文件中的五列(实数 - 例如 19.54271)偶尔会出现错误值(字符串 - 例如NAN")，导致包失败.

I am using SSIS (SQL Server 2008 R2) to transform an input CSV file into an SQL table. Five columns in the input file (reals - e.g. 19.54271) occasionally have a bad value (strings - e.g. "NAN") that cause the package to fail.

检查这 5 列是否存在错误值NAN"、将其转换为 NULL 值或已知错误数字 (-9999) 并将更正后的值写入同一个最终 SQL 表的最简单方法是什么?

What is the simplest way to check these 5 columns for the bad value "NAN", convert that into either a NULL value or known bad numeric (-9999), and write the corrected values into the same final SQL table?

到目前为止，我有以下一团糟，最后决定问是否有更简单的方法...

I have the following mess going so far, and finally decided to ask if there is a simpler way...

我目前的条件逻辑:

My current conditional logic:

My Case1 派生列转换:

My Case1 Derived Column Conversion:

注意:仍然不确定是否可以将其他派生列合并到一个实例中，但由于我的目的地只能有一个输入，我怀疑我需要...

Note: Still not sure if I can combine the other derived columns into one instance, but since my destination can have only one input, I suspect I will need to...

TIA

推荐答案

如果我的逻辑比简单地将错误值转换为空值更复杂，那么使用脚本组件似乎是最好的处理方式.

It looks like using a script component would be the best way to proceed if my logic had been more complex than simply converting bad values into nulls.

然而，转换对象的逻辑相当简单，所以希望这可以帮助其他人:

However, the logic with transformation objects is fairly straightforward, so hopefully this can help someone else:

包(请注意，我重定向了数据源中可疑列的行):

The package (note that I redirect rows for suspect columns in the datasource):

条件拆分逻辑:

派生列逻辑:

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