Doing the same replace on several columns?

In the derived column task you can choose each column and write an expression for each column. But when you need to do a <ISNULL(status) ? "0" : statusdato> on 40-50 columns it get kind of irritating. Is there a way easy to do the sam expression on a selection of columns like a sort of derived column task, where you write an expression and assign that to a selection of columns (otherwise this would be a wish :-) )

Sorry, there is no such feature. Sounds like a good candidate to enter as a suggestion.

Note: it would be possible to write code that programmatically does what you are looking for, by loading a package, finding the derived column transform, getting the input column collection, and setting the expression properties.

Thanks
Mark

|||Ascential's (now IBM) DataStage had a really nice interfaces for doing just what you've asked. while SSIS doesn't have this interface, it does have the Script Component, which I find much more flexible than DataStage's Transformer stage.

I had a similar dilemma with having a good number of my input fields requiring trimming. Following the lead of several custom component examples I got rid of the ProcessInput_Row (I think that's what's in there) and overrode PreExecute and ProcessInput with the following in a Script Componet, of type Transform.

The biggest drawback is having to select all the checkboxes on the Input Columns tab and set them all to ReadWrite, but with some keyboard skills, that can go pretty quickly.

I've not been a programmer in any of my previous lives, so please forgive the poor form:

Public Class ScriptMain
Inherits UserComponent

Private _inputColumnInfos As ColumnInfo()

Public Structure ColumnInfo
Public bufferColumnIndex As Integer
Public lineageID As Integer
End Structure

Public Overrides Sub PreExecute()
Dim input As IDTSInput90 = ComponentMetaData.InputCollection(0)

ReDim _inputColumnInfos(input.InputColumnCollection.Count - 1)

For x As Integer = 0 To (input.InputColumnCollection.Count - 1)
Dim column As IDTSInputColumn90 = input.InputColumnCollection(x)
_inputColumnInfos(x) = New ColumnInfo()
_inputColumnInfos(x).bufferColumnIndex = input.InputColumnCollection.FindObjectIndexByID(column.ID)
_inputColumnInfos(x).lineageID = column.LineageID
Next
MyBase.PreExecute()
End Sub

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

While (Buffer.NextRow())

For x As Integer = 0 To (_inputColumnInfos.Length - 1)
columnInfo = _inputColumnInfos(x)

Dim trxVal As String = Buffer.GetString(columnInfo.bufferColumnIndex).ToString().Trim

Buffer.SetString(columnInfo.bufferColumnIndex, trxVal)
Next
End While
End Sub

End Class