Post reply

import varying number of tables each time from one database to a different databasse

  • September 9, 2015 at 4:58 pm

    #321132

    I am new to SSIS. I have been struggling with this for the past one week. I have a weird task. I need to import several tables from one database to a different server with a new database name. We need to do this at the end of every year. The main problem here is that the number of tables varies every year. You may not have all the tables as last year or may have more tables. So I need to create a dynamic task that takes care of this every year without changing the package.

    I have performed the following tasks **

    1. Create a new dynamic database. ( I have used Execute SQL Task to do this) 2. Copy all the table structures ( I have used Execute SQL Task to do this)

    3. Import Data

    This is the main problem. I was trying to create a dynamic connection string with variables as suggested in several forums but I finally came to know that this cannot be done if the table structures are different as the metadata cannot be refreshed at runtime.

    4. The final step to create a process to validate the data (the count from each table for both source and destination. I think this can be done with Sql task.

    Any suggestions what is the best method to do this? My DBA does not like “Transfer SQL Objects Task” or “transfer Database Task”. I would like to create this as a dynamic process.

    Thanks in advance ylsv

  • September 10, 2015 at 7:31 am

    #1825660

    Hi

    Imho it's better (faster) to backup and restore a database to a new location.

    Best regards

    Mike

  • September 10, 2015 at 7:38 am

    #1825661

    michal.lisinski (9/10/2015)

    Hi

    Imho it's better (faster) to backup and restore a database to a new location.

    Best regards

    Mike

    I'd try the backup and restore method first.


    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 10, 2015 at 8:06 am

    #1825677

    Thanks you both. I agree with you both but here the things are little weird. There is only one DBA and she has to take care of lot of the back ups and processes. This is only for creating space on the current database. She does not want o maintain this. Any other suggestions, please?

    Thanks

    ylsv

  • September 10, 2015 at 8:10 am

    #1825679

    ylsvani (9/10/2015)

    Thanks you both. I agree with you both but here the things are little weird. There is only one DBA and she has to take care of lot of the back ups and processes. This is only for creating space on the current database. She does not want o maintain this. Any other suggestions, please?

    Thanks

    ylsv

    Backup and restore is probably the easiest way to do this. All you have to do is take the last good backups and restore them on the destination server. You don't even need real DBA to do this. You could actually create a SSS package to do it.

    Any other solution would take more resources to maintain.


    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]

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

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