Floating FB popout byF5debug

SQL Server Integration Services (SSIS) – Part 13 – Backup Database task in SSIS and Send Mail

 

This is part 13 of the series of article on SSIS

Introduction

In this article we are going to see on how to use a Backup Database task to create an execution plan in SSIS to take a database backup. This task will be very helpful for the DBA’s to maintain the database automatically by creating the package and using it across the servers. This task has many options to do a Full, Differential backups based on our requirement we can go ahead and use the respective option. Let’s jump into the steps on how to create and use the backup database task

You can look into my series of article on SSIS at the url - http://f5debug.net/tutorial/ssis.aspx

[more]

Steps

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see on how to use Backup Database task options available with SSIS.

Once we are into the project drag and drop the Backup Database Task from the tool box as shown in the below figure

clip_image001

Now drag and drop the Send Mail task, so here our task is to do a backup and send a mail to the DBA that the backup has be done successfully. Once you drag and drop the Send mail task then you project will look like below

clip_image002

Now let’s step into the configuration section of the Backup Database task and do the configuration for a Full backup database. Just double click on the backup database task it will open a window as shown in the below screen. We need to do the configuration as shown below. It’s self-explanatory on the options available

clip_image003

Clicking on the View T-SQL button will open a popup as shown in the below screen

clip_image004

Now configure the Send mail task (Check my previous link on how to configure Send Mail task in SSIS).  Now press F5 to execute the package to make a Full database backup. Once everything is ready and executed your screen will look like below.

clip_image005

Send Mail task shows red color which means there is an error in the task. It’s a known error, since it’s executed in my local system SMTP is not configured.

Conclusion

So in this article we have seen how to back up a database using SSIS package and send a mail to the DBA on success.

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Comments (3) -

sahana
sahana
8/10/2012 12:36:42 AM #

Simply wonderful.Hats off to your tutorial.
Love all your posts and the simplicity in them.
Deployment and Error Resolution can be added to this series.A personal request.Thanks for this.

sahana
sahana
8/10/2012 12:41:13 AM #

Deployment through Package on SQL server that is .Thanks again

yujkty
yujkty
7/3/2013 5:17:24 AM #

fdjntgfjmn fgth, mcu

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Random Posts

Most Viewed

URL Shortner

Create your own short urls!!!

Site Counter

free counters

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter