Avatar of lostboyinsofla
Flag for United States of America asked on

SSIS script component issue

I have a script that I'm passing about 60000 records to.  One of the fields is a dt_text field.  When I'm handing that field off to the script I'm converting it to unicode as you can see in the script below.

My problem is after around 50000 records are passed through the pipeline I get this error:

   at ScriptComponent_f389ae70f7634d74ab65be5acee6a369.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
   at ScriptComponent_f389ae70f7634d74ab65be5acee6a369.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
   at ScriptComponent_f389ae70f7634d74ab65be5acee6a369.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

I am not very familar with scripts but it seems to be working and from what I can guess the problem is a buffer  issue.

I've played with the DefaultBufferMaxRows and DefaultBufferSize options but can't seem to get past this issue.

Any advise is much appreciated.  I'm pasting my script below for reference.

' 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
Imports System.Text
Public Class ScriptMain
    Inherits UserComponent
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        'Dim blobData() As Byte = Row.FolioLegalDescription.GetBlobData(0, CInt(Row.FolioLegalDescription.Length))
        Dim inLegal As String = System.Text.Encoding.Unicode.GetString(Row.FolioLegalDescription.GetBlobData(0, CInt(Row.FolioLegalDescription.Length)))
        Dim inLegal2, inLegal3, inLegal4, inLegal5, inLegal6 As String
        inLegal = inLegal.Substring(1, inLegal.Length - 2)
        inLegal = Replace(inLegal, Chr(10), "")
        inLegal = Replace(inLegal, Chr(13), "")
        inLegal = Replace(inLegal, Chr(9), "")
        If inLegal.Length > 20000 Then
            inLegal6 = inLegal.Substring(20000, inLegal.Length - 4000)
            inLegal = inLegal.Substring(0, 20000)
        End If
        If inLegal.Length > 16000 Then
            inLegal5 = inLegal.Substring(16000, inLegal.Length - 4000)
            inLegal = inLegal.Substring(0, 16000)
        End If
        If inLegal.Length > 12000 Then
            inLegal4 = inLegal.Substring(12000, inLegal.Length - 4000)
            inLegal = inLegal.Substring(0, 12000)
        End If
        If inLegal.Length > 8000 Then
            inLegal3 = inLegal.Substring(8000, inLegal.Length - 4000)
            inLegal = inLegal.Substring(0, 8000)
        End If
        If inLegal.Length > 4000 Then
            inLegal2 = inLegal.Substring(4000, inLegal.Length - 4000)
            inLegal = inLegal.Substring(0, 4000)
        End If
        Row.Legal1 = inLegal
        Row.Legal2 = inLegal2
        Row.Legal3 = inLegal3
        Row.Legal4 = inLegal4
        Row.Legal5 = inLegal5
        Row.Legal6 = inLegal6
    End Sub
End Class

Open in new window

Microsoft SQL Server 2005VB ScriptVisual Basic.NET

Avatar of undefined
Last Comment

8/22/2022 - Mon

but why you use script?!
Why you dont use other components like conditionSplit or derived column?
Only some exception cases you should use script tasks.. other components are more optimized... depends on what you need, but in this case, I think  you could replace the script by other components!

I am not the original author of the package so I'm not entirely sure why a script was used.  That being said I think they probably found it easier to split the blob up.

How else can I do it?  I still need to be able to take a dt_text datatype which can be up to 24,000 chars and break it out into 6 4,000 char dt_str datatypes.

Do you know what is causing the actual error I posted though?  Is it the script or the buffers for the dataflow object?  I'm not so sure the script is causing it and I don't want to go re-writting things just because if that isn't going to solve the issue.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

try what frog suggest, and for each output add an OLE DB Destination and unlock table destination.
Avoid using Union All and Aggregates.

The problem with having multiple copies of the same destination, is that you can't use a fast load - a fast load has to have an exclusive lock on the table.

Even though Unions are best avoided where posssible, I think in this case they will improve the performance. Especiallly if there are a number of other transformations further along the pipeline that need to be applied to each output. Otherwise you would have to duplicate every transform 6 times... less than ideal for development and performance.

However, like most things with SQL/SSIS there is always more than one way to do things - the right way is very dependent on the specifics of each project.

i'm with you frog... and notjing more important to test the performance of several ways to do the job!:-)
In sql2008... the performance it's high improved!!
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.