Help on Script Component assignment of output variable

Hi all,

Actually I′m working with the beta 2 of the Sql Server 2005 with SSIS. And it′s great fun! But I′m now experience a problem:

I′m working with a script component. In that script component I would like to assign a value to a specific column ("Row.Formula"). The type of "Row.Formula" is Unicode text stream [DT_NTEXT]. Whenever I try to assign a byte array or a string to that field I am getting a compliation error. I have to assign a variable with the type "blobcolumn". But I cannot initialize this variable because "blobcolumn" has no public constructor.

Any thoughts on that?

The code sample:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Avanade.AMCS.DataProcessorReplacement.Utility

Public Class ScriptMain Inherits UserComponent

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim wholeRowBuffer As Byte()
Dim columns() As String

wholeRowBuffer = Row.WholeLine.GetBlobData(0, CInt(Row.WholeLine.Length))

columns = ScriptComponentHelper.RetrieveStringArrayOutOfBuffer(wholeRowBuffer)

Row.Name = columns(1)
Row.MenuName = columns(2)
Row.CascadeName = columns(3)

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

'Conversion compliation error - cannot convert from byte array to 'Microsoft.SqlServer.Dts.Pipeline.BlobColumn'

Row.Formula = buffer

End Sub

End Class

To access BLOB data in Script component, please use AddBlobData and GetBlobData.

For example:

Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))

Row.Formula.AddBlobData(buffer)

|||

---

Imports System
Imports System.Xml
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)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
.CoverNote = Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

The above scripting which highlightted wtih red color was encounter error.
The error msg : Expression doed not produce value.
Note: sources column for CoverNote is text data type which contain a lot multivalue.
example 001^002^003^004^005

Anyonce know how to write the correct scripting for handle such column with text data type.I dont have any idea for this case with text data type.

Thanks in advance.

|||

The line should just read

Row.CoverNote.AddBlobData(...

So remove the assignment part of the highlighted statement; AddBlobData() returns nothing.

|||Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim i As Integer
Dim CoverNote As String()

CoverNote = Split(Row.CoverNote.ToString, "^")

For i = 0 To UBound(CoverNote)
With Output0Buffer
.AddRow()
.Key = Row.Key
.AgentCode = Row.AgentCode
.TransactionDate = Row.TransactionDate
.Branchcode = Row.BranchCode
.BatchNo = Row.BatchNo
.NoOfCoverNotes = Row.NoOfCoverNotes
.TotalGrossPremium = Row.TotalGrossPremium
.MedicalGrsPrem = Row.MedicalGrsPrem
Dim buffer As Byte()
buffer = System.Text.Encoding.Unicode.GetBytes(CoverNote(i))
Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class

Thanks jaegd.

I had try to modify the code as per suggested.

Other columns output were fine and ok, just CoverNote result output was NULL value.

Any other script I miss out and need to add in or modified to able support this CoverNOte with text data type?

Appreciate for any help.

|||

What is the pipeline data type of the input column Row.CoverNote -- DT_TEXT,DT_NEXT, or something else?

|||The pipeline data type of the input column Row.CoverNote is DT_TEXT|||

Since the source pipeline type is DT_TEXT , before you can meaningfully called Split() on the character data contained in the blob, convert the pipeline data type to a .NET String first. Retrieval of DT_TEXT data is performed by calling GetBlobData() on the named/typed accessor and then decoding the array of bytes returned.

In other words, replace the line

CoverNote = Split(Row.CoverNote.ToString, "^")

with

Dim blobLength As Int32 = Convert.ToInt32(Row.CoverNote.Length)

Dim blobData() As Byte = Row.CoverNote.GetBlobData(0, blobLength)

Dim blobCodePage As Int32 = Row.CoverNote.ColumnInfo.CodePage

Dim joinedCoverNote As String = Text.Encoding.GetEncoding(blobCodePage).GetString(blobData)

CoverNote = Split(joinedCoverNote, "^")

|||

I had tried the sripting as per suggested.

The problems had been solved.

Thanks for your helping.

sql