Post reply

How to Copy Objects with a SQL 2005 SSIS package

  • March 15, 2006 at 8:27 am

    #116438

    I raised this issue with SQL 2005 SSIS a few weeks ago but I would like to bring it up again because I've been banging my head against the wall and still have not found a solution.

    The problem is this: In SQL 2000 DTS there was an option for "Copy Objects and Data Between SQL Servers". However, this option has been removed in SQL 2005 SSIS. Apparently the only way to do this in SQL 2005 is to create a .DTSX package in SQL Server Business Intelligence Developement Studio or VS 2005. You do this by creating a new Integration Service Project and using the Transfer SQL Objects Task. Within the properties of this task you can select any of the options that were available in the SQL 2000 DTS export wizard. I have set up a test package that will copy a stored procedure from one db to another but I am unable to get it to work. It runs fine but the result is that the SP is not copied.

    I am new to Visual Studio and I think I probably just need help in knowing how to run a package in SQL Server Management Studio. I was able to import the package into SSIS in the Management Studio and run it without errors, but not with the expected result (the copy of an SP from one db to another). I'm sure there are people besides me who would like to have the ability to easily perform ad hoc copies of objects between SQL servers. If anyone has any experience with using a SSIS package to do this please help. Thanks!

  • March 20, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • March 22, 2006 at 9:22 am

    #628138

    Hi,

    I also am new to SQL 2005, here is my understanding of how this process works, hope it helps.

    - Use Visual Studio or SQL Business Intelligence to build a package

    - Run/Debug the Package which is the default option

    - Once you sail through the debug without any error, now you need to Build the package (select package name under Build menu).

    - Now you should be able to find what you built in the SQL Management Studio. Incase of Stored Procedure you should see it in Programmability folder of your database.

    To see your packages in Management Studio:

    - View -> Registered Servers (CNTRL+Alt+G)

    - Click on Integration Service Icon (first one from the right)

    - Double click on your servername and now you get to see all the running and stored packages.

    Best,

    Michael Sh

  • April 11, 2006 at 6:58 am

    #631900

    In theory you should be able to use 'CopyDatabaseWizard.exe' 

    This is buried somewhere in c:/Program Files/Microsoft SQL Server/90/Tools/Binn/VS Shell/Common7/IDE

    Haven't yet managed to get it to work - but have a look. If you get any success then post your results

    Regards

  • April 11, 2006 at 8:08 am

    #631926

    Ryan I would like to sympathise. Unfortunately I don't have any answers and I am also desperate to know how to copy objects in SQL Server 2005. I find it hard to understand why the option to copy objects and data between SQL server databases has been removed - I used this all the time in Enterprise Manager.

    Fingers crossed someone will provide us with a step by step guide on how to copy objects in SQL Server 2005. Or better still Microsoft decide to include it in the first service pack release. I'm sure there will be many people like us who are not familiar with Visual Studio yet use SQL Server every day and are now struggling to work out how to copy objects.

  • April 11, 2007 at 5:35 pm

    #699937

    As Ryan said in his first post, there is a task for copying objects between SQL Server databases - the Transfer SQL Server Objects task.  The hitch most people hit is that it's not included in the import/export wizard anymore.  You have to create an SSIS package in BIDS that includes it.  BTW, the wizard does still exist and you can use it in SQL Server Management Studio by right-clicking on a database and selecting Tasks.

    Greg

    Greg

  • April 13, 2007 at 1:53 pm

    #700505

    One method that has been successful for me when it comes to copying objects/tables in 2005 is the old fashion way (backup/restore). 
     
    - Make a full backup copy of source database (source.bak)
    - Create a database with same name on destination server & back it up
    - Copy the bakup file from source database (source.bak) to detination server
    - Restore database on destination server using "source.bak"
     
    This way I can copy database among different server with all objects  (excluding packages). I had no success with other methods: (keep getting bogus error messages)
        Tasks -> Copy database in Management Studio
        or Transer database Task in SSIS
        or Transfer SQL Server Object Task in SSIS
     
    Hope this helps.
     
    Best,
     
    Michael Sh

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

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