Issue :SSIS Package data transformation in loop script component

Question

I have developed a package in SQL server 2008 R2. I want to convert the data using script block. But I got the error that was the first and last records is not inserted and it tries to insert null that violates integrity contraint. Below is my code in VB script.

    <microsoft.sqlserver.dts.pipeline.ssisscriptcomponententrypointattribute()> _
    <clscompliant(false)> _

    Public Class ScriptMain
    Inherits UserComponent
    Dim i As Integer = 0

    Public Overrides Sub PreExecute()
        MyBase.PreExecute()        
    End Sub

    Public Overrides Sub PostExecute()
        MyBase.PostExecute()        
    End Sub
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

            i = i + 1
            With Output0Buffer
                .OrderHeaderID = i
                .CustomerNumber = Row.CUSTNO
                .OrderNumber = Row.ORDER
                .TermsCode = Row.TRMSCD
                .TermsDescription = Row.TRMDSC
                .TotalLines = Row.TOTLNS
                .TotalDollars = Row.TOTDOL
                .ComCls = Row.COMCLS
                .DropShip = Row.DRPSHP
                .DropshipPurchaseOrder = Row.DSPONO
                .InvoiceNumber = Row.INVNO
                .LstSeq = Row.LSTSEQ
                .OrderType = Row.ORDTYP
                .PONumber = Row.PONUMB
                .ProgramCode = Row.PROGCD
                .ProgramDeal = Row.PROGDL
                .ProgramName = Row.PRGNAM
                .PRONumber = Row.PRONUM
                .ReceivedVia = Row.RCVVIA
                .StatusMessage = Row.STAMSG
                .TermsDescription = Row.TRMDES
                .TermsDiscount = Row.TRMDSC
                .TruckName = Row.TRKNAM

                If IsDate(Row.SHPDAT) Then
                    .ShipDate = Row.SHPDAT
                End If

                If IsDate(Row.ORDDAT) Then
                    .OrderDate = Row.ORDDAT
                End If

                If IsDate(Row.INVDAT) Then
                    .InvoiceDate = Row.INVDAT
                End If

                If IsDate(Row.DATING) Then
                    .Dating = Row.DATING
                End If

                If Not Row.INVDAT_IsNull Then
                    .AddRow()
                Else
                    Exit Sub
                End If
            End With

    End Sub

    Public Overrides Sub CreateNewOutputRows()

        Output0Buffer.AddRow()

    End Sub
End Class

Currently I am trying to insert 10 rows but it gives error. Please guide me.

Can you post the error you are getting from the script component? From looking at your code you handle the row output in Sub Input0_ProcessInputRow, so you probably don't need to override Sub CreateNewOutputRows() since you are not creating(or intending to create) any new rows. This might be the reason you are getting the integrity violation as well. — jymbo
– jymbo, Commented Aug 19, 2013 at 19:28

shree.pat18 · Accepted Answer · 2013-09-20 11:48:30Z

1

You initialise variable i = 0 and then increment it before processing for that row. That could cause the first row in your input to be skipped.

answered Sep 20, 2013 at 11:48

shree.pat18

21.8k3 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Issue :SSIS Package data transformation in loop script component

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related