Help with deploying my warehouse.

  • OK, so basically I have a production database with 5 tables, and a warehouse with the same 5 tables and many other aggregate tables.

    The aggregates all pretty much take care of themselves, but I need a solid SSIS package to manage moving the data from the 5 production tables to the 5 warehouse tables.

    I cannot use the simple routine of just copying the data and structures the way they are, as the warehouse "destination" tables are slightly different from the production "source" tables.

    Instead, I am planning on doing the following:

    1.) Make 1 run at populating the destination tables with all the data from the source tables.

    2.) Create an INSERT VIEW for each of the destination tables that basically will display all the data added to the source tables, but not in the destination tables.

    3.) Create an UPDATE VIEW for each of the destination tables that basically will display what records have been updated in the source and need to be updated in the destination tables.

    4.) Make a stored procedure that will INSERT everything displayed by the INSERT VIEW into the destination tables each night.

    5.) Make another stored procedure that will UPDATE everything displayed in the UPDATE VIEW into the destination tables each night.

    6.) Make yet another stored procedure that will delete any data removed from the source production table from the destination tables each night.

    Will that work?

    Is there an easier way to do that? I mean, I can create an SSIS package with an OLE DB Source and an OLE DB destination, but that's only for step #1. I cannot use the same package to somehow "refresh" what has changed can I?

  • Hi,

    You can use SSIS to do all of this. No need to have views and stored procedures doing this. You'd pull from you source table in SSIS and transform it to be what you need. You'd then load that in your destination. As you are doing this you can run checks on the source and destination to determine what changed all in your package.

    It can be a little hard to explain without knowing your structure though. But based on what you layed out in your steps, SSIS can do all of that.

    Strick

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply