How do you tell the script task NOT to write a particular row?
This code currently writes 0 and blank when the type <> 4, i'm looking NOT to write the row at all.
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
Private iType, iRest As String
Private rawAmount As Double
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
iType = Row.Type
iRest = Row.rest
If iType = "4" Then
Row.ani = iRest.Substring(112, 10)
rawAmount = CInt(iRest.Substring(41, 4))
If rawAmount <> 0 Then
Row.amount = rawAmount / 100
End If
End If
End Sub
End Class
Also, I'm writting these columns to a destination excel, and event hough the spreadsheet cells are formated for an nn.nn numeric, every cell has an error that my data is text and I get that green wedge asking me to convert it. If I manually enter what I'm sending(example 45.5, it takes it just fine and turns it into 45.50 numeric). What can I do about this? anything additional I can send excel to tell it treat numerics like numerics.. maybe something like: mso-number-format or something.
Thanks for any help or information.
To have a script task not write a particular row, create two outputs, and direct desired rows to one output, undesired rows to the other.
By default, for a given script component, a single output is created. So, create a second output, named "Output 1" by default. Ensure the SynchrousInputID property of the new output is equal to that of the original. Set the ExclusionGroup on both outputs to 1, meaning they are filtered outputs.
In the component, direct rows as needed, connecting the desired output to downstream inputs. Unconnected ouputs are effectvely discarded (technically they are accessible on the disregarded output). Some will naturally reply, you can do similar operations with a conditional split, and that's true enough.
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)
If True Then
Row.DirectRowToOutput0()
Else
Row.DirectRowToOutput1()
End If
End Sub
End Class
|||
Having problems with this. I have a script component in a data flow.
when I add the additional output, the synchronousInput id defaults to 0, and I can't change it to 1133 which is what Output 0 has.
In the code, when I attempt to add code like this
Row.DirectRowToOutput1()Row.DirectRowToOutput1()
property DirectRow* is not available. I must be missing something.
Also, while on this, how does one insert additional rows?
|||
You can't insert new rows into a synchronous output, only into an asynchronous one.
If the script component is set to be a transform (you're prompted for this when you first add the script component), then when you add the new output, the SynchronousInputID should be a dropdown that allows you to select the Input.
|||
re "property DirectRow* is not available. I must be missing something."
I had this problem too - resolved it by setting Exclusion Group from 0 to 1 on both outputs
