• Having 3 different databases has other advantages than those already mentioned. The BIG ones are that your "IMPORT" and "STAGING" databases likely don't need to be backed up because all of the data there can easily be recreated from import files and the data is, at best, transient in nature to begin with. That also means that both databases can be set to the SIMPLE recovery model and that means that you can also take advantage of high performance/low resource usage Minimal Logging methods especially when Trace Flag 610 is enabled.

    Please see the following white paper for more on Minimal Logging and the use of Trace Flag 610. The rub here is that I have no clue if any of that works in SSIS because I normally do all of this type of work in T-SQL.

    https://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx

    --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)