Nisarg Upadhyay
Control flow window

How to backup SQL databases to an FTP server using the SSIS FTP Task

August 26, 2020 by

In this article, I am going to explain how we can upload the backup file to an FTP Server using the SSIS FTP task. For the demonstration, I have installed SQL Server 2017 on my workstation and restored the AdventureWorks2017 database.

I have configured an FTP site on a virtual machine. The IP Address of the FTP Site is 192.168.0.130, and the hostname is SAN.Dc.Local.SQL Server Data Tool, 2017.

For the demonstration, we are going to upload the backup of the AdventureWorks2017 database to the FTP Site. I have generated a backup of the AdventureWorks2017 database. The backup file is located on the directory. Following is the flow of the SSIS package.

  1. Generate the backup of the AdventureWorks2017 database. This task is going to be performed by the SSIS Backup Database Task
  2. Get the list of the file that is going to be uploaded on the FTP Site. To upload the file, we require a fully qualified name of the file. To do that, we are going to use SSIS Foreach Loop Container. If you want to learn about Foreach Loop Container, you can read SSIS Foreach Loop vs For Loop Container article
  3. Upload the backup file on FTP Site. To do that, we are going to use the SSIS FTP Task. If you want to learn about the SSIS FTP task, you can read An overview of the SSIS FTP Task article

First, let us create an SSIS package. To create an SSIS Package, Launch SQL Server Data Tools, and create a new SQL Server Integration Services Project.

Create a Back Up Database Task

First, let us configure the backup database task. To do that, drag and drop the Back Up Database Task in the Control Flow window.

Control flow window

The location of the backup files should be ‘C:\SQLFTPBackups’ so configure the destination of the backup files as shown:

Back Up Database Task

Create variables to store the values of FTP Local Source and FTP Remote destination

Once the Backup task is configured, we will configure three variables. The details are the following:

  1. SourceDirectory: This variable contains the path of the source directory. In this demo, the value of the variable is C:\SQLFTPBackups
  2. Remote Directory: This variable contains the path of the FTP. In our demo, the value of the variable is /192.168.1.130
  3. FileFullPath: This variable will be used in the Foreach Loop Container. The location of the files within the source directory. In this demo, we are not going to specify the value for this variable

To create a variable, On the main menu, click on SSIS Click on Variables.

Variables

In the variables screen, click on Add variable Enter the name and value of the variables in Name and value columns.

Add variable

Once variables are configured, let us configure the Foreach loop container.

Configure Foreach Loop Container

To configure the Foreach Loop Container, drag and drop it from the SSIS toolbox. Double-click on Foreach Loop Container to configure it.

Foreach loop container

In the Foreach Loop editor dialog box, click on the collection. We are going to upload the backup files on FTP, so choose the ‘Foreach File emulator’ from Enumerator drop-down box.

Configure Foreach Loop Editor

Enter the location of the Source Directory in the textbox and provide the file extension in the textbox. To upload the files, we must use the Fully qualified name of the files; therefore, select the Fully Qualified option.

Configure Enumerator

Now, Click on Variable Mappings. From the Variable list, choose USER::FileFullPath.

Perform Variable mapping

Once the mapping is completed, click OK to save the configuration and close the dialog box.

Configure SSIS FTP Task

Now, drag the SSIS FTP task from the toolbox to drop it inside the Foreach Loop Container.

Add FTP task in Control Flow Window

We are going to use the variables to specify the values of the Source and Destination path. To do that, double-click on the SSIS FTP Task. In the FTP Task Editor dialog box, click on the File Transfer option. Set the value of ‘IsLocalPathVariable’ as True. In the ‘LocalVariable’ drop-down box, select the name of the variable. In our demo, it is ‘USER::SourceDirectory’.

Local variable in Ftp Task Editor

Similarly, set the value of the ‘IsRemotePathVariable’ as True. In ‘RemotePath’ drop-down box, select the name of the variable. In our demo, it is ‘USER::RemoteDirectory’.

Remote variable in FTP task editor

Once FTP Task is configured, click on OK to save the configuration and close the dialog box.

Test the SSIS Package

Once the package has been created, it looks like as shown in the following image:

SSIS Package

Click Execute to run the package. If the SSIS package executed successfully, it must generate the backup file of the AdventureWorks database and upload it to the FTP Server. First, Open the ‘C:\SQLFTPBackups’ directory.

Backup file on Windows explorer

As you can see, the backup file has been created. Now open FTP destination by opening the URL.

ftp://san or ftp://192.168.0.130

See the following image:

File on FTP Site

The backup file has been uploaded to the FTP destination.

Summary

In this article, we have learned how we can use the SSIS FTP Task to upload the backup file of the SQL Server database on the FTP server.

Nisarg Upadhyay
168 Views