Microsoft SQL Server - Run a SSIS DTSX package using Windows PowerShell

by
Jeremy Canfield |
Updated: March 12 2020
| Microsoft SQL Server articles
For this purpose of this tutorial, let's say you have created a DTSX file to export data from a table in SQL Server to an Excel file. Running the DTSX file using PowerShell is a very easy way to export the data from the table in SQL Server to an Excel file. Only one line of code is needed to accomplish this.
- Select the Windows Start icon, in search type PowerShell, and select PowerShell ISE
- In the top panel of PowerShell ISE, enter the below code
- Select File > Save as, and save the file as C:\Users\<username>\PowerShell_Scripts\sql_server_ssis.ps1
- Press the green play button in PowerShell ISE
- In Windows Explorer, navigate to C:\Users\<username>\PowerShell_Scripts\ and open the stage.txt file. Ensure the text file contains the export from the SQL table.
- Wait a couple minutes and press the green play button again. Ensure the last modified date/time of the text file contains the time the file was run.
dtexec.exe /f "C:\path\to\example.dtsx"
We can spice this up a little bit creating a PowerShell script which displays some text while the export job is running. Save the following as a PowerShell script, such as example.ps1.
Write-Host "All right, let's get started! I am getting that Excel file for you. . . "
Start-Sleep -s 3
dtexec.exe /f "C:\path\to\example.dtsx"
Start-Sleep -s 3
Write-Host "All set, I've created the Excel file you requested. It's in your Downloads folder. `n`n You can close me now. Please come back again."
We next want to ensure we can run this script using PowerShell (not PowerShell ISE):
- Ensure PowerShell ISE is closed
- Select the Windows Start icon, in search type PowerShell, and select PowerShell
- Type C:\Users\<username>\PowerShell_Scripts\ sql_server_ssis.ps1 and press enter
- In Windows Explorer, navigate to C:\Users\<username>\PowerShell_Scripts\ and ensure the last modified date/time of the text file contains the time the file was run.
Did you find this article helpful?
If so, consider buying me a coffee over at