How to run a SQL Server SSIS DTSX package using Windows PowerShell

Home > Search > How-to
  by

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.

  1. Select the Windows Start icon, in search type PowerShell, and select PowerShell ISE
  2. In the top panel of PowerShell ISE, enter the below code
  3. Select File > Save as, and save the file as C:\Users\<username>\PowerShell_Scripts\sql_server_ssis.ps1
  4. Press the green play button in PowerShell ISE
  5. 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.
  6. 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):

  1. Ensure PowerShell ISE is closed
  2. Select the Windows Start icon, in search type PowerShell, and select PowerShell
  3. Type C:\Users\<username>\PowerShell_Scripts\ sql_server_ssis.ps1 and press enter
  4. 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.

 

 



Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.




Please enter in the box below so that we can be sure you are a human.




Comments