none
SSIS ETL scenario

    Question

  • please advice cme the complete scenationfor the given scenario in which

    there are two SQL Server database DB1 and DB2 as source and one cube as a Destination , now within particular time cycle the merged data from both SQL Server DB1 and DB2 extracts and load in to the cube, then simultaneously that cube is Sync with both DBs 1and 2 , suppose when any of the data get deleted either from DB1 or DB2  , one DeleteFlag Column in the Cube get 'Y' other wise by default it remains 'N'

    please guide me using which Tasks, transformations etc. i can design this scenarios in SSIS packages

     

    many many thanks in advance.

    Regards,

    Amitesh Srivastva

     

     


    Regards, Amitesh Srivastva
    Monday, February 28, 2011 12:35 PM

Answers

  • Hello Amitesh,

    to achieve "suppose when any of the data get deleted either from DB1 or DB2  , one DeleteFlag Column in the Cube get 'Y' other wise by default it remains 'N'" you better use a table trigger (on delete namely).

    The rest of the question is about synchronization. 

    This MSDN SSIS post offers some help on the cube sync: http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/c9f763d0-0da6-4b18-bc7d-4b8106b47e16 if you need that, but I am not sure what exactly you are trying to figure out.


    Arthur My Blog
    By: TwitterButtons.com
    • Marked as answer by KJian_ Monday, March 07, 2011 8:37 AM
    Monday, February 28, 2011 2:50 PM
    Moderator
  • As Arthur said you can use a trigger for the updting the cube. how ever Trigger is not somthing advisable(look into the pros and cons).

    Coming to SSIS : SSIS will not be in a condition to know when a record is deletd from a table and do the needful. There has to be an event raised once the records are deleted. Either you need to look into the place(application/code) where the records are getting deleted and fire a update in the cube.

    Else another approach is there assuming all the records in DB1 & DB2 are there in the cube. You would have to read the data from the cube and do a left join with the db1 & DB2 data. All the extra records that you get will be the records that are deleted. Now update these recods in the cube with Y.


    My Blog    |    Ask Me
    • Marked as answer by KJian_ Monday, March 07, 2011 8:37 AM
    Monday, February 28, 2011 3:53 PM

All replies

  • Hello Amitesh,

    to achieve "suppose when any of the data get deleted either from DB1 or DB2  , one DeleteFlag Column in the Cube get 'Y' other wise by default it remains 'N'" you better use a table trigger (on delete namely).

    The rest of the question is about synchronization. 

    This MSDN SSIS post offers some help on the cube sync: http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/c9f763d0-0da6-4b18-bc7d-4b8106b47e16 if you need that, but I am not sure what exactly you are trying to figure out.


    Arthur My Blog
    By: TwitterButtons.com
    • Marked as answer by KJian_ Monday, March 07, 2011 8:37 AM
    Monday, February 28, 2011 2:50 PM
    Moderator
  • As Arthur said you can use a trigger for the updting the cube. how ever Trigger is not somthing advisable(look into the pros and cons).

    Coming to SSIS : SSIS will not be in a condition to know when a record is deletd from a table and do the needful. There has to be an event raised once the records are deleted. Either you need to look into the place(application/code) where the records are getting deleted and fire a update in the cube.

    Else another approach is there assuming all the records in DB1 & DB2 are there in the cube. You would have to read the data from the cube and do a left join with the db1 & DB2 data. All the extra records that you get will be the records that are deleted. Now update these recods in the cube with Y.


    My Blog    |    Ask Me
    • Marked as answer by KJian_ Monday, March 07, 2011 8:37 AM
    Monday, February 28, 2011 3:53 PM