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.
- Generate the backup of the AdventureWorks2017 database. This task is going to be performed by the SSIS Backup Database Task
- 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
- 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.
The location of the backup files should be ‘C:\SQLFTPBackups’ so configure the destination of the backup files as shown:
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:
- SourceDirectory: This variable contains the path of the source directory. In this demo, the value of the variable is C:\SQLFTPBackups
- Remote Directory: This variable contains the path of the FTP. In our demo, the value of the variable is /192.168.1.130
- 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.
In the variables screen, click on Add variable Enter the name and value of the variables in Name and value columns.
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.
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.
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.
Now, Click on Variable Mappings. From the Variable list, choose USER::FileFullPath.
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.
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’.
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’.
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:
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.
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:
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.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022