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.
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.
In order to start, open the SQL Server Data Tools (SSDT)
Go to File>New Project
Select the Integration Services Project
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.
Double click on the task and press the New button to create a New connection
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.
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.
If you press the View T-SQL, you will have the chance to see the T-SQL command equivalent to the Task action
The tasks executes the DBCC CHECKDB statement
Double click the Execute T-SQL Statement Task and add the tasks with a constraint
- Double click the Execute T-SQL Statement Task and add the following T-SQL StatementWe 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.123456USE [master]GOALTER DATABASE [AdventureWorks2012] SET RECOVERY FULL WITH NO_WAITGO
The main purpose of this is to show that we can execute T-SQL tasks with SSIS and combine with other tasks
Double click the Backup Database Task and join it with the other tasks using a constraint
Double click the Back Up Database Task use the current connection, select the Full Backup type and a Database to backup
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.
You can also verify the backup integrity and compress the backup
Finally, double click on the Maintenance Cleanup Task and you can clean all databases
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.
You can optionally press the view T-SQL button and see the command line used
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.
You can verify if the backup was created to verify that everything worked fine
Other TasksThere 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:
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:
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:
As you can see, SSIS is a very complete and powerful tool to help you to Administer and Maintain your Database.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte