Post reply

Migrating Multiple Tables from one DB to Another

  • September 22, 2016 at 10:21 am

    #329963

    Hi,

    I am relatively new to SSIS packages and am in the process of creating my own data warehouse from an existing data warehouse. There are about (21) tables in the existing DW that I need to create the new one. I have successfully moved one table over utilizing a single Control Flow item with (3) Data Flow items inside of it (Data Flow Source to Data Conversion to Data Destination):

    I guess my question is, should I duplicate the Control Flow item (21) times and then modify each of them to point to their respective tables, or should I duplicate the Data Flow items 21 times inside of the one Control Flow item? I would want to tie them all together either way and add some Event Handlers? Is there an advantage to tying them all together at the Control Flow level vs. tying all the Data Flow items together inside (1) Control Flow?

    Thanks,

    Jerid

  • September 22, 2016 at 10:29 am

    #1902544

    Take a look at the "Transfer SQL Server Objects Task" task. If it works for you, it could save you a lot of work.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • September 22, 2016 at 10:34 am

    #1902545

    Alvin,

    Thanks for the response! Wouldn't running a task be a one-time thing though? I want to run this package daily to make sure that the data in the source db always matches the data in the destination db. That's why I thought that a SSIS package would be the answer.

    Thanks,

    Jerid

  • September 22, 2016 at 10:41 am

    #1902550

    Jerid421 (9/22/2016)

    Alvin,

    Thanks for the response! Wouldn't running a task be a one-time thing though? I want to run this package daily to make sure that the data in the source db always matches the data in the destination db. That's why I thought that a SSIS package would be the answer.

    Thanks,

    Jerid

    Oh? You didn't say earlier that you wanted to run this daily to keep the data in sync.

    There's many ways you can do what you're trying to do. Creating a separate dataflow for each table is an option. Another simple option, which might be less work, is to simple copy the data using SQL Tasks.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • September 22, 2016 at 10:54 am

    #1902555

    Jerid421 (9/22/2016)

    Hi,

    I am relatively new to SSIS packages and am in the process of creating my own data warehouse from an existing data warehouse. There are about (21) tables in the existing DW that I need to create the new one. I have successfully moved one table over utilizing a single Control Flow item with (3) Data Flow items inside of it (Data Flow Source to Data Conversion to Data Destination):

    I guess my question is, should I duplicate the Control Flow item (21) times and then modify each of them to point to their respective tables, or should I duplicate the Data Flow items 21 times inside of the one Control Flow item? I would want to tie them all together either way and add some Event Handlers? Is there an advantage to tying them all together at the Control Flow level vs. tying all the Data Flow items together inside (1) Control Flow?

    Thanks,

    Jerid

    One way of doing this is to create 21 packages, one for each table.

    Then have a single master package which calls the 21 child packages, in whatever order is best.

    Doing it this way keeps things simple and tidy and helps make it easier to identify the cause of any errors.

    It would also allow you to easily manipulate and experiment which should run in parallel and which in series.

    The packages would be so simple that a BIML [/url]solution sounds like a good fit.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • September 22, 2016 at 10:58 am

    #1902561

    Phil's suggestion also makes it much easier to update just one table, or a subset of tables.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • September 22, 2016 at 12:10 pm

    #1902598

    I don't use SSIS so I can't help much there but... how would setting up Replication (not a favorite of mine, BTW) for these 21 tables compare or is that (as I suspect) a totally unnecessary complication?

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

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

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