In this article, we will be using FTP task to download an .xls file and then use that file to upload it in SQL Server using Data Flow Task base. So let's create a new package and name our package as DownloadFile.
- Open BIDS; create a new Project by selecting Integration Service Project.
- Then we create the flow and below is how the workflow would look like.
We will now start configuring out FTP Task. Right click on FTP Task and click on EDIT and create new FTP Connection.
Here Server Name(or IP address of the server) from which files are to be downloaded is required and Username and password to connect to the server. Click on Test Connection to make sure connection is established.
- The second option is File Transfer and we will specify the operation and path as below. In here Remote path is location of FTP server from where you would download the files. Click Ok and FTP Task is configured.
Now we move on to Data Flow Task to upload the downloaded file to SQL Server Database. Here we will be using Excel Source as the downloaded file is in *.xls format and SQL Server Destination.
- Let's see how we can configure these source and destination. First, select the Excel Source, Right click and click Edit. We will be creating the new connection here. To see how we can create a new connection refer to my article here .(Since while doing the configuration if the file is not present at local location, then you can create an excel file having same name that will be downloaded from FTP site)
We then configure SQL Server Destination in same way by right clicking and select Edit, a window will open, click on new. A window will open with list of Data connections on the left. If the connection to Database that you require is not present then click on new and create the connection. (in my case below the connection is present so I am selecting that one). Click Ok
Under Use as table or view, select the table in which you want to insert the data. If the table you want to insert is not present you can click on new and create a table, in my case since table is not present I will be creating one as below
Last we want to get notified if the task completed successfully or it failed so we will configure Send Mail task for both operation. How to configure send mail task is elaborated here
Run the complete package and all tasks should turn green to show success f every task.
Note: We are using Send Mail task here to get the notification in case we decide to run the package through Job scheduler and get notification when job has run with success.