Hi Guys, I have these codes that gives my the desire output before. These codes scanned the records and if the current RowType <> "TENDER" and previousC1 = "TENDER", assign the value of RowType = "TOTAL" to a variable ouput. Before, if the RowType is "TENDER", it means that its the end of a transaction group. <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _ <CLSCompliant(False)> _ Public Class ScriptMain Inherits UserComponent Private dt As DataTable Private previousC1 As String = String.Empty Public Overrides Sub PreExecute() MyBase.PreExecute() dt = New DataTable() dt.Columns.Add("BusinessDate") dt.Columns.Add("StoreCode") dt.Columns.Add("Brand") dt.Columns.Add("TransactionType") dt.Columns.Add("RowType") dt.Columns.Add("VoidFlag") dt.Columns.Add("TransactionDate") dt.Columns.Add("TransactionTime") dt.Columns.Add("TransactionNumber") dt.Columns.Add("CashierID") dt.Columns.Add("Data6") dt.Columns.Add("ItemName") dt.Columns.Add("Quantity") dt.Columns.Add("Amount") dt.Columns.Add("ProductCode") dt.Columns.Add("ParentProductCode") dt.Columns.Add("TenderCode") dt.Columns.Add("DiscountCode") dt.Columns.Add("CustomerID") dt.Columns.Add("POSNumber") dt.Columns.Add("RowNumberPre") End Sub Public Overrides Sub Input0_ProcessInput(ByVal Buffer As Input0Buffer) While Buffer.NextRow() If Buffer.RowType.Trim() <> "TENDER" AndAlso previousC1 = "TENDER" Then FetchOutGroupResults() End If Input0_ProcessInputRow(Buffer) previousC1 = Buffer.RowType.Trim() End While If Buffer.EndOfRowset() Then FetchOutGroupResults() Output0Buffer.SetEndOfRowset() End If End Sub Private Sub FetchOutGroupResults() Dim vCustomerID As String = String.Empty For Each dr As DataRow In dt.[Select]("RowType='DELIVERY'") vCustomerID = Convert.ToString(dr("VoidFlag")) & "," & Convert.ToString(dr("TransactionTime")) Next Dim vCustomerPAIDOUT As String = String.Empty For Each dr As DataRow In dt.[Select]("(TransactionType='DELIVERY' And RowType<>'ITEM' And RowType<>'TAX' And RowType<>'DISCOUNT' And RowType<>'DELIVERY CHARGE' And RowType<>'TENDER' And RowType<>'TOTAL' And RowType<>'DELIVERY')") vCustomerPAIDOUT = Convert.ToString(dr("RowType")) & "," & Convert.ToString(dr("TransactionDate")) Next Dim vPOSNumber As String = String.Empty For Each dr As DataRow In dt.[Select]("RowType='TOTAL'") vPOSNumber = dr("Data6").ToString() Next For Each dr As DataRow In dt.Rows Output0Buffer.AddRow() Output0Buffer.BusinessDate = dr("BusinessDate").ToString() Output0Buffer.Brand = dr("Brand").ToString() Output0Buffer.StoreCode = dr("StoreCode").ToString() Output0Buffer.TransactionType = dr("TransactionType").ToString() Output0Buffer.RowType = dr("RowType").ToString() Output0Buffer.VoidFlag = dr("VoidFlag").ToString() Output0Buffer.TransactionTime = dr("TransactionTime").ToString() Output0Buffer.TransactionNumber = dr("TransactionNumber").ToString() Output0Buffer.CashierID = dr("CashierID").ToString() Output0Buffer.Data6 = dr("Data6").ToString() Output0Buffer.Name = dr("ItemName").ToString() Output0Buffer.Quantity = dr("Quantity").ToString() Output0Buffer.Amount = dr("Amount").ToString() Output0Buffer.ProductCode = dr("ProductCode").ToString() Output0Buffer.ParentProductCode = dr("ParentProductCode").ToString() Output0Buffer.TenderCode = dr("TenderCode").ToString() Output0Buffer.DiscountCode = dr("DiscountCode").ToString() If dr("RowType").ToString().Trim() = "TENDER" Then Output0Buffer.CustomerID = vCustomerID ElseIf dr("RowType").ToString().Trim() = "PAIDOUT" Then Output0Buffer.CustomerID = vCustomerPAIDOUT Else Output0Buffer.CustomerID = String.Empty End If Output0Buffer.POSNumber = vPOSNumber Output0Buffer.RowNumberPre = dr("RowNumberPre") Next dt.Rows.Clear() End Sub Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) dt.Rows.Add(Row.BusinessDate, Row.StoreCode, Row.Brand, Row.TransactionType, Row.RowType, Row.VoidFlag, Row.TransactionDate, Row.TransactionTime, Row.TransactionNumber, Row.CashierID, Row.Data6, Row.ItemName, Row.Quantity, Row.Amount, Row.ProductCode, Row.ParentProductCode, Row.TenderCode, Row.DiscountCode, Nothing, Nothing, Row.RowNumberPre) End Sub End Class Now, my client wants me to modify the code. Instead of using the RowType = "TENDER" as the breakpoint of a one transaction group, they want to replace it by the Trans#. Here's the sample data. Trans# RowType Data6 123 ITEM 0 123 ITEM 0 123 ITEM 0 123 ITEM 0 123 TAX 0 123 DELIVERY CHARGE 0 123 TOTAL 1 456 TENDER 0 456 ITEM 0 456 ITEM 0 456 ITEM 0 456 ITEM 0 456 TAX 0 456 DELIVERY CHARGE 0 456 TOTAL 2 456 TENDER 0 456 TENDER 0 456 TENDER 0 My goal is the same, I want to use the Tran# column instead of RowType = "TENDER". To have this results: Trans# RowType Data6 POS# 123 ITEM 0 1 123 ITEM 0 1 123 ITEM 0 1 123 ITEM 0 1 123 TAX 0 1 123 DELIVERY CHARGE 0 1 123 TOTAL 1 1 456 TENDER 0 1 456 ITEM 0 2 456 ITEM 0 2 456 ITEM 0 2 456 ITEM 0 2 456 TAX 0 2 456 DELIVERY CHARGE 0 2 456 TOTAL 2 2 456 TENDER 0 2 456 TENDER 0 2 456 TENDER 0 2 Thanks,

Take a look at DENSE_RANK function ORDER BY Trans#Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

One thing I’m not sure is the POS# value of the record (456 TENDER), it should be 2, right? If so, you need to check DENSE_RANK() as URi Dimant suggested. If you can use T-SQL direclty then it will be the best choice, otherwise, one simply way is load the data to a staging table and then use T-SQL to extract data in another data flow. Hope this helps, Raymond Raymond Li - MSFT

I suggest against using T-SQL - if that were the solution, you'd be using it already. All you need to do is watch the transaction column instead of the rowtype column. In your "detection" code, instead of checking rowtype, check to see if your transaction number is the same as what you have stored. If it isn't then call your output just like you have, and assign the new transaction number to your stored value. Clear as mud? Talk to me now on