DBA Tasks in SSIS 2005
Data base Administrations can create data base maintenance plans either by using Maintenance plan wizard or by using SSIS designer. By using the Maintenance plan wizard a very basic maintenance plan can be created for acc the system and user data bases. However, to create an enhanced work flow it is advised to create maintenance plan using SSIS designer
Back up Data base Task:
Back up data base task different types of data bases backups to be performed; such as full, differential or Transactional log backups based on the Recovery Model of the system or User databases. To learn more about the different recovery models in SQL Server refer to the article titled “Database Recovery Models in SQL server”.
Check Data base Integrity Task:
Check Database Integrity Task can be used to check the allocation and structural integrity of all the user and system tables within a database. This task also has an option to check the allocation and structural integrity of all the indexes available within a database. The task internally executes DBCC CHECK DB Statement.
Execute SQL Server Agent Job Task:
Execute SQL server Agent Job Task can be used to run SQL Server Agent Jobs that are created on the SQL server Instance.
Interested in mastering MSBI Training? Enroll now for FREE demo on MSBI Training.
Execute T-SQL statement Task:
Execute T-SQL statement Task can be used to execute some Transact SQL queries against databases . This task is only available when creating Maintenance plans using SSIS designer.
Transfer Database Task:
The Transfer Database Task is used to move a database to another SQL Server instance or create a copy on the same instance (with different data base name). This task works in two modes as show below:
In this mode, the source database is detached from the source server after putting it in single network location. On the destination server the copies are taken from the network location to the destination server and then finally both data bases are attached on the source and destination servers. This mode is faster, but a disadvantage with mode is that the source database will not available during copy and move operation. Also the person executing the package with this mode must be sys admin on both source and destination instances.
In this mode, the task uses SMO to transfer the database objects to the destination server. In this mode, the database is online during the copy and move operation, but it will take longer as it has to copy each object from the data base individually. Some one executing the package with this mode must be either sys admin or database owner of the specified databases.
Transfer Database Task:
Drag the Transfer “Database Task” from the Tool box (which is normally on the left side) to the control Flow pane. Right click on the task and select Edit… as shown below.
In the “Transfer Database Task Editor, “ select Databases on the left and now you are ready to configure this task. Source connection is the property to specify the connection for the source SQL server instance, if you have already created a connection manager then one as shown below. This will also need to be done to configure the Destination connection property as well.
Next you need to specify the values.
Source connection --- the source instance
Destination connection --- the destination instance
Learn more about MSBI Interview Questions in this blog post.
Destination Data base:
Destination Database Name --- name of the new database
Destination Database Files ---Name and location of the data base file
Destination Over write ---If the database already exists on the destination server it will give you an error, so if you want to overwrite the destination database you can set this property to true.
Source Data base:
Action - Whether you want to copy or move a database.
Method - Whether you want the copy and move operation to be offline (in this case you also need to provide the network share name which will be used to transmit the database files).
Source Data base Name - name of the source data base
Source Data base Files - name and location of the database files
Reattach source Database -is another property which you can set to TRUE to Reattach the source database after the copy operation.
Once you are done with all these configurations you can hit F5 or click on the play icon to execute the package. Your task will turn yellow during the execution and then either red or green depending on the execution out come. You can go to progress tab to see error messages if the execution fails. Although failure is rere, it is possible if your source data base is smaller it is possible if your source database is smaller than the size of the model database on the destination server.
For an Indepth knowledge on MSBI click on: