SSIS Scripts

I have been working with SSIS for a couple weeks, and while I understand a few of the common tools I am really a novice in many areas.

Today I needed to prevent the Available Quantity on inventory from going negative since we don't want to expose that to users of the site I am working on. There are no build in commands to do that in the "Derived Column" object, so I had to use a script object.

For some reason VB is the only language supported, but it was really easy. After identifying which columns were available and whether they were read-only or not, the only code I had to write was:Row.AvailableQty = Max(Row.AvailableQty, 0).

The entire script follows, all but one line was generated by the system.

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
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)
'
' Add your code here
'
Row.AvailableQty = Max(Row.AvailableQty, 0)

End Sub

End Class