Post reply

Require Suggestion on third party tools both PAID & FREE

  • August 21, 2015 at 12:28 am

    #299934

    Hi All,

    I have designed a SSIS package which archives historical data from LiveDB to ArchiveDB.

    Archival is done on department basis, each dep lead is given choice of how much amount of live data is required. If the LEAD selects '100'. Only 100 days of data is stored at LiveDB and other data is moved to Archival. All this info is stored in a table and dep LEADS have access to UPDATE the values. SSIS PACKAGE refers to this value while archiving.

    However I would keep all the DIM tables in sync with LiveDB and ArchivalDB which would be a simple PROC.

    This Package only archives FACT tables, all the FACT tables are kept in a SEQUENCE Container such that they run in parallel.

    My Organization wants a third party which validates the outcome of the package sent a mail to DBA's about the archival status.

    Validations like:

    It should trigger the package at a point of time between 1AM and 5AM as per we schedule.

    How many records are INSERT(ed) from STAGING table to PROD table.

    How much data is loaded into FLAT files using BCP QUERYOUT and how much did the BULK INSERT process load the data into Archival DB.

    It should keep a note of the failed process TABLEs and should include in the mail which tables have succeeded and failed.

    There should be a choice to select or run only the failed tables manually.

    We would appreciate if there is any option to auto-trigger the package to execute only the failed STEPS/ TABLEs on next day at the same time.

    Thank you, please let me know if you require any other info.

  • August 24, 2015 at 3:40 am

    #1821897

    bounce

  • August 24, 2015 at 11:31 am

    #1822057

    To be honest, your questions reflect a fairly large project. My recommendation is that you and your team get started on these things and if you run into a specific problem, then ask a specific question.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 24, 2015 at 11:37 am

    #1822060

    All of the functionality you just mentioned (logging record counts, event handling, scheduling, email alerts, etc.) are basic functionality of SSIS and SQL Server Agent.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

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

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