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.
