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

Updated:   |  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.

  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.

 

 




Did you find this article helpful?

If so, consider buying me a coffee over at Buy Me A Coffee



Comments


Add a Comment


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