derived column transform (flat file blanks to 0)

Hi,

Is it possible using derived column transform to change all blank values in a flat file to say a "0"

Basically convert "" to "0"

Thanks for any help,

Slash.

Hi,

I think you can generate your derived column like this :

([source_column] == "") ? "0" : [source_column]

Arno.

|||

Top man,

Worked a treat,

Thanks again,

Slash.

derived column - lots of columns-automate?

I'm sending a lot of columns through my derived column transform, checking for empty strings from a flat file - I was wondering is there a way that I could "script out" all the transforms instead of enduring this click hell that I'm stuck in inside the derived column transformation editor. I've got probably 100+ columns to configure with the following sort of transform....

Replace Col1

TRIM(Col1) == "" ? NULL(DT_WSTR,2) : Col1

Basically - if the string is empty, then throw Null in the data stream. I'm about a third the way through but it would really be nice if there was a quicker way. Even with the most efficient copying & pasting & keyboard shortcuts, it's still painful.

Using a script component, you can loop through the columns and perform this operation. Here's a few samples:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=864401&SiteID=1

http://agilebi.com/cs/blogs/jwelch/archive/2007/06/01/xml-destination-script-component.aspx

The top one requires a little more coding, but is more flexible. The other uses reflection, which is slower but requires less code. Or you could use something like this, a slimmed down version of the first one that only handles strings.

Code Snippet

'Note: this code was originally written/posted by the SSIS forum user, jaegd. http://forums.microsoft.com/MSDN/User/Profile.aspx?UserID=133544&SiteID=1

'Credit has been given where credit is due

'Original post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=864401&SiteID=1

'Trimmed to bare minimum code by jwelch

Imports Microsoft.SqlServer.Dts.Pipeline

Public Class ScriptMain

Inherits UserComponent

Private inputBuffer As PipelineBuffer

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim counter As Integer = 0

For counter = 0 To inputBuffer.ColumnCount - 1

If (inputBuffer.GetString(counter)) = "." Then

inputBuffer.SetString(counter, Chr(0))

End If

Next

End Sub

Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)

' Get the Pipeline Buffer for subsequent ordinal column access

inputBuffer = Buffer

MyBase.ProcessInput(InputID, Buffer)

End Sub

End Class

sql