Database Object-Level Tasks

The last section of this chapter is reserved for a set of tasks that are going to be convenient for copying or moving schema and data-level information. These tasks are similar to the Transfer SQL Objects tasks from DTS and should be compatible if you transfer any packages using these tasks from DTS to SSIS. These tasks can do the following:

• Move or copy entire databases. This can be accomplished by detaching the database and moving the files (faster) or by moving the schema and content (slower).

• Transfer error messages from one server to another.

• Move or copy selected or entire SQL Agent jobs.

• Move or copy server-level or database-level logins.

• Move or copy objects such as tables, views, stored procedures, functions, defaults, user-defined data types, partition functions, partition schemas, schemas (or roles), sql assemblies, user-defined aggregates, user-defined types, and xml schemas. These objects can be copied over by selecting all, by individually selecting each desired object types, or even by selecting individual objects themselves.

• Move or copy master stored procedures between two servers.

Transfer Database Task

The Transfer Database task has, as you would expect, a source and destination connection and a database property. The other properties address how the transfer should take place. Figure 6-47 is an example of the Transfer Database task filled out to copy the AdventureWorks database on the same server as a test instance.

Figure 6-47

Notice that the destination and source are set to the same server. For this copy to work, the DestinationDatabaseFiles property has to be set to new mdf and ldf file names. The property is set by default to the SourceDatabaseFiles property. To set the new destination database file names, click on the ellipsis and then change the Destination File or Destination Folder properties.

The Action property controls whether the task should copy or move the Source Database. The Method property controls whether the database should be copied while the source database is kept online, using SQL Server Management Objects (SMO), or by detaching the database, moving the files, and then reattaching the database. The DestinationOverwrite property controls whether the creation of the destination database should be allowed to overwrite. This includes deleting the database in the destination if it is found. This is useful in the case where you want to copy a database from production into a quality-control or production test environment and the new database should replace any existing similar database. The last property is the ReattachSourceDatabase, which allows control over what action should be taken upon failure of the copy. Use this property if you have a package running on a schedule that takes a production database offline to copy it and you need to guarantee that the database goes back online even if the copy fails.

What's really great about the Transfer Database task is that the logins, roles, object permissions, and even the data come along too. This task may in some instances be too big of a hammer. You may find it more advantageous to just transfer specific sets of objects from one database to another. The next four tasks will give you these abilities.

Transfer Logins Task

The Transfer Logins task focuses only on the security aspects of your databases. Have you ever backed up and restored a database or used the SQL 2000 DTS to transfer logins only to find that the SIDs associated with the logins don't match? Now you can transfer the logins from one database and have them corrected at the destination.

Of course, you'll have your obligatory source and destination connection properties in this editor. You also have the choice to move logins from all databases or selected databases, or you can select individual logins to transfer. Make this choice in the LoginsToTransfer property. The default is SelectedLogIns. The partner properties to LoginsToTransfer are the LogInsList and DatabasesList. One will be activated based on your choice of logins to transfer. Figure 6-48 shows an example Transfer LogIns Task Editor with the selection to copy selected logins.

Figure 6-48

Two last properties to cover relate to what you want the transfer logins process to do if it encounters an existing login in the destination. If you want the login to be replaced, set the IfObjectExists property to Overwrite. Other options are to fail the task or to skip that login. The long-awaited option to resolve unmatched user security IDs is found in the property CopySids and can be true or false.

Transfer Master Stored Procedures Task

This task is used to transfer master stored procedures. If you need to transfer your own stored procedure, use the Transfer SQL Server Objects task instead. To use this task, set the source and destination connections, and then set the property TransferAllStoredProcedures to true or false. If you set this property to false, you'll be able to select individual master stored procedures to transfer. The remaining property, IfObjectExists, allows you to select what action should take place if a transferring object exists in the destination. Again the choices are to Overwrite, FailTask, or Skip. Figure 6-49 is an example of a completed Transfer Master Stored Procedures Task Editor.

Figure 6-49

Transfer SQL Server Objects Task

The Transfer SQL Server Objects Task is the most flexible of the Transfer tasks. Within this task lies the ability to transfer all types of database objects. To use this task, set the properties to connect to a source and destination database; if the properties aren't visible, expand the Connection category. As you can see in Figure 6-50, there are many options available in this task. Some may be hidden until categories are expanded.

Figure 6-50

This task exists for those instances when selective object copying is needed. The selectivity is why this is not called the Transfer Database task. You specifically have to set the property CopyData to true to get the bulk transfers of data. The Property CopyAllObjects means that only the tables, views, stored procedures, defaults, rules, and UDFs will be transferred. If you want the table indexes, triggers, primary keys, foreign keys, full-text indexes, or extended properties, you'll have to select these individually. By expanding the ObjectsToCopy category, you'll expose properties that allow individual selections for tables, views, and other programmable objects. The security options give you some of the same abilities as the Transfer Database task. You can transfer Database users, roles, logins, and object-level permissions by selecting true for these properties.

The power lies in the complexity, since this task can be customized and used in packages to move only specific items, for example, during the promotion of objects from one environment to another, or to be less discriminate and copy all tables, views, and other database objects, with or without the data.