SQL Server Performance

Script component

Discussion in 'SQL Server 2005 Integration Services' started by tboonleong, Jun 5, 2007.

  1. tboonleong Member

    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
  2. tboonleong Member

    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
  3. gurucb New Member

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

    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?
  5. satya Moderator

  6. tboonleong Member

    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.
  7. tboonleong Member

    For clearer understand the problem, please refer to the image below:<br /><br />The related 2 columns show at the imgae are the text data type.<br />This is the data view before the script component and after the script component.<br /><br /><img src='http://i115.photobucket.com/albums/n310/tboonleong2006/1.jpg' border='0' /><br /><br /><br />The preview result from the destination data sources after the ETL process completed.<br /><img src='http://i115.photobucket.com/albums/n310/tboonleong2006/2.jpg' border='0' /><br /><br />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.<br /><br />Anyonce know how to solve this issue?Or any alternative solution?<br />Please let me know.<br />Thanks.<br />
  8. tboonleong Member

    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.



  9. dineshasanka Moderator

    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/
  10. tboonleong Member

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

    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(]
  12. tboonleong Member

    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.
  13. tboonleong Member

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

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

Share This Page