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.UtilityPublic Class ScriptMainInherits UserComponent PublicOverridesSub Input_ProcessInputRow(ByVal RowAs InputBuffer)
Dim wholeRowBufferAsByte()
Dim columns()AsString 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 bufferAsByte()
buffer = System.Text.Encoding.Unicode.GetBytes(columns(4))'Conversion compliationerror - cannot convert frombyte array to 'Microsoft.SqlServer.Dts.Pipeline.BlobColumn' Row.Formula = buffer EndSubEnd 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)
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.
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,
"^") 
