Sql server 2005 integration slowly chaning dimension

• vasu.ssis

  Default port

  Points: 1423

  More actions

  July 24, 2008 at 12:33 am

  #130175

  hi alls,

  i am new to SSIS. i have one requirement that is in my source 3 colums is there.in that one column was username.and my requirement is using slowly changing dimension change records if username chage and my difficult problem is i created one column extra in destination.in that who username are not active it will display number(-1) and who are active display(1) in my destination.what we have to do in slowly changing dimension.

  i hope anybody help

  vasu

• SrikanthSv

  Ten Centuries

  Points: 1197

  More actions

  July 24, 2008 at 8:22 pm

  #848171

  I assume that if the user is incoming record he is active otherwise he is inactive

  well then in the flow add a derived column transformation and update it in the DB

  Regards
  Venkat
  http://sqlblogging.blogspot.com[/url]

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  July 25, 2008 at 2:50 pm

  #848701

  Its very simple

  Take OLE DB SRC first..

  Then Take Lookup and take ur target table in that...map ID take name from target (change to name1)

  Configure Errorputput sAY redirect row...OK

  Now from Lookup you will be getting 2 arrows(Green & RED)

  TAKe Green to Conditional Split

  TYPE condition name != name1 say OK

  Take Condition(case1) arrow to OLE DB COMMAND..

  WRITE THIS

  UPDATE with your target

  update usertrg

  set flag = -1

  where uid = ?

  in Variable mapping MAP UID (coming from SRC to PARAM0)

  SAY OK..

  After this add an OLE DB DESTINATION take target table...

  MAP ALL Except Surrogate key and FLAG

  ----------------------DONe wid GReen Arrow

  Now the RED arrow of Lookup take it to OLE DB DESTINATION and do the mapping for user trg table.

  When you create table

  Surrogate key is there in Target table and Primary key

  and the default Value for Flag = 1

  Hope this Helps

  [font="Comic Sans MS"]
  ---------------------------------------------------

  Thanks [/font]

• SrikanthSv

  Ten Centuries

  Points: 1197

  More actions

  August 1, 2008 at 1:22 pm

  #852153

  I think using SCD component will also do the job.

  try it out if you don't get it jus shoot out an email to me. @ emailsv@gmail.com

  Priya: Just trying to get as many approaches as possible...nothing against ur approach...

  Regards
  Venkat
  http://sqlblogging.blogspot.com[/url]