Hi Guys, I am trying to validate the sequence of my flat file per Transaction Number by TypeCode column. Table below is the correct sequence. Trans# | Code | Name | TypeCode 12345 | 46729 | VM RYC FF | M 12345 | 23931 | VM COKE | MC 12345 | 83178 | VM FRIES | MC 12345 | 43421 | JEDI | MT 12345 | 23251 | SOUP | MAO 12345 | 1 | RICE | S 12345 | 42891 | VM CHCKN | M 12345 | 23144 | VM COKE | MC M is the start of the meal grouping and ends in either MC, MT or MAO depending on the data. Here are the rules that i need to accomplish. M should only have MC, MT, and MAO per meal grouping MC, MT, or MAO should always be preceded by an M per meal grouping. MC can be followed either by MT or MAO per meal grouping. How can i do that using a script component. Thanks,

If I understood correctly you need the records in some specific order based upon values in TypeCode column. You can assign some integer value for each value and then do a sort on the integer value column. Use a derived column to ceate a new column (Order) with expression as:TypeCode == "M" ? 1 : (TypeCode == "MC" ? 2 : 3) Then use a sort component with Order as input column with Sort type as ascending. If you want to filter any row with type code not starting with M then use conditional split after the source component.Nitesh Rai- Please mark the post as answered if it answers your question

Nitesh, I think the OP means that the sequence of records needs to be validated. In other words, if a record has an M for TypeCode, the following records should have MC, MT or MAO as typecode. If they have another typecode, those records are invalid. Luckily, I already answered the OP's question, as he already posted a thread with the same question: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/8494eb5d-f186-4fe2-badf-787719473e73/#4cb9db54-42f4-4119-8f5f-def765cd5dc8 @W00kies: follow my advice from the link above. If you have problems with anything, let us know.MCTS, MCITP - Please mark posts as answered where appropriate.

Hi Koen, Sorry for the double entry. Anyway, I am just a beginner in using script component and i don't know how or where to start. Can you gave me a script that i needed? Thanks,

Sorry, I don't have the time to write out such a script myself. I can however point you in the right direction: http://msdn.microsoft.com/en-us/library/ms136133.aspx On the net there should be plenty of examples on how to create (asynchronous) scriopt components.MCTS, MCITP - Please mark posts as answered where appropriate.

Hi, I have created this script and i know something is missing. Public Class ScriptMain Inherits UserComponent Private PreviousTypeCode As String = String.Empty Private CurrentTypeCode As String = String.Empty Private dt As DataTable Public Overrides Sub PreExecute() MyBase.PreExecute() dt = New DataTable dt.Columns.Add("TransactionNumber") dt.Columns.Add("RowType") dt.Columns.Add("RowNumber") dt.Columns.Add("ItemName") dt.Columns.Add("ProductCode") dt.Columns.Add("ProductTypeCode") End Sub Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) dt.Rows.Add(Row.TransactionNumber, Row.RowType, Row.RowNumberPre, Row.ItemName, Row.ProductCode, Row.ProductTypeCode) End Sub Public Overrides Sub Input0_ProcessInput(ByVal Buffer As Input0Buffer) While Buffer.NextRow() If Buffer.RowType = "ITEM" Then FetchOutputItemDate() End If Input0_ProcessInputRow(Buffer) PreviousTypeCode = CurrentTypeCode End While If Buffer.EndOfRowset() Then FetchOutputItemDate() Output0Buffer.SetEndOfRowset() End If End Sub Public Sub FetchOutputItemDate() For Each dr As DataRow In dt.Select("(RowType='ITEM')") CurrentTypeCode = dr("ProductTypeCode").ToString.Trim() Next For Each dr As DataRow In dt.Rows If CurrentTypeCode = "MC" AndAlso PreviousTypeCode = "M" Then With Output0Buffer .AddRow() .TransactionNumber = dr("TransactionNumber").ToString() .RowType = dr("RowType").ToString() .RowNumber = CInt(dr("RowNumber")) .ItemName = dr("ItemName").ToString() .ProductCode = dr("ProductCode").ToString() .ProductTypeCode = dr("ProductTypeCode").ToString() .Tag = 0 End With ElseIf CurrentTypeCode = "MC" AndAlso PreviousTypeCode = "MC" Then With Output0Buffer .AddRow() .TransactionNumber = dr("TransactionNumber").ToString() .RowType = dr("RowType").ToString() .RowNumber = CInt(dr("RowNumber")) .ItemName = dr("ItemName").ToString() .ProductCode = dr("ProductCode").ToString() .ProductTypeCode = dr("ProductTypeCode").ToString() .Tag = 0 End With ElseIf CurrentTypeCode = "MT" AndAlso PreviousTypeCode = "M" Then With Output0Buffer .AddRow() .TransactionNumber = dr("TransactionNumber").ToString() .RowType = dr("RowType").ToString() .RowNumber = CInt(dr("RowNumber")) .ItemName = dr("ItemName").ToString() .ProductCode = dr("ProductCode").ToString() .ProductTypeCode = dr("ProductTypeCode").ToString() .Tag = 0 End With ElseIf CurrentTypeCode = "MAO" AndAlso PreviousTypeCode = "M" Then With Output0Buffer .AddRow() .TransactionNumber = dr("TransactionNumber").ToString() .RowType = dr("RowType").ToString() .RowNumber = CInt(dr("RowNumber")) .ItemName = dr("ItemName").ToString() .ProductCode = dr("ProductCode").ToString() .ProductTypeCode = dr("ProductTypeCode").ToString() .Tag = 0 End With ElseIf CurrentTypeCode = "M" AndAlso (PreviousTypeCode = "MC" OrElse PreviousTypeCode = "MAO") Then With Output0Buffer .AddRow() .TransactionNumber = dr("TransactionNumber").ToString() .RowType = dr("RowType").ToString() .RowNumber = CInt(dr("RowNumber")) .ItemName = dr("ItemName").ToString() .ProductCode = dr("ProductCode").ToString() .ProductTypeCode = dr("ProductTypeCode").ToString() .Tag = 1 End With Else With Output0Buffer .AddRow() .TransactionNumber = dr("TransactionNumber").ToString() .RowType = dr("RowType").ToString() .RowNumber = CInt(dr("RowNumber")) .ItemName = dr("ItemName").ToString() .ProductCode = dr("ProductCode").ToString() .ProductTypeCode = dr("ProductTypeCode").ToString() .Tag = 0 End With End If Next dt.Rows.Clear() End Sub End Class Thanks,