none
script component input buffer issues RRS feed

  • Question

  • Hello,

    I have a script component that creates a '|' delimited file. the input for this script component is from an oledb source, the problem im facing now is not all rows from the oledb source that is being fed to the script component buffer is being processed by the script component, it randomly picks up two rows from the buffer and process it as the text files(i sometimes have 5-10 rows in the buffer but only two gets picked up). I have tried using the different function Input0_processInput()/input0_processInputRows()/processInput but much to my disappointment im not able to control the data in the buffer.

    any ideas or workarounds on how i can better control the data in the buffer.

    would appreciate your replies.


    • Edited by r_ean Sunday, December 16, 2012 8:33 AM
    Sunday, December 16, 2012 8:32 AM

Answers

  • Hi Eileen,

    Thanks for the reply.

    i was able to resolve this issue for the time being by putting the scrip component to sleep for 1 ms (Thread.Sleep(1000))

    With the sleep in place all rows are getting written in order they appear to files. I'm guessing the issue could also be with the way i'm concatenating all the columns in the buffer ( this line in particular ...StrMessage = StrMessage & Chr(28) & Chr(13)).

    although this is a temporary fix as i'm still trying different methods to create  the files real time.

    i'm using sql server 2005 hence i'm not able to debug the same. wish i could do that to actually pin point the issue for certain.


    • Marked as answer by Eileen Zhao Thursday, January 3, 2013 8:21 AM
    Thursday, December 27, 2012 1:32 PM

All replies

  • what are you trying to accomplish? If you want to randomly pick some records you could also use the row sample transformation: http://msdn.microsoft.com/en-us/library/ms141200.aspx


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Sunday, December 16, 2012 9:27 AM
    Moderator
  • thanks for replying

    below is my scenario:

    i am pulling data from an sql view, the data set of the view is the input to my script component.

    the scrip component on its part loops through the data set and creates a delimited file for every row in the data set, i need the script component to loop through the all the  rows in the data set and create individual files for every row data but that is not happening as of now, say the view from oledb source produces 10 rows i should have 10 delimited files from the scrip component but for some reason the script component produces only two files for two randomly picked rows from the source. im NOT setting any conditions for the script component to do this, be it any number of rows from the oledb source no matter what it just generates only 2 files.I somehow cannot control what row is being picked to generate the file as the output.

    im using sql server 2005 hence the code is in VB

    i have read so many posts on this but not able to figure out a way around this below is snap shot of my code.

    Public Overrides Sub PreExecute()
            MyBase.PreExecute()
            '
            ' Add your code here for preprocessing or remove if not needed
            '
        End Sub
    
    Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As PipelineBuffer)
            Try
    
                StrMessage = Nothing
    
                
                While (Buffer.NextRow())
                    '**************************TEST To LOG********************
                    now = DateTime.Now
                    dateFileName = now.ToString("s")
                     newFileName = "PPVIS_Log" & dateFileName & "_" & Buffer(52).ToString()
                
                	destFilePath = "c:\HL7$"
                    destFileName = Path.Combine(destFilePath, newFileName)
                    writeFile = New FileStream(destFileName & ".txt", FileMode.Create, FileAccess.Write)
                    sw = New StreamWriter(writeFile)
    
    
    
                   
                    For indx = 2 To Buffer.ColumnCount - 1
                     
                      	'concats strings to  StrMessage 
                      	
                    Next
                    
                    StrMessage = StrMessage & Chr(28) & Chr(13)
                    sw.Write(StrMessage)
                    sw.Close()
                End While '(Buffer.NextRow())
               
    
            Catch ex As Exception
                
                System.Windows.Forms.MessageBox.Show("Error..... " & ex.ToString())
               
            Finally
    
                writeFile.Close()
    
            End Try
    
            Buffer.Dispose()
    
        End Sub
        
        Public Overrides Sub PostExecute()
                MyBase.PostExecute()
                
                StrMessage = Nothing
                delimiter = Nothing
                indx = Nothing
                newFileName = Nothing
                destFilePath = Nothing
                destFileName = Nothing
                now = Nothing
                dateFileName = Nothing
        End Sub





    Sunday, December 16, 2012 5:17 PM
  • If you want each row to go to a separate text file then I would the Input0_ProcessInputRow method. Something like this (not tested):

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    	now = DateTime.Now
    	dateFileName = now.ToString("s")
    	newFileName = "PPVIS_Log" & dateFileName & "_" & Row.Colxxx.ToString()
    	destFilePath = "c:\HL7$"
    	destFileName = Path.Combine(destFilePath, newFileName)
    	writeFile = New FileStream(destFileName & ".txt", FileMode.Create, FileAccess.Write)
    	sw = New StreamWriter(writeFile)
    	
    	' loop through columns with REFLEXION
    	' see example at http://microsoft-ssis.blogspot.com/2010/12/flexible-xml-destination.html
    	
    	' or manually add all columns
    	' StrMessage = Row.Col1 + '|' + Row.Col2 + etcetera
    	
    	sw.Write(StrMessage)
    	sw.Close()
    	writeFile.Close()
    End Sub
    Reflexion example

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    Sunday, December 16, 2012 7:33 PM
    Moderator
  • Hello again,

    Thanks for the quick responses to my questions, my main problem here is not being able to control the input buffer , was curious to know why the script component randomly processes  rows from the input buffer instead of looping consecutively through all the rows in the input buffer.

    i have used the Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) function but had the same problem with random rows being selected with that as well.

    any thoughts on that.

    Monday, December 17, 2012 4:28 PM
  • I would use a script component transformation which will be executed once for each row, dump the row in a file  and you got what you want, one file for each row.


    Monday, December 17, 2012 11:19 PM
  • script component transformation is exactly what i am using now...

    Tuesday, December 18, 2012 3:17 PM
  • Hi r_ean,

    We can think of the Data Flow as a pipeline for data, Data Flow is appropriately named because the data equates to the water in the plumbing analogy. You use a transformation component in the data flow of an Integration Services package to modify and analyze data as it passes from source to destination. A transformation with synchronous outputs processes each input row as it passes through the component. A transformation with asynchronous outputs waits until it has received all input rows to complete its processing. I suggest you can refer to the following article:

    Creating an Asynchronous Transformation with the Script Component: http://msdn.microsoft.com/en-us/library/ms136133.aspx 


    If you want the rows to be sorted in order, you can use Sort Transformation befrore using script component transformation, please see: http://msdn.microsoft.com/en-us/library/ms140182.aspx 

    Thanks,
    Eileen

    If you have any feedback on our support, please click here


    Eileen Zhao
    TechNet Community Support

    Thursday, December 27, 2012 6:44 AM
  • Hi Eileen,

    Thanks for the reply.

    i was able to resolve this issue for the time being by putting the scrip component to sleep for 1 ms (Thread.Sleep(1000))

    With the sleep in place all rows are getting written in order they appear to files. I'm guessing the issue could also be with the way i'm concatenating all the columns in the buffer ( this line in particular ...StrMessage = StrMessage & Chr(28) & Chr(13)).

    although this is a temporary fix as i'm still trying different methods to create  the files real time.

    i'm using sql server 2005 hence i'm not able to debug the same. wish i could do that to actually pin point the issue for certain.


    • Marked as answer by Eileen Zhao Thursday, January 3, 2013 8:21 AM
    Thursday, December 27, 2012 1:32 PM