Introduction

This Article is for people with experience using T-SQL and SQL Server Management Studio, but without experience in SQL Server Integration Services (SSIS).

In this article, we will introduce you to the SSIS world and then we will show how to combine different SSIS task to create combined backup tasks.

SSIS is a visual tool to Integrate SQL Server with other programs and databases like Oracle, MySQL, Teradata, Excel, MS Access, Visual Basic, C#, Web Services, FTP Sites, etc.

You can combine different tasks to work on complex tasks.

Figure 1a

SSIS is a very powerful tool because you can:

• Work with parameters and variables.
• Work with loops.
• Execute tasks in parallel incrementing the speed to complete the tasks.

SSIS is commonly used to Migrate Data, but it rarely used to backup databases. This is a tutorial to start using SSIS for these administrative tasks.

Getting started

1. In order to start, open the SQL Server Data Tools (SSDT)

   
   Figure 1

2. Go to File>New Project

   
   Figure 2

3. Select the Integration Services Project

   
   Figure 3

4. Double click on the Check Database Integrity task. This task verifies the integrity of the logical and physical Database objects. You can run this task before the backup.

   
   Figure 4

5. Double click on the task and press the New button to create a New connection

   
   Figure 5

6. Specify any name for the connection, the name of the SQL Server and select an authentication method (Windows Authentication is strongly recommended for security reasons) and press OK.

   
   Figure 6

7. Click in the Databases Combobox and select the Database(s) to backup. As you can see, you can check the integrity of several databases with one single task and backup system databases.

   
   Figure 7

8. If you press the View T-SQL, you will have the chance to see the T-SQL command equivalent to the Task action

   
   Figure 8

9. The tasks executes the DBCC CHECKDB statement

   
   Figure 9

10. Double click the Execute T-SQL Statement Task and add the tasks with a constraint

    
    Figure 10

11. Double click the Execute T-SQL Statement Task and add the following T-SQL Statement
    1 2 3 4 5 6

    USE [master] GO ALTER DATABASE [AdventureWorks2012] SET RECOVERY FULL WITH NO_WAIT GO

    We are just making sure that the Database Logs are in full mode to recover databases at any point in time. Ideally, it should be set before.

    The main purpose of this is to show that we can execute T-SQL tasks with SSIS and combine with other tasks

    
    Figure 11

12. Double click the Backup Database Task and join it with the other tasks using a constraint

    
    Figure 12

13. Double click the Back Up Database Task use the current connection, select the Full Backup type and a Database to backup

    
    Figure 13

14. You can backup individual files and files groups, you can also use the copy only option which allows you to backup the database without changing the Database and without affecting regular backup procedures. We can also specify expiration dates for the backups and Append or Overwrite the backup if it already exists.

    
    Figure 14

15. You can also verify the backup integrity and compress the backup

    
    Figure 15

16. Finally, double click on the Maintenance Cleanup Task and you can clean all databases

    
    Figure 16

17. With the Maintenance Cleanup Task, we can delete older backups. It is a good practice if there are obsolete backup that need to be removed. You can remove Backup files and Maintenance Plan text reports. You can also specify the file name or search folders with a specific extension in a specific folder. You can also search backup files in subfolders and specify which folders to delete according to their age.

    
    Figure 17

18. You can optionally press the view T-SQL button and see the command line used

    
    Figure 18

19. Once done, Start the package and it will run all the tasks created. If everything is OK all the tasks will be on green status.

    
    Figure 19

20. You can verify if the backup was created to verify that everything worked fine

    
    Figure 20

Other Tasks

There are many tasks that you can use to improve your SSIS projects. For example, you can use the Web Service Task to call Web Services in SSIS projects. Another powerful task is the Script Task. You can program in Visual Basic or C# complex or customized tasks not allowed by other Tasks. The Data Flow is the most popular and frequently used task to import or Export data to different data sources like Oracle, MySQL, DB2, MS Excel, Posgresql and most of the databases with the help of the Database Drivers. There are also tasks to send emails, upload files to an FTP Server, automate tasks in SQL Server Analysis Services, copy files, run the command shell (CMD), Work with XML files, etc.

Free extra tasks

There are also additional free SSIS components that you can download from the codeplex site.

There are task to upload files to the Amazon S3, Connect to Microsoft CRM Dynamics, FTPS, SCP, SSH, SharePoint and more.

How to schedule SSIS packages

You can schedule your SSIS packages using the SQL Server Agent. That way you can run the packages regularly according to your needs.

For more information about scheduling SSIS packages, review this link:

SQL Server Agent Jobs for Packages

SSIS for developers

If you do not like visual tasks and you love .NET, you can also program SSIS using C#, visual basic or other languages supported by the .NET Framework.

For more information about the Integration Services Development, please refer to this link:

Integration Services Developer Documentation

Create your own custom SSIS task

Sometimes you need to create your custom task and add it to the SSDT to use it frequently. It is possible to create your own task and add it to the ones included by default.

For more information about creating your own custom SSIS task, please refer to this link:

Developing a Custom Task

Conclusions

As you can see, SSIS is a very complete and powerful tool to help you to Administer and Maintain your Database.