SQL Server Performance Forum – Threads Archive

Script component

I faced the rror msg when creating script component with scripting as below:
The related error msg: ‘Output0Buffer’ is not a member of ‘ScriptComponent_78a69…..ScriptMain’ I had check the refernces as below:
Microsoft.sqlServer.DTSPipelineWrap
Microsoft.sqlServer.DTSRuntimeWrap
Microsoft.sqlServer.pipelineHost
Microsoft.sqlServer.TXScript
Microsoft.VSA
System
System.Data
System.Windows.Forms
System.XML May I know for Output0Buffer , which file it is refering to? Am I miss out adding any other file? Scripting at script componnent:
================================= Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
‘
‘ Add your code here
‘All pivoted values come from the Row object which contains the incoming row With Output0Buffer
.AddRow()
.ProductAlternateKey = Row.ProductAlternateKey
.AmountType = "Sales"
.AmountValue = Row.SalesAmount .AddRow()
.ProductAlternateKey = Row.ProductAlternateKey
.AmountType = "UnitPrice"
.AmountValue = Row.UnitPrice .AddRow()
.ProductAlternateKey = Row.ProductAlternateKey
.AmountType = "Freight"
.AmountValue = Row.Freight
End With
End Sub

This is the another part of the scripting at the top side that i miss out to show out:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Collections Public Class ScriptMain
Inherits UserComponent

What type is Output0Buffer , is there any object name created with Output0Buffer. What is the this script component (Source/ Destination / Transformation.

I had set the property for the output of synchronousInputId to none, and the whole script component runing fine and no error ecounter.
Thanks . But I face another issue where if the source column for ProductAlternateKey using data type ‘text’ then the output for ProductAlternateKey will be problem.
When run the script then the error msg show as below: Conversion from type ‘BlobColumn’ to type ‘String’ is not valid. The data viewer of data flow from the source of ProductAlternateKey show <Long text> value. If I change the sources column for ProductAlternateKey data type to become varchar or nvarchar, then no such issue. But sources column for ProductAlternateKey sure will have value leght more then 8000.
so the varchar and nvarchar data type sure not able to support more then 8000. Any suggestion?

See this bloghttp://barrettbi.spaces.live.com/blog/cns!73FA7CB7EEA2354F!107.entry is any help, also what is the service pack level on server in this case. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

The services pack 2 is installed at the server.
I had tried many time to try to open the website from the link you had provided, but it was fail to open it.

For clearer understand the problem, please refer to the image below: The related 2 columns show at the imgae are the text data type. This is the data view before the script component and after the script component. <img src=’http://i115.photobucket.com/albums/n310/tboonleong2006/1.jpg’ border=’0′ /> The preview result from the destination data sources after the ETL process completed. <img src=’http://i115.photobucket.com/albums/n310/tboonleong2006/2.jpg’ border=’0′ /> Note: If the related 2 columns show at the imgae are the varchar and nvarchar data type, then no such problem encounter.But varchar only support 8000 and nvarchar data type only support 4000, thus text data type able to support bigger size. Anyonce know how to solve this issue?Or any alternative solution? Please let me know. Thanks.

For clearer understand the problem, please refer to the image below: The related 2 columns show at the imgae are the text data type.
This is the data view before the script component and after the script component.
http://s115.photobucket.com/albums/n310/tboonleong2006/?action=view&current=1.jpg
The preview result from the destination data sources after the ETL process completed.
http://s115.photobucket.com/albums/n310/tboonleong2006/?action=view&current=2.jpg Note: If the related 2 columns show at the imgae are the varchar and nvarchar data type, then no such problem encounter.But varchar only support 8000 and nvarchar data type only support 4000, thus text data type able to support bigger size. Anyonce know how to solve this issue?Or any alternative solution?
Please let me know.
Thanks.

Have you defined your output buffers correctly? For information you create output buffers by,
doouble click script components
Select InputANDOutput and Add Output. You can verify this by go to the script
select buffer rapper
all your buffers should be there
—————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=84488&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=84488&SiteID=1 The text and ntext data type at the sources column need special scripting at the script component. It invovle addBlobdata and GetBloddata scripting. Anyone know more about this scripting? Since i had tested the script from the link, it return null value only without any error msg….[xx(]

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)) Row.CoverNote = Row.CoverNote.AddBlobData(buffer)
End With
Next
End Sub
End Class The above scripting which highlighted wtih red color was encounter error.
The error msg : Exppression doed not produce value.
Note: sources column for CoverNote is text data type which contain a lot multivalue.
eg: 001^002^003^004^005
Anyonce know how to write the correct scripting for handle such column with text data type.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=84488&SiteID=1

Above link to another forum, which have the solution for above issue. Thanks everyone for the helping and concern. Thanks again.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

]]>