Compare rows, based on the value (i.e. date and status columns) insert a new row in SQL Server 2012 or SSIS 2012

I need to compare rows in a single table for three different columns "MSName", "MSStatus" and "MSDate". "ID" is the location Identifier and "MSName" is a column for several milestones to be completed on a specific location. Please ignore the "CalcSAED" column for now as I have created it as part of my solution. Please refer to the screenshot below for sample table:

Rules:

Condition 1: IF ( MSName = "FTA" AND MSStatus = "Actual" THEN "INSERT a new row with same column values for ("ID,"SName", "MSStatus", "MSDate") but MSName should "SAEDR",

Condition 2: ELSE IF( MSNAME ="Acc AAA" AND MSStatus ="Actual" AND Condtion 1 = FALSE THEN "INSERT a new row with same column values for ("ID,"SName", "MSStatus", "MSDate") but MSName should "SAEDR",

Condition 3: ELSE IF ( MSNAME ="SAED" AND MSStatus ="Actual" AND Condition 1 AND Condition 2 = FALSE THEN "INSERT a new row with same column values for ("ID,"SName", "MSStatus", "MSDate") but MSName should "SAEDR",

ELSE NEXT

Rules Flow Chart

Sample Table

Sample data

CREATE TABLE D1 (

             ID varchar(2)
            ,SName varchar(50)
            ,MSName varchar(50)
            ,MSStatus Varchar(10)
            ,MSDate Date
            ,CalcSAED varchar(10)
    )

INSERT INTO D1 VALUES ('A1', 'Melbourne', 'FTA', 'Actual', '2016-02-12', NULL)
    INSERT INTO D1 VALUES ('A1', 'Melbourne', 'Acc AAA', 'Pending', '2016-02-13', NULL)
    INSERT INTO D1 VALUES ('A1', 'Melbourne', 'SAED', 'Pending', '2016-02-14', NULL)
    INSERT INTO D1 VALUES ('A2', 'Melbourne1', 'FTA', 'Pending', '2016-02-15', NULL)
    INSERT INTO D1 VALUES ('A2', 'Melbourne1', 'Acc AAA', 'Actual', '2016-02-12', NULL)
    INSERT INTO D1 VALUES ('A2', 'Melbourne1', 'SAED', 'Pending', '2016-02-16', NULL)
    INSERT INTO D1 VALUES ('A3', 'Melbourne2', 'FTA', 'Pending', '2016-02-17', NULL)
    INSERT INTO D1 VALUES ('A3', 'Melbourne2', 'Acc AAA', 'Pending', '2016-02-12', NULL)
    INSERT INTO D1 VALUES ('A3', 'Melbourne2', 'SAED', 'Actual', '2016-02-11', NULL)
    INSERT INTO D1 VALUES ('A4', 'Melbourne3', 'FTA', 'Pending', '2016-02-10', NULL)
    INSERT INTO D1 VALUES ('A4', 'Melbourne3', 'Acc AAA', 'Pending', '2016-02-19', NULL)
    INSERT INTO D1 VALUES ('A4', 'Melbourne3', 'SAED', 'Pending', '2016-02-16', NULL)

    INSERT INTO D1 VALUES ('A5', 'Melbourne4', 'FTA', 'Actual', '2016-02-13', NULL)
    INSERT INTO D1 VALUES ('A5', 'Melbourne4', 'Acc AAA', 'Actual', '2016-02-16', NULL)
    INSERT INTO D1 VALUES ('A5', 'Melbourne4', 'SAED', 'Actual', '2016-02-18', NULL)

    INSERT INTO D1 VALUES ('A6', 'Melbourne5', 'FTA', 'Actual', '2016-02-13', NULL)
    INSERT INTO D1 VALUES ('A6', 'Melbourne5', 'Acc AAA', 'Pending', '2016-02-16', NULL)
    INSERT INTO D1 VALUES ('A6', 'Melbourne5', 'SAED', 'Actual', '2016-02-18', NULL)


    INSERT INTO D1 VALUES ('A7', 'Melbourne6', 'FTA', 'Actual', '2016-02-13', NULL)
    INSERT INTO D1 VALUES ('A7', 'Melbourne6', 'Acc AAA', 'Actual', '2016-02-16', NULL)
    INSERT INTO D1 VALUES ('A7', 'Melbourne6', 'SAED', 'Actual', '2016-02-18', NULL)

My lousy solution so far:

1. Create a Column called "CalcSAED" and update it as "True" For valid
2. UNION two select statements for "True" values and change their "MSName" to "SAEDR" and Complete Original table.

PS: I am not even sure if my query is 100% reliable but so far working with the sample table.

My solution code:

UPDATE D1 SET CalcSAED = NULL


    UPDATE D1
       SET CalcSAED = 'True'

      FROM D1 
INNER JOIN D1 AS D2
        ON D2.ID = D1.ID
INNER JOIN D1 AS D3
        ON D1.ID = D3.ID 
     WHERE (D1.MSName LIKE 'FTA' AND D1.MSStatus LIKE 'Actual')
        OR ((D1.MSName LIKE 'Acc AAA' AND D1.MSStatus LIKE 'Actual') AND (D2.MSName LIKE 'FTA' AND D2.MSStatus NOT LIKE 'Actual'))
        OR (D1.MSName LIKE 'SAED' AND D1.MSStatus LIKE 'Actual') AND  (D2.MSName LIKE 'Acc AAA' AND D2.MSStatus NOT LIKE 'Actual') AND (D3.MSName LIKE 'FTA' AND D3.MSStatus NOT LIKE 'Actual') 

GO

    ;WITH TempCTE(ID, SName, MSName, MSStatus, MSDate, CalcSAED) AS

        (


            SELECT ID, SName, MSName = 'SAED-R', MSStatus, MSDate, CalcSAED 
            FROM D1
            WHERE CalcSAED LIKE 'True'

            UNION ALL

            SELECT ID, SName, MSName , MSStatus, MSDate, CalcSAED 
            FROM D1



        )

        SELECT * FROM TempCTE
        ORDER BY ID ASC

Basically, I am using SSIS package to import data from SQL Server database onto a different SQL Server. Currently, I am planning to run this query at the end of SSIS package execution.I am not sure if this is the right way to do it. So, please any suggestions and advice on this will be highly appreciated.. Thanks in advance.