SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to download a file from internet using SSIS

By Daniel Calbimonte,

Introduction

In this article, we will show how to download files from internet using SQL Server Integration Services (SSIS). How to use parameters in PowerShell and integrate SSIS parameters with PowerShell parameters.

Requirements

1. A Machine with Windows OS and Powershell installed.

2. SSIS and SQL Server Data Tools (SSDT).

Getting Started

In this example, I will use the text files from www.textfiles.com/adventure, but you can use any file in internet:

We will integrate SSIS with PowerShell to accomplish our goal. You could use notepad to create a PowerShell script, but I strongy recommed you to use PowerShell Integrated Scripting Environmente (PowerShell ISE).

This application allows you to easily create, edit and save PowerShell Scripts. It is included in the Windows OS and it is very simple:

Add the following code in the script (you can download the scripts at the bottom of the article in the resource files section):

Start-BitsTransfer http://www.textfiles.com/adventure/221baker.txt C:\adb\221baker.txt

Start-BitsTransfer PowerShell cmdlet will download the file named 221baker.txt from the www.textiles.com/adventure web site and downloads the file in the sql folder in the c:\ drive.

Save the script and name it downloadfile.ps1:

To invoke the PowerShell script created, we will use the Execute Process Task. This is included in SSDT in the SSIS projects. Drag and drop this task to the design pane:

Double click the Execute Process Task and add the following properties in the Process page:

Executable is the program that we are invoking in SSIS. We are invoking Powershell.

Arguments will send the command to invoke the PowerShell script created before.

Working Directory is the file where the script is stored.

Press Start to run the Task:

A PowerShell window will be displayed with the BITS transference:

If everything is OK, the 221baker.txt file will be downloaded in the c:\sql folder:

Running PowerShell with Parameters

That is fine, but is there a way to specify the file name as a parameter? Yes, we will create a new script named downloadfileParams.ps1 with the following content (or you can download the file from resource files):

param() is used to specify 1 or multiple parameters. In this example, the only parameter is $filename. If the parameter value is not specified, the default value is 221baker.txtThe Start-BitsTransfer now uses the parameter to get the file name dynamically.

Once that the script is saved, in SSDT>SSIS, use the following parameters in the Execute Process>Process Page:

The only thing that we changed is the arguments:

-file downloadfileParam.ps1 "aland.txt"

Where downloadfileParam.ps1 is the PowerShell script and "aland.txt" is the name of the file that we want to download.

Use SSIS parameters or variables in PowerShell

We will show how to send SSIS Parameter values to a PowerShell script. We will create a SSIS parameter named filename and specify the txt file name using that Parameter to download it. Go to the Parameter Tab and create a new parameter named filename of type string and specify an existing txt file name:

Return to the Control Data Flow Tab and click Execute Process Task. Go to Expressions page and select Arguments properties to create an expression:

In Arguments, specify the following arguments:

"-file downloadfileParam.ps1"+" "+ @[$Package::filename]

The expression will invoke the PowerShell script named downloadfileParam.ps1 and the parameter value is obtained from the filename SSIS parameter value. 

Conclusions

In this example, we show how to download a file from a HTTP web site to a local folder using PowerShell and SSIS. It is also possible to download the file using the script task and adding C# code or VB code, but this option is easier because you require less lines of code.

We also learned how to use parameters in PowerShell and how to integrate SSIS Parameters with PowerShell. 

References

 

Resources:

downloadfile.ps1 | downloadfileParam.ps1
Total article views: 2175 | Views in the last 30 days: 13
 
Related Articles
ARTICLE

How to create Policies using PowerShell scripts

This article explains how to create policies with user defined conditions in MS SQL 2008 using Powe...

SCRIPT

Scripting SQL jobs using PowerShell

Using powershell to create different sql scripts in different files.

BLOG

PowerShell: Extending Modules – System Parameter measurement

The Microsoft Technet Guru Award  (August 2016) winning article  – Silver PowerShell: Extending Mod...

ARTICLE

Paging Doctor Powershell

Use Powershell to create a single script that checks over your server and every SQL instance on it t...

BLOG

Creating Red Gate SQL Compare Snapshots with Powershell

Co-worker and fellow DBA,, John O'Shea developed a Powershell script to create Red Gate SQL Compare ...

Tags
download    
file    
integration services (ssis)    
internet    
 
Contribute