Package hangs at Script component

Question

I am trying to use a script component to import a flat file into sql server. The file is has fixed length fields but the record length is variable (some of the fields are not in the record just refnum and cr character. With the script I just want to import refnum and code from a record that has some 600 fields. Not all records have code

Between Refnum and Code there is a string of 587 characters. The package hangs during the script


SSIS package "Package1.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

' 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.IO

Public

 

Class ScriptMain

 

Inherits UserComponent

 

Private Sub CompleteRec(ByVal LineValue As String)

OutputTextBuffer.AddRow()

OutputTextBuffer.RefNum = Trim(LineValue.Substring(1, 24))

OutputTextBuffer.Junk = Trim(LineValue.Substring(25, 687))

OutputTextBuffer.code= Trim(LineValue.Substring(688, 697))

 

End Sub

 

Private Sub IncompleteRec(ByVal LineValue As String)

OutputTextBuffer.AddRow()

OutputTextBuffer.RefNum = Trim(LineValue.Substring(1, 24))

OutputTextBuffer.Junk = Trim(LineValue.Substring(25, 687))

 

End Sub

 

Public Overrides Sub CreateNewOutputRows()

 

'

 

' Add rows by calling AddRow method on member variable called "<Output Name>Buffer"

 

' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"

 

'

 

Dim sr As StreamReader

 

Dim type As String

 

Dim line As String

 

Dim taskresult As String

sr =

New StreamReader(Me.Connections.BOABidfileConnection.ConnectionString, False)

 

Try

line = sr.ReadLine

 

While line.Length <> 0 type = Trim(line.Substring(0, 1))

 

Select Case type

 

Case "A"

IncompleteRec(line)

line = line.Remove(0, 687)

 

 

Case "B"

CompleteRec(line)

line = line.Remove(0, 697)

 

End Select

 

End While

 

Catch ex As Exception

 

End Try

 

 

 

 

End Sub

 

End

 

Class

Answer 1

Try putting a breakpoint in the script component and debug to see if the full script executes for one set of rows. There might be some threading/locking issue which ties your package in a loop and never gets out of it.

Cheers!!
M.

Answer 2

Try putting a breakpoint in the script component and debug to see if the full script executes for one set of rows. There might be some threading/locking issue which ties your package in a loop and never gets out of it.

Cheers!!
M.

"Iqbal" we set breakpoint in script component but can't debug it.

return to the main problem, whatever you are trying in script you can achieve by using "Flat file source" and derived column.

1) Select  "ragged right / fixed width" file format, take all data in one column .

2) use conditional split and check the length of column-----------   Trim[Column] != ""

3) use derived column and add three expression

              Trim(Substring(column,2, 24))                               ---------- give a name as "RefNum"

               Trim(Substring(column, 689, 9))                         ---------- give name as "code"

Trim(Substring(column,1,1) --------- name as Type

4) Now you have all the data in separate column and continue with your other validation.

Thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 3

HI
Please replace ur try catch block with the below given one

Try
            line = sr.ReadLine
            While line.Length <> 0
                i = i + 1
                type = Trim(line.Substring(0, 1))
                Select Case type
                    Case "A"
                        IncompleteRec(line)
                        ''line = line.Remove(0, 687)
                    Case "B"
                        CompleteRec(line)
                        ''line = line.Remove(0, 697)
                End Select
                MsgBox("lines scanned :" & i)
                line = sr.ReadLine
            End While
        Catch ex As Exception
        End Try

Hope That helps
basically u were not iterating through lines in ur flat file ...
Note Use Message Boxes to check what actually happens in ur code ..
** just a suggestion while posting code use "insert code block " option that helps reading and identifying the problem easily and fast ...
____________________________________________________
Please mark post/s helpful / answered if they answer ur question

Answer 4

I agree with what is suggested by kunal. use message boxes to check what flows in to variable/objects and what flows out.

But DO NOT forget to remove the message boxes before deploying/delivering the code as it would be a problem when you run it in the final production environment.

---

Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

Answer 5

I changed the code and it still seems like the script component never executes.
If I push the run button in the vb window nothing happens, and break it does not see the breakpoints

Right now i just have script component
                                        |
                                  ole db destination

But the script component does not seem to run

Answer 6

As suggested above, you need to add some messages to your code so you can see what it is doing. You can't add breakpoints to a script component (they only work in script tasks).

Instead of message boxes, I'd suggest using the built-in event functionality in SSIS. You don't have to worry about removing the code before deployment, and the messages will show up in the Progress window in BIDS.

bool bFireAgain = false;
this.ComponentMetaData.FireInformation(0, "MyScript", "Describe what's running", "", 0, ref bFireAgain);

---

John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com

Answer 7

I changed the code and it still seems like the script component never executes.
If I push the run button in the vb window nothing happens, and break it does not see the breakpoints

Right now i just have script component
                                        |
                                  ole db destination

But the script component does not seem to run

What do u mean by Script component never executes .....
just replace the try catch block given in ur script code ...
save and run the package [press f5 or green run button in BIDS ]  not the VB editor window ...
fine just put a message box at the entry point of the script component CreateNewOutputRows
to see if at all ur script component is working or not ....
also please tell us what do u mean by script comp never executes .....