The Effective Date and Expiry Date in our SCD type 2 dimension is date only (no time component),i want to set the Expiry Date = Effective Date -1 of the new row, (Means a daybefore) From The Source EmpID EmpName Salary RowEffectiveDt 1 Venkat 2000 10/1/2009 1 Venkat 3000 21/1/2010 1 VenkatNaidu 3000 24/1/2010 Target would be in scd type 2 EmployeeKey EmpID EmpName Salary RowEffectiveDt RowExpirationDt CurrentFlg 1 1 Venkat 2000 10/1/2009 20/1/2010 N 2 1 Venkat 3000 21/1/2010 23/1/2010 N 3 1 VenkatNaidu 3000 24/1/2010 12/31/9999 Y So How can i handle this type of SCD and it cludes delete flow also where we will write RowExpiration logic in,i need detail level explanation. can any one pls help me Thanks in advance gvgnaidu

Something like there I guess Found this in http://blog.tallan.com/2008/08/14/the-scd-transformation-in-ssis/Arthur My Blog

Hi Arthur, Thanks for reply but i need more explanation,i need to implement both current flg and date logic Thanks in advancegvgnaidu

the current flag could be set in a Derived Column with an expression like: RowEffectiveDt < GETDATE() && RowExpirationDt >= GETDATE() ? "Y" : "N" http://msdn.microsoft.com/en-us/library/ms141680.aspx ? : operator http://msdn.microsoft.com/en-us/library/ms139875.aspx GETDATE() Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

the current flag could be set in a Derived Column with an expression like: RowEffectiveDt < GETDATE() && RowExpirationDt >= GETDATE() ? "Y" : "N" http://msdn.microsoft.com/en-us/library/ms141680.aspx ? : operator http://msdn.microsoft.com/en-us/library/ms139875.aspx GETDATE() Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter