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!!
 
 
 
 
 
 
 
 
 
 
 

About these ads

About maxt2posh

I’m from Puerto Rico, have been working with computers since 1979. Throughout many years, I have worked with SQL Server Technologies, provided support to Windows Servers/Client machines, Microsoft Virtualization Technologies, and build some Visual Studio solutions. I’m a Microsoft PowerShell MVP since 2009 and MVP SAPIEN Technologies since 2010. I speak in most of the SQLSaturday, IT Pro and .NET camps events around the Florida’s State. Also founder of the Florida PowerShell User Group which meeting every 3rd Tuesday evening of the month.
This entry was posted in Uncategorized. Bookmark the permalink.

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

  1. dcostelloe says:

    Sorry don’t see the link to download sample files?

  2. Anonymous says:

    I wrote this powershell script to install ssis packages automatically

    I pasted this into the email so that the mail system doesn’t reject the ps1 file extension.
    Place the script in a file and give it a .ps1 extension.

    If finds all *.dtsx files in the folder where the script is located
    It backs up any package that may be overwritten
    It Rolls back everything in the backup folder if any package fails.

    I unit tested it. But be careful there may be a bug or two

    #get the location of DTUTIL
    $DTSPath = (get-itemproperty -path “HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS\Setup\DTSPath”).'(default)’
    #get the names of the packages in the current windows folder
    $PackageNames = Get-ChildItem -Name -Filter “*.dtsx”
    #The SSIS or Filesystem folder where the packages will be installed
    $InstallTargetFolder = “”
    #The SSIS or Filesystem folder where the existing packages will be backed up
    $BackupFolder = “MattBKUP\”

    function RUNINSTALL()
    {
    cls
    Write-Host “.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^.^”
    foreach ($name in $PackageNames )
    {
    InstallPackage $name.TrimEnd().ToString()
    Write-Host “………………………………………………………….”
    }
    Write-Host ” ”
    Write-Host ” ”
    Write-Host “……………….DONE”

    }

    #copy all packages in the BackupFolder to the InstallFilder and delete the Backup Folder
    #get the list of files in the $BackupFolder
    #move them to the $InstallTargetFolder
    function RollbackPackages {
    Write-Host “ROLLING BACK…………………..”
    foreach ($name in $PackageNames )
    {
    Write-Host “ROLLING BACK PACKAGE ” + $Name
    [String]$From = $BackupFolder + $Name.Replace(“.dtsx”,””)
    [String]$To = “SQL;” + $InstallTargetFolder + $Name.Replace(“.dtsx”,””)
    Write-Host “Restoring PACKAGE From:” + $From + ” TO:” + $To
    [String]$Status = dtutil /SQL $From /MOVE $To /quiet

    }
    }

    #Move the Package $Name From $InstallTargetFolder to $BackupFolder
    function BackupPackage { param( [String]$Name, [String]$Status )
    [String]$From = $InstallTargetFolder + $Name.Replace(“.dtsx”,””)
    [String]$To = “SQL;” + $BackupFolder + $Name.Replace(“.dtsx”,””).ToString()
    [String]$ToPath = “SQL;” + $BackupFolder
    #package exists?
    [String]$PackageExists = dtutil /SQL $From /Ex /quiet
    if($PackageExists.Contains(“The specified package exists”) )
    {
    #backup folder exists?
    [String]$FolderExists = dtutil /Fe $ToPath /quiet
    if($FolderExists.Contains(“The specified folder does not exist”) )
    {
    [String]$CreatePath = “SQL;\;” + $BackupFolder.TrimEnd(‘\’)
    Write-Host “Creating Backup folder:” $CreatePath
    #/FC[reate] {SQL | DTS};ParentFolderPath;NewFolderName
    [String]$Status = dtutil /FC $CreatePath /quiet
    }
    Write-Host “BACKING UP PACKAGE From:” $From ” TO:” $To
    [String]$Status = dtutil /SQL $From /COPY $To /quiet
    }
    else
    {Write-Host “Backup not required for:” $From}
    }

    function ValidateInstall {
    param([String]$Name,[String]$Status)

    if ( $LASTEXITCODE -eq 0)
    {
    Write-Host “Package Deployment Success ” $Name
    }
    else
    {
    if
    ( $LASTEXITCODE -eq 1) {Write-Host “Package Deployment Failed ” + $Name + ” …*** ERROR *** The utility failed. Error code 1 ” + $status }
    elseif
    ($LASTEXITCODE -eq 4) {Write-Host “Package Deployment Failed ” + $Name + ” …*** ERROR *** The utility cannot locate the requested package. Error code 4 ” + $status }
    elseif
    ($LASTEXITCODE -gt 5) {Write-Host “Package Deployment Failed ” + $Name + ” …*** ERROR *** The utility cannot load the requested package. Error code 5 ” + $status }
    elseif
    ($LASTEXITCODE -gt 6) {Write-Host “Package Deployment Failed ” + $Name + ” …*** ERROR *** The utility cannot resolve the command line because it contains either syntactic or semantic errors. Error code 6 ” + $status }
    else
    {“Package Deployment Failed ” + $Name + ” …*** ERROR *** Unidentified Error. ” + $status }

    RollbackPackages
    Write-Host ” ”
    Write-Host “ROLLBACK COMPLETE”
    Write-Host ” ”
    Read-Host “Press enter to continue …”

    Exit
    }
    }

    function InstallPackage { param( [String]$Name, [String]$Status )
    backuppackage $Name $Status
    [String]$To = “SQL;” + $InstallTargetFolder + $Name.Replace(“.dtsx”,””)
    $Status = dtutil /FILE $Name /COPY $To /quiet
    ValidateInstall $Name $Status
    }

    RUNINSTALL

  3. Anonymous says:

    Not working for me,Getting the error:
    You cannot call a method on a null-valued expression.
    At line:16 char:29
    + InstallPackage $name.TrimEnd <<<< ().ToString()
    + CategoryInfo : InvalidOperation: (TrimEnd:String) [], RuntimeEx
    ception
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Can you please help me?

  4. Hey just wanted to give you a brief heads up and let you know a few of the pictures aren’t loading correctly. I’m not sure why but I think its a linking issue.
    I’ve tried it in two different internet browsers and both show the same outcome.

  5. kiquenet says:

    What about errors codes executing dtutil.exe in powershell ?

  6. Simply desire to say your article is as amazing. The clearness in your post is just nice and i could assume you are an expert on this subject.

    Well with your permission let me to grab your feed to keep updated with forthcoming post.
    Thanks a million and please continue the enjoyable work.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s