locked
Can I use SSIS to do this? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    I am new to SSIS and wondering if this is possible and if so can anyone explain to me the data flow that would be necessary. We are currently upgrading our databases. During this process we need to change values of medical lookup values from the first database and insert the updated medical lookup codes into the new database. Does SSIS have a data flow task that would allow me to lookup values from 1st database and tranform them into new database based on matches.  I have a flat file with columns of old lookup values and the ones that need to go into the new database. Any help would be greatly appreciated.
    Monday, June 16, 2014 4:47 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    Yes. You can use SSIS data flow for this. You need a package with flow as below

    1. data flow task with flat file source connecting to flatfile

    2. Lookup task to lookup wuth table on old values. Set lookup failure action as redirect row to No match output.

    3. Join Match output to OLEDB command and write update statement to update oldvalues with new ones

    update query would be like

    UPDATE Table
SET FieldName = ?
WHERE FieldName = ?


    and map 0 to new value and 1 to old value in parameter mapping tab

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Mike Yin Wednesday, June 18, 2014 5:56 AM
    • Marked as answer by Mike Yin Tuesday, June 24, 2014 4:01 PM
    Monday, June 16, 2014 5:02 PM