Monday, April 27, 2009

Limpieza de datos (Data Cleansing) con Script en SSIS

Hola chicos.
Muchas veces he necesitado hacer frente a información realmente sucia, poblada con cadenas en blanco, valores nulos y fechas futuras. La mayoría de las veces he tenido que limpiar estos valores en varios flujos de datos y varios paquetes. Podemos hacerlo fácilmente incluyendo un componente 'Column Transformation', pero tendríamos que declarar la función de transformación para cada columna que queramos limpiar.
Un enfoque diferente sería utilizar el componente de script para cubrir cada columna que deseáramos limpiar de forma automática, a través de la reflexión de .NET.
En el componente de script tendremos que seleccionar las columnas de entrada que hay que limpiar. A continuación declaramos un bucle que iterará para cada columna y realizará un llamada a la función de limpiar valor, dependiendo del tipo del valor de la columna.
En el ejemplo de código siguiente, se limpia la cadena de columnas con valores en blanco, y se reemplazan las fechas futuras con un null:


Imports System
Imports System.Data
Imports System.Math
Imports System.Reflection
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim str As String
Dim obj As Object
Try
Dim p As PropertyInfo
For Each p In Row.GetType().GetProperties()
'First check, null properties
If p Is Nothing Then
Continue For
End If
'Second check, null values
If p.GetValue(Row, Nothing) Is Nothing Then
Continue For
End If
'Second check by types
If p.PropertyType Is GetType(String) Then
p.SetValue(Row, CleanString(p.GetValue(Row, Nothing).ToString()), Nothing)
End If
If p.PropertyType Is GetType(DateTime) Then
If CType(Row.GetType().GetProperty(p.Name() + "_IsNull").GetValue(Row, Nothing), Boolean) = True Then
Continue For
End If
obj = CleanDate(p.GetValue(Row, Nothing))
If obj Is Nothing Then
Row.GetType().GetProperty(p.Name() + "_IsNull").SetValue(Row, True, Nothing)
Else
p.SetValue(Row, obj, Nothing)
End If
End If
Next
Catch e As Exception
'Do Nothing
End Try
End Sub

Public Function CleanString(ByVal str As String) As String
If str.Trim() = "" Then
Return Nothing
End If
Return str
End Function

Public Function CleanDate(ByVal obj As Object) As Object
Dim dt As DateTime
dt = CType(obj, DateTime)
If dt > DateTime.Now Then
Return Nothing
End If
Return obj
End Function

End Class