Post reply

Transfer Objects task

  • June 20, 2011 at 10:29 am

    #148237

    Hello guys,

    I have to copy objects between 2 schemas of 2 databases. I need to select all the objects within schema and BIDS does not give any option of selecting all the tables within schema or selecting all the procedures within a schema. Right now I need to select all the objects manually as there are multiple schemas in my source database. If any one has got any ideas for automating this task, it will be really helpful.

    Thanks,

    Nick

  • June 21, 2011 at 2:16 pm

    #1342117

    Do you have to use SSIS non-sql task or can you do this in SQL or a sql task. SQL has commands to do things for each object in a database and such.

    http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

  • June 21, 2011 at 2:31 pm

    #1342125

    Hey Mark,

    Thanks for the reply. If I use non SSIS task, I can either use Sql Import and Export Wizard or use SQL scripting for copying the data. If I use SQL Scripting (ms_foreachtable etc) for tables coding gets more complicated as number of tables and relations grow. If I use SSIS or SQL import and export wizard it does not have any option for me to select Schema level objects, I dont want to select it manually rather it will be nice if I can select all the objects within schema.

    Thanks,

    Nikhil

  • June 21, 2011 at 2:35 pm

    #1342130

    Yes, you cannot select an entire schema in SSIS. This sort of goes against the concept of metadata in the data flow task. I'm not sure why doing it in sql would be any more code, you can execute a command with the stored proc once per table in the schema, either doing a insert...select * or doing a sp_rename on the object, but to be honest with you...this seems like a really odd way of moving data around.

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

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