First, you didn't ask for an SSIS solution, you asked for a SQL solution.

Second, use the right tool for the job. This isn't a job for SSIS, it is a job for SQL.

Third, I have another SQL solution:

with TestData as (

select

PassM,

cast(EnterDt as datetime) EnterDt,

ConsecutiveD

from

(values

('Boo K K','5/1/2012 11:55:00 PM', 1),

('Boo K K','5/2/2012 11:30:00 PM', 2),

('Boo K K','5/4/2012 10:30:00 AM', 1),

('LIAW S','4/30/2012 11:48:52 PM', 1),

('LIAW S','5/1/2012 00:11:07 AM', 2),

('LIAW S','5/1/2012 11:59:07 AM', 2),

('LIAW S','5/1/2012 4:42:02 AM', 2),

('LIAW S','5/2/2012 1:10:09 AM', 3),

('LIAW S','5/2/2012 1:43:06 AM', 3),

('LIAW S','5/4/2012 2:17:47 AM', 1)

)dt(PassM, EnterDt, ConsecutiveD)

)

, GrpDates as (

select

PassM,

EnterDt,

GrpDate = dateadd(dd, dense_rank() over (partition by PassM order by dateadd(dd,datediff(dd,0,EnterDt),0)) * -1, dateadd(dd,datediff(dd,0,EnterDt),0)),

ConsecutiveD

from

TestData

)

select

PassM,

EnterDt,

ConsecutiveD,

dr = dense_rank() over (partition by PassM, GrpDate order by dateadd(dd,datediff(dd,0,EnterDt),0))

from

GrpDates

order by

PassM,

EnterDt;

When you run the code above, compare the ConsecutiveD column (the expected results) to the dr column (the computed Consecutive Days column).