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.txt. The 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.
