Replication with SSIS (Data transformation)

  • Hi,

    I'm trying to think of the best way to achive the following.

    I want to replicate a normalised database into a denormalised table for reporting in real time (transactional replication). I only want certain columns and in some cases certain rows (pivoted)

    Any suggestions?

    Cheers

  • This was removed by the editor as SPAM

  • If I replicate the view, I will also need to replicate the base tables wont I?

    I don't really want a view over the current tables, I would like a flattened cut down version if possible.

    Thanks

  • This was removed by the editor as SPAM

  • stewartc-708166 (6/17/2011)


    No, as far as the publisher is concerned, the view is a table (albeit virtual) and will publish to a table at the destination.

    this will be substantially more efficient (and contribute towards the online reporting you require) than an SSIS package, scheduled to run at regular intervals (e.g. every 15 minutes), with much less overhead.

    That is not true. You cannot replicate a regular view and have it work on the subscriber unless you also replicate the base tables.

    You can however replicate an indexed view (persisted locally on publisher) as a concrete table to a subscriber.

    http://msdn.microsoft.com/en-us/library/ms152559.aspx

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Excellent thanks. I think that option would work 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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