Post reply

Clone Staging-Data to RemoteServer

  • July 18, 2017 at 2:15 am

    #402319

    Hi,
    How could i automate the following Task?: (DWH / Staging-Tables)
    Loop through all the tables (from a list), and import the data from the current day (ImportDate = getdate ()) and copy the Data into an identical database on another server. Same DB-Structure (is a clone). Basically, following SQL would have to be generated for all tables:
    INSERT INTO RemoteServer. [Dbo]. [Customer]
                (ColumnList ...)
        SELECT
                ColumnList ....
        FROM LocalServer. [Dbo]. [Customer]
        WHERE ImportDate = getdate()

    Realize with SSIS, C #, T-SQL / Merge, SQL Server Tools, Replicate ....

    Thanks
    Regards
    Nicole :Whistling:

  • August 14, 2017 at 10:08 am

    #1955183

    Hi Nicole,

    I may not have a complete solution for you, but here are my initial thoughts...

    "Loop through all the tables (from a list)" -> You can use a Foreach Loop container to loop through your list of tables. Depending on how your list is stored (e.g. .csv file, table, variable), you would set the Enumerator property for the container accordingly. On the Variable Mappings tab, create a variable and map it to the value in the collection (.csv file, table, etc.) that you are using so that as the container loops through your list of tables, it assigns the table name to the variable with each pass through the loop.

    "...import the data from the current day (ImportDate = getdate ())" -> Within the Foreach Loop container, you can place an Execute SQL task. Within the Execute SQL task's "SQLStatement" property, paste your SQL code for inserting the data. For the "FROM" clause of your statement, put the variable that you mapped in the Foreach Loop. For each pass through the loop, you will be pulling data from the table that you are currently at in your list.

    Also, for the "INSERT INTO" clause, use the same variable that you used for the "FROM" clause, since your destination database is identical to your source database. You will be inserting into a table in your destination database with the same name as the table in your source database. You may need to do some tweaking to this to get the syntax correct. Maybe something like:

    INSERT INTO "RemoteServer." + @VariableA

    Those are my thoughts. Hope this helps. If I can clarify anything, please let me know!

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

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