HI,

Suppose in my table i have 300 records. In that 300 records i want to update first 100 records with today's date. 101 to 200 records with yesterday's date and 201 to 300 records with tommorow's date. How can we do this ?

• Edited by SSISJoostMVP, Moderator 11 hours 44 minutes ago title too general. It's an SSIS forum!

For the first set add an order by clause and a top 100 in the update statement.
update top (100) table1 set field1 = GETDATE()

in the second and third add a where clause to filter the first set(s).

update top (100) table1 set field1 = DATEADD(day,-1, GETDATE()) WHERE field1 < GETDATE()

Depending on the datatype (date/datetime), you might have to add some more code to handle time issues 

Hi Koteswararao,

Based on my test, we can delete the table data, then generate an auto row number column for this table with Script component of SSIS, and then insert the records based on the row number column. For more details, please see:

1. Use an Execute SQL Task to delete the table data, then connect it to a Data Flow Task.
2. Drag and drop the OLE DB Source to connect to the source table in Data Flow Task.
3. Drag and drop the Script Component to connect to the Data Flow Task and select Script Component Type as Transformation.
4. Double click the Script Component.
5. In the Input Columns tab, select the column you want to pass through the script component.
6. In the Inputs and Outputs tab, add a column named Row_Number with an integer data type.
7. Go to the Script tab and click the Design Script button. Type the following script in the script editor:
   Imports System
   Imports System.Data
   Imports System.Math
   Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
   Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
    Public Class ScriptMain
        Inherits UserComponent
        Dim intCounter As Integer = 1 Set intial value here
        Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
             Row. Row_Number = intCounter
             intCounter = intCounter + 1   Set the incremental value here
        End Sub
    End Class
8. Drag a Derived Column Transformation connect to the Script Component, then add a Derived Column with the expression below:
   ([Row_Number]>0 &&  [Row_Number]<101)? GETDATE(): ([Row_Number]>100 &&  [Row_Number]<201)? DATEADD("day",-1,  GETDATE()):DATEADD("day",1,  GETDATE())
9. Select the Derived Column maps to the OLE DB Destination with the original column.
   

Thanks,
Katherine Xiong