SSIS to the rescue, Replication, or overkill?

  • Question -

    I need to pull data from our internal SQL Server Database, create a few derived tables which are truncated and repopulated daily, and get this data out to our public web site in a set of identical tables. The tables on the web site match identically in design to the tables which are derived on our internal servers. So the daily flow is this:

    (1) Multiple intenral tables >> Views & Stor Procs >> (2) Truncate and Populate Derived Tables >> (3) Get data out to web site in matching tables.

    The method I'm using to populate the web site tables is I am deleting all the records in these dummy tables and Importing the data through SSMS Wizards. So far, so good. This is beta web site and it is only day 2 of this process so it's still all new.

    I can easily do this through SSMS because I can connect to both our interal database and the one residing on the web hosting servers.

    So my question is this - although I haven't tried yet, wouldn't it make sense just to eliminate the Derived tables I am populating daily in step 2 and just get that data out to the web site to THOSE tables directly? As long as SSMS is connected to both SQL Server databases, this should be possible through a Stored Procedure right since I can do it manually through Import/Export Wizards.

    If this is true the I suppose it's just a matter of syntax in the Stored Proc right??

    Thoughts? Advice?

  • I like derived (staging) tables as an intermediary, and I'd probably use SSIS to move the data regularly. No reason a stored proc can't work, but I tend to stick these things in a package as if things move it can be easier to reconfigure. Especially if you needed to separate logic and use files to do this later and connectivity is lost (for security reasons).

    The staging tables give you a place to move data, without causing issues in the other tables. Prevents locking/blocking, or anything else if there are issues.

  • That's kind of the direction I'm leaning towards as well. Although it contains duplicate data, I don't mind having a staging database that can be trashed and recovered at will. No impact. I guess SSIS is where I will start. Now...to learn SSIS....:-D

  • Use the import wizard to get you started.

    Or look through the Stairway here. It does an incremental load that might help

    http://www.sqlservercentral.com/stairway/72494/

  • Excellent. Thanks! Sounds like a good Monday morning reading....:-)

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

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