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.
我正在使用SSIS(SQL Server 2008 R2)将输入CSV文件转换为SQL表。输入文件中的五列(实数 - 例如19.54271)偶尔会出现导致包失败的错误值(字符串 - 例如“NAN”)。
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?
检查这5个列的错误值“NAN”的最简单方法是什么,将其转换为NULL值或已知错误数字(-9999),并将更正后的值写入相同的最终SQL表?
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 Derived Column Conversion:
我的Case1派生列转换:
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
2 个解决方案
#1
2
SSIS expressions get hairy and hard to read when the logic is complex or if there are multiple evaluations. In your case you're going to wind up with a bunch of tasks that, individually, do very little.
当逻辑复杂或有多个评估时,SSIS表达式会变得毛茸茸且难以阅读。在你的情况下,你最终将完成一系列任务,这些任务分别做得很少。
I'd bundle this up into a script component. That way you could use basic vb or c# functions to evaulate if all of your columns properly convert to numeric and assign defaults when they don't. Additionally, you can implement a try/catch scenario and gracefully send errors to a different output buffer.
我将它捆绑到一个脚本组件中。这样你就可以使用基本的vb或c#函数来评估你的所有列是否正确转换为数字并在没有时指定默认值。此外,您可以实现try / catch方案并优雅地将错误发送到不同的输出缓冲区。
Here's some examples of how to use the script component as a transformation:
以下是如何使用脚本组件作为转换的一些示例:
http://www.bimonkey.com/2009/09/the-script-transformation-part-1-a-simple-transformation/
http://www.sqlis.com/sqlis/post/The-Script-Component-as-a-Transformation.aspx
http://www.codeproject.com/Articles/193855/An-indespensible-SSIS-transformation-component-Scr
#2
1
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):
包(注意我重定向数据源中可疑列的行):
The conditional split logic: [EDIT: I found that every case condition requires a separate processing path. If you are evaluating multiple expressions, you can do so in one case by appending them with the || operator.] 
条件分割逻辑:[编辑:我发现每个案例条件都需要一个单独的处理路径。如果要评估多个表达式,可以在一种情况下通过附加||来表示它们运营商。]
The derived column logic: 
派生列逻辑:
#1
2
SSIS expressions get hairy and hard to read when the logic is complex or if there are multiple evaluations. In your case you're going to wind up with a bunch of tasks that, individually, do very little.
当逻辑复杂或有多个评估时,SSIS表达式会变得毛茸茸且难以阅读。在你的情况下,你最终将完成一系列任务,这些任务分别做得很少。
I'd bundle this up into a script component. That way you could use basic vb or c# functions to evaulate if all of your columns properly convert to numeric and assign defaults when they don't. Additionally, you can implement a try/catch scenario and gracefully send errors to a different output buffer.
我将它捆绑到一个脚本组件中。这样你就可以使用基本的vb或c#函数来评估你的所有列是否正确转换为数字并在没有时指定默认值。此外,您可以实现try / catch方案并优雅地将错误发送到不同的输出缓冲区。
Here's some examples of how to use the script component as a transformation:
以下是如何使用脚本组件作为转换的一些示例:
http://www.bimonkey.com/2009/09/the-script-transformation-part-1-a-simple-transformation/
http://www.sqlis.com/sqlis/post/The-Script-Component-as-a-Transformation.aspx
http://www.codeproject.com/Articles/193855/An-indespensible-SSIS-transformation-component-Scr
#2
1
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):
包(注意我重定向数据源中可疑列的行):
The conditional split logic: [EDIT: I found that every case condition requires a separate processing path. If you are evaluating multiple expressions, you can do so in one case by appending them with the || operator.]
条件分割逻辑:[编辑:我发现每个案例条件都需要一个单独的处理路径。如果要评估多个表达式,可以在一种情况下通过附加||来表示它们运营商。]
The derived column logic:
派生列逻辑: