Deploying SSIS Packages using DTUtil.exe with PowerShell – Part 1…

This is a three part series:

Part 1. I’m showing my initial script to deploy an SSIS package. 

Part 2. This script will continue to evolve to a PowerShell function.

Part 3. I will demo the integration of this function into your Visual Studio environment.

Part – 1

Well, this is a good example that sometimes you have to add extra code in other to use PowerShell to execute a DOS command.  And YES! I said “execute” a DOS command.  The reality is, that DOS will stay around for awhile and there’s nothing we can do about it. 

So, let me provide some pointers on how to use PowerShell to Deploy SSIS packages:

1. Make sure to identify the full path to run DTUtil.exe because you may have both SQL Server 2005 and 2008 on the same box:

a.  For SQL Server 2005 – “C:\’Program Files’\’Microsoft SQL Server’\90\DTS\Binn\dtutil.exe”

b. For SQL Server 2008 – “C:\’Program Files’\’Microsoft SQL Server’\100\DTS\Binn\dtutil.exe”

2. If you don’t identify the properly the DTUtil.exe then it will pick the first on from the system path:

Example of DOS environment PATH setting: (highlighted in yellow the SQL 2k5 & 2k8 Binn path)

3. If is needed, remember to create the SSIS folder where your package is going to be deployed.  In this sample, my SSIS package location is the default MSDB folder.

*DTUtil.exe , basic form:

sample dtutil /FILE c:\sourcepkg.dtsx /DestServer <servername> /COPY SQL;destpkgname or dtutil /FILE c:\sourcepkg.dtsx /DestServer <servername> /COPY SQL;destlocation/destpkgname

 

*Note: For more information click this link “DTUtil Utility”: http://msdn.microsoft.com/en-us/library/ms162820.aspx

Implementing DTUtil to run from PowerShell

Without going into too much detail, it was tricky for me to put this together.  But, here’s the results.  Using my PrimalScript 2009 editor I end up creating three script files:

1. Two DOS batch files: SSIS_Deploy_PckgUtil_2K5.bat and SSIS_Deploy_PckgUtil_2K8.bat – Each of these batch file will deploy your SSIS package to an specific SQL Server version.

2. One PowerShell Script file “SSIS_Deploy_PckgUtil.ps1” – This script will run from the console prompting you for three information:

a. Package Source location with the filename.

b. SSIS SQL Servername where the SSIS package with be stored.

c. SSIS Package name you want to have it stored.

In all three files, I added a little extra code in order to display more information while executing.  Using the PowerShell script I can enter all the information required to run my DTUtil to deploy my SSIS package to any of my Integration Services SQL Server.

$pick = Show-MsgBox "Enter Yes if you deploying to SQLServer SSIS 2008:" "Deploying to SSIS" "Y"

$SrcFile = Read-Host "Enter your SSIS package fullpath w/Name: C:\Temp\Sample.dtsx"  ##- This is both the Source folder and SSIS package.
$DestSrv = Read-Host "Enter your SSIS SQLServer Name:"  ## - Computer with SSIS installed.
#$DestLoc = Read-Host "Enter you SSIS folder to store the package: "  ## Optional - This is the SSIS package folder.
$Destname = Read-Host "Enter SSIS package name (or new name):" ## - This is a way to rename SSIS packages during deployment.
#$CopyTo = "$DestLoc\$Destname"   ## - FYI -> $Destloc must exist in SSIS.
$CopyTo = $Destname

if ($pick -eq "Yes"){
    # Run DTUtil.exe from the SQL 2008 path - Running DOS batch command
    Get-Date 
    & .\SSIS_Deploy_PckgUtil_2K8.bat $SrcFile $DestSrv $CopyTo
    Get-Date    
} else {
    # Run DTUtil.exe from the SQL 2005 path - Running DOS batch command
    Get-Date 
    & .\SSIS_Deploy_PckgUtil_2K5.bat $SrcFile $DestSrv $CopyTo
    Get-Date        
}

All the batch file will accept the parameters from the PowerShell script.

 

@echo SQL Server 2005
@echo Running SSIS_Deploy_PckgUtil batch process
@echo ------------------------------------------
@echo Param1 = %1
@echo Param2 = %2
@echo Param3 = %3
@echo ------------------------------------------
@echo -
C:\"Program Files"\"Microsoft SQL Server"\90\DTS\Binn\dtutil.exe /Quiet /File %1 /DestServer %2 /copy SQL;%3 
@Echo Process completed!

 

@echo SQL Server 2008
@echo Running SSIS_Deploy_PckgUtil batch process
@echo ------------------------------------------
@echo Param1 = %1
@echo Param2 = %2
@echo Param3 = %3
@echo ------------------------------------------
@echo -
C:\"Program Files"\"Microsoft SQL Server"\100\DTS\Binn\dtutil.exe /Quiet /File %1 /DestServer %2 /copy SQL;%3 
@Echo Process completed!

As you can see in the batch file, in Param3, it can either be the SSIS_Destination_location_name with the package_name (separated with a “\”) or only the package name.

 

Executing the PowerShell Script “SSIS_Deploy_PckgUtil.ps1” from the console:

 

1. Will ask you if you are deploying to SQL Server 2008 SSIS – click on “YES” to continue.

 

 

2. Then, it will prompt to enter the information about the SSIS package to be deploy.

 

 

3. The processing started.

 

 

4. The process completed.

 

Let’s take a look at my SQL Server 2008 R2 Integration Services and we can find our deployed SSIS package.

 

 

 

In this PowerShell Script I intentionally included a custom function named “Show-MsgBox”.  This function is use for the windows message box to display if  this is a SQL Server 2008 SSIS package to be deploy.   This code need to be loaded from your PowerShell profile and I hope you’ll like it.

 

function Show-MsgBox {
<# 
.SYNOPSIS
    Display a Windows Messagebox. 
.Description 
    This function will allow you to prompt both a simple and a Yes/No message box.  This function can be use in both
    PowerShell V1 and V2. Has the alias - smb.
.Parameter TextMsg 
    TextMsg - [String].  This is your text message to be display in the box. (Required)
.Parameter Caption
    Caption - [String].  This is your messagebox Title. (Optional)
.Parameter YesNo
    YesNo - [String].  Use a "Y" to enable Yes/No buttons to show. (Optional)
.Example 
    PS> Show-MsgBox "Hello Everyone" "Test Tile" "Y"
    Using the full function name with all paramaters
.Example
    PS> smb "Hello Everyone" "Test Tile" "Y"
    Using the alias function with all parameters
.Example    
    PS> smb "Wait here!"
    Function in its simple form
.Notes 
    NAME: Show-MsgBox
    Alias: smb    
    AUTHOR: Max Trinidad     
    Created:  04/16/2010 21:16:01 
    Compatibility - Version 1 and Version 2
.Link 
    about_functions 
    about_functions_advanced 
    about_functions_advanced_methods 
    about_functions_advanced_parameters 
.Inputs
    TextMsg - [String] - Required
    Caption - [String] - Optional
    YesNo - [String] Need to be "Y". - Optional
.Outputs
    Return [String] - Only if YesNo Parameter is used.
#> 

Param([string] $TextMsg,[string] $Caption, [string] $YesNo)
    
    [Void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null;
    
    if ($caption -eq $null){ $caption = "msgbox caption" }
    
    if ($YesNo.ToUpper() -eq "Y"){
        $result = [System.Windows.Forms.MessageBox]::Show($textMsg,$caption,[System.Windows.Forms.MessageBoxButtons]::YesNo);
            return $result
    } else {
        $result = [System.Windows.Forms.MessageBox]::Show($TextMsg, $caption) ;
    }
    
}; Set-Alias smb Show-MsgBox

Please, don’t be afraid to try using PowerShell combining with the .NET framework makes it a very powerful scripting tool. 

 

Stay tuned for Part – 2 next week.

 

Happy PowerShelling!!