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

In Part 1, I created both a batch file and a PowerShell script that would allow me to pass parameters to the SQL Server dtutil.exe command use to manage SQL Server Integration Services packages. This example is meant to show how to work with parameters and pass values to a command Batch file. And, I know, we are using two files that need to be in the same folder in order to run without any problems.

Now, in Part 2, this script has evolved from a script file to a three separate functions. We got rid of the batch file.  I’m going to auto0generatge the batch file from the within the functions and then execute the batch file. This batch file will be create in the “C:\Users\YourName\Documents\WindowsPowerShell” folder and it will be overwritten every time a function is executed.  Each function has a help documentation using the cmdlet: Get-help or help FunctionName –full

But, what was learned in part two:

1. Evolving from a PowerShell script to an advance function with required parameters

2. Build a string variable with your batch command lines with area you can replace string values.

3. Add the help comments in the functions.

 

Here’s the three new functions for the copying SSIS packages: (below is the full source code)

1. Deploy-SQL2DTS

2. Deploy-File2SQL

3. Deploy-File2File

Requirements for these functions:

1. You need to have your PowerShell User profile folder in place.

2. You can three functions in your Microsoft.PowerShell_profile.ps1 script file for quick execution.

3. If you don’t want to load all functions to the profile then use the Dot-Source way to load the function separately:

example:  PS> . .\Deploy-SQL2DTS.sp1

4. In cases the “Program files” folder reside on a different drive letter, then change the directory path in the batch file string inside all three functions.

5. The auto-generated batch file will be created in your Users WindowsPowerShell folder.  But, if you want to change this, look for the variable name $DefaultBatchBuildLocation to change the location to store the batch file.

Here’s all three functions code:

1. Deploy-SQL2DTS.ps1 – Copy SQL MSDB package to another folder under MSDB folder.

 
# ==============================================================================================
# Microsoft PowerShell Source File -- Created with SAPIEN Technologies PrimalScript 2009
# NAME: Deploy-SQL2DTS.ps1
# AUTHOR: Max Trinidad,
# DATE : 4/24/2010
#
# COMMENT: This is function will copy a SQL package to another folder in SQL Integration Services msdb.
#
# ==============================================================================================

function Deploy-SQL2DTS {
<#
.SYNOPSIS
Deploy SSIS package, using the "/Copy" switch, to a SQL Server Integration Services store.
.Description
This function will allow you to deploy a copy SSIS package in SQL MSDB to another folder in MSDB SQL Server Integration Services.
All the parameters are require to deploy the package (except for "is2k8"). This function can be use in both PowerShell V1 and V2. Has the alias dsd
.Parameter is2K8
is2K8 - [String]. This is to identify is the package is going to a SQL Server 2008 Integration Services store. (Required)
Enter a "y" for SQL2k8 and "n" for SQL2k5.
.Parameter SrcFile
SrcFile - [String]. This is your SSIS package store in MSDB. (Required)
.Parameter CopyTo
CopyTo - [String]. This is the SQL Server destination name include in MSDB\foldername. (Required)
.Example
PS> Deploy-SQL2DTS Y Sample_SSISpkg01 MSDB\BACKUPSSIS\Sample_SSISpkg01
Using the full function name with all parameters
.Example
PS> dsd Y Sample_SSISpkg01 MSDB\BACKUPSSIS\Sample_SSISpkg01
Using the alias function with all parameters
.Example
PS> dsd c:\temp\Sample_SSISpkg01.dtsx Sample_SSISpkg01_copy
Using the full function name but excluding the first parameters because will deploy to SQL Server 2005 Integration Services
.Notes
NAME: Deploy-SQL2DTS
Alias: dsd
AUTHOR: Max Trinidad
Created: 04/16/2010 21:16:01 - Version 0.1
Compatibility - Version 1 and Version 2
.Link
about_functions
about_functions_advanced
about_functions_advanced_methods
about_functions_advanced_parameters
.Inputs
is2K8 - [String] - Required
SrcFile - [String] - Required
CopyTo - [String] - Required
#>
Param(
[Parameter(Mandatory=$true, Position=0)] [string] $is2K8,
[Parameter(Mandatory=$true, Position=1)] [string] $SrcFile,
[Parameter(Mandatory=$true, Position=2)] [string] $CopyTo
)

#Initializing variables
$SQLver = "999"
$SQLVName = "SQLServerDesc"

#Location where the create batch file will be build.
$DefaultBatchBuildLocation = $Env:UserProfile + "\My Documents\WindowsPowerShell"

#This is the string use to create the batch file.
$Batch = @"
@echo SQLServerDesc
@echo Running SSIS Deploy PckgUtil batch process
@echo ------------------------------------------
@echo Param1 = %1
@echo Param2 = %2
@echo ------------------------------------------
@echo -
C:\"Program Files"
\"Microsoft SQL Server"\999\DTS\Binn\dtutil.exe /Quiet /SQL %1 /copy DTS;%2
@Echo Process completed!
"@

#Write-Host $is2K8 $SrcFile $CopyTo

if ($is2K8.ToString() -eq "
Y"){

# Run DTUtil.exe from the SQL 2008 path - Running DOS batch command
$SQLver = "
100"; $SQLVName = "SQL Server 2008"
Write-Host "
Building SQL2k8 Deploy Batch file in $DefaultBatchBuildLocation"

# Display the datetime before execution
Get-Date

#Building batch file step
$Batch = $Batch.replace("
SQLServerDesc",$SQLVName)
$Batch = $Batch.replace("
999",$SQLver)
$Batch | Out-File -FilePath $DefaultBatchBuildLocation\Deploy-SSISPkgUtil.bat -Encoding ASCII

#Execute created batchfile
& $DefaultBatchBuildLocation\Deploy-SSISPkgUtil.bat $SrcFile $CopyTo

# Display the datetime after execution
Get-Date

} else {
# Run DTUtil.exe from the SQL 2005 path - Running DOS batch command
$SQLVer = "
90"; $SQLVName = "SQL Server 2005"
Write-Host "
Building SQL2k5 Deploy Batch file in $DefaultBatchBuildLocation"

# Display the datetime before execution
Get-Date

#Building batch file step
$Batch = $Batch.replace("
SQLServerDesc",$SQLVName)
$Batch = $Batch.replace("
999",$SQLver)
$Batch | Out-File -FilePath $DefaultBatchBuildLocation\Deploy-SSISPkgUtil.bat -Encoding ASCII

#Execute created batchfile
& $DefaultBatchBuildLocation\Deploy-SSISPkgUtil.bat $SrcFile $CopyTo

# Display the datetime after execution
Get-Date
}

}; Set-Alias dsd Deploy-SQL2DTS

2. Deploy-File2SQL.ps1 – Copy a SSIS package store in the filesystem to SQL MSDB folder.  Reminder, you can’t deploy an SSIS 2K8 package to a SQL Server 2K5 Integration Services or you get the below message “Could not load package…”.

SQL Server 2008 Integration Services

 SQL Server 2005 Integration Services

  
# ==============================================================================================
# Microsoft PowerShell Source File -- Created with SAPIEN Technologies PrimalScript 2009
# NAME: Deploy-File2SQL.ps1
# AUTHOR: Max Trinidad,
# DATE : 4/24/2010
#
# COMMENT: Deploy SSIS package, using the /File to copy the SSIS package from the filesystem to SQL Server Integration Services MSDB.
# to SQL Server Integration Services MSDB.
# ==============================================================================================

function Deploy-File2SQL {
<#
.SYNOPSIS
Deploy SSIS package, using the /File to copy the SSIS package from the filesystem to SQL Server Integration Services MSDB.
.Description
This function will allow you to deploy SSIS package *.dtsx to a SQL Server Integration Services. All the parameters are
require to deploy the package (except for "is2k8"). This function can be use in both PowerShell V1 and V2. Has the alias dfs
.Parameter is2K8
is2K8 - [String]. This is to identify is the package is going to a SQL Server 2008 Integration Services store. (Required)
Enter a "y" for SQL2k8 and "n" for SQL2k5.
.Parameter SrcFile
SrcFile - [String]. This is your SSIS package fullpath and name . (Required)
.Parameter CopyTo
CopyTo - [String]. This is the SQL Server destination name. (Required)
.Example
PS> dfs Y c:\temp\Sample_SSISpkg01.dtsx MAX-PCWIN1 Sample_SSISpkg01_copy
Using the full function name with all parameters
.Example
PS> Deploy-CopySSIS Y c:\temp\Sample_SSISpkg01.dtsx MAX-PCWIN1 Sample_SSISpkg01_copy
Using the alias function with all parameters
.Example
PS> dfs c:\temp\Sample_SSISpkg01.dtsx MAX-PCWIN1 Sample_SSISpkg01_copy
Using the full function name but excluding the first parameters because will deploy to SQL Server 2005 Integration Services
.Notes
NAME: Deploy-File2SQL
Alias: dcs
AUTHOR: Max Trinidad
Created: 04/25/2010 21:16:01 - Version 0.1
Compatibility - Version 1 and Version 2
.Link
about_functions
about_functions_advanced
about_functions_advanced_methods
about_functions_advanced_parameters
.Inputs
is2K8 - [String] - Required
SrcFile - [String] - Required
DestSrv - [String] - Required
CopyTo - [String] - Required
#>

Param(
[Parameter(Mandatory=$true, Position=0)] [string] $is2K8,
[Parameter(Mandatory=$true, Position=1)] [string] $SrcFile,
[Parameter(Mandatory=$true, Position=2)] [string] $DestSrv,
[Parameter(Mandatory=$true, Position=3)] [string] $CopyTo
)

#Initializing variables
$SQLver = "999"
$SQLVName = "SQLServerDesc"

#Location where the create batch file will be build.
$DefaultBatchBuildLocation = $Env:UserProfile + "\My Documents\WindowsPowerShell"

#This is the string use to create the batch file.
$Batch = @"
@echo SQLServeraDesc
@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"\999\DTS\Binn\dtutil.exe /Quiet /FILE %1 /DestServer %2 /copy SQL;%3
@Echo Process completed!
"@

#Write-Host $is2K8 $SrcFile $DestSrv $CopyTo

if ($is2K8.ToString() -eq "
Y"){

# Run DTUtil.exe from the SQL 2008 path - Running DOS batch command
$SQLVer = "
100"; $SQLVName = "SQL Server 2008"
Write-Host "
Building SQL2k8 Deploy Batch file in $DefaultBatchBuildLocation"

# Display the datetime before execution
Get-Date

#Building batch file step
$Batch = $Batch.replace("
SQLServerDesc",$SQLVName)
$Batch = $Batch.replace("
999",$SQLver)
$Batch | Out-File -FilePath $DefaultBatchBuildLocation\Deploy-SSISPkgUtil.bat -Encoding ASCII

#Execute created batchfile
& $DefaultBatchBuildLocation\Deploy-SSISPkgUtil.bat $SrcFile $DestSrv $CopyTo

# Display the datetime after execution
Get-Date

} else {
# Run DTUtil.exe from the SQL 2005 path - Running DOS batch command
$SQLVer = "
90"; $SQLVName = "SQL Server 2005"
Write-Host "
Building SQL2k5 Deploy Batch file in $DefaultBatchBuildLocation"

# Display the datetime before execution
Get-Date

#Building batch file step
$Batch = $Batch.replace("
SQLServerDesc",$SQLVName)
$Batch = $Batch.replace("
999",$SQLver)
$Batch | Out-File -FilePath $DefaultBatchBuildLocation\Deploy-SSISPkgUtil.bat -Encoding ASCII

#Execute created batchfile
& $DefaultBatchBuildLocation\Deploy-SSISPkgUtil.bat $SrcFile $DestSrv $CopyTo
# Display the datetime after execution
Get-Date
}

}; Set-Alias dfs Deploy-File2SQL

3. Deploy-File2File.ps1 – Copy the SSIS package stored in the filesystem to another location in the *filesystem or the same location but different package name.
 
*Note: As you can see in this picture, make sure to include the *.dtsx extension.

# ==============================================================================================
# Microsoft PowerShell Source File -- Created with SAPIEN Technologies PrimalScript 2009
# NAME: Deploy-File2File.ps1
# AUTHOR: Max Trinidad,
# DATE : 4/26/2010
#
# COMMENT: This is function will deploy a copy of a SSIS package in the filesystem to another
# filesystem folder.
# ==============================================================================================

function Deploy-File2File {
<#
.SYNOPSIS
Deploy a copy of a SSIS package in the filesystem to another filesystem folder.
.Description
This function will allow you to deploy SSIS package *.dtsx to another filesystem folder. All the parameters are
require to deploy the package(except for "is2k8"). This function can be use in both PowerShell V1 and V2. Has the alias dff
.Parameter is2K8
is2K8 - [String]. This is to identify is the package is going to a SQL Server 2008 Integration Services store. (Required)
Enter a "y" for SQL2k8 and "n" for SQL2k5.
.Parameter ScrFile
ScrFile - [String]. This is your SSIS package fullpath and name . (Required)
.Parameter CopyTo
CopyTo - [String]. This is the SQL Server destination name. (Required)
.Example
PS> dff Y c:\temp\Sample_SSISpkg01.dtsx C:\Temp\Sample_SSISpkg01_copy.dtsx
Using the full function name with all parameters
.Example
PS> Deploy-File2File Y c:\temp\Sample_SSISpkg01.dtsx C:\Temp\Sample_SSISpkg01_copy.dtsx
Using the alias function with all parameters
.Example
PS> dff C:\temp\Sample_SSISpkg01.dtsx C:\Temp\Sample_SSISpkg01_copy.dtsx
Using the full function name but excluding the first parameters because will deploy to SQL Server 2005 Integration Services
.Notes
NAME: Deploy-File2File
Alias: dcs
AUTHOR: Max Trinidad
Created: 04/25/2010 21:16:01 - Version 0.1
Compatibility - Version 1 and Version 2
.Link
about_functions
about_functions_advanced
about_functions_advanced_methods
about_functions_advanced_parameters
.Inputs
is2K8 - [String] - Required
SrcFile - [String] - Required
DestSrv - [String] - Required
CopyTo - [String] - Required
#>

Param(
[Parameter(Mandatory=$true, Position=0)] [string] $is2K8,
[Parameter(Mandatory=$true, Position=1)] [string] $SrcFile,
[Parameter(Mandatory=$true, Position=3)] [string] $CopyTo
)

#Initializing variables
$SQLver = "999"
$SQLVName = "SQLServerDesc"

#Location where the create batch file will be build.
$DefaultBatchBuildLocation = $Env:UserProfile + "\My Documents\WindowsPowerShell"

#This is the string use to create the batch file.
$Batch = @"
@echo SQLServeraDesc
@echo Running SSIS_Deploy_PckgUtil batch process
@echo ------------------------------------------
@echo Param1 = %1
@echo Param2 = %2
@echo ------------------------------------------
@echo -
C:\"Program Files"
\"Microsoft SQL Server"\999\DTS\Binn\dtutil.exe /Quiet /FILE %1 /copy File;%2
@Echo Process completed!
"@

#Write-Host $is2K8 $SrcFile $DestSrv $CopyTo

if ($is2K8.ToString() -eq "
Y"){

# Run DTUtil.exe from the SQL 2008 path - Running DOS batch command
$SQLVer = "
100"; $SQLVName = "SQL Server 2008"
Write-Host "
Building SQL2k8 Deploy Batch file in $DefaultBatchBuildLocation"

# Display the datetime before execution
Get-Date

#Building batch file step
$Batch = $Batch.replace("
SQLServerDesc",$SQLVName)
$Batch = $Batch.replace("
999",$SQLver)
$Batch | Out-File -FilePath $DefaultBatchBuildLocation\Deploy-SSISPkgUtil.bat -Encoding ASCII

#Execute created batchfile
& $DefaultBatchBuildLocation\Deploy-SSISPkgUtil.bat $SrcFile $CopyTo

# Display the datetime after execution
Get-Date

} else {
# Run DTUtil.exe from the SQL 2005 path - Running DOS batch command
$SQLVer = "
90"; $SQLVName = "SQL Server 2005"
Write-Host "
Building SQL2k5 Deploy Batch file in $DefaultBatchBuildLocation"

# Display the datetime before execution
Get-Date

#Building batch file step
$Batch = $Batch.replace("
SQLServerDesc",$SQLVName)
$Batch = $Batch.replace("
999",$SQLver)
$Batch | Out-File -FilePath $DefaultBatchBuildLocation\Deploy-SSISPkgUtil.bat -Encoding ASCII

#Execute created batchfile
& $DefaultBatchBuildLocation\Deploy-SSISPkgUtil.bat $SrcFile $CopyTo
# Display the datetime after execution
Get-Date
}

}; Set-Alias dff Deploy-File2File

Now, feel free to copy/paste this three functions.  And, as you probably notice, there’s still room for improvements.
 
For next week, Part 3 – Integrating your solution into your Visual Studio application.
 

Happy PowerShelling!!!

Advertisements

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.

5 Responses to Deploying SSIS Packages using DTUtil.exe with PowerShell – Part 2…

  1. Thanks , I have recently been looking for information approximately this subject for a long time and yours is the best I have discovered till now.

    However, what about the bottom line? Are you certain about the
    supply?

  2. pity on line says:

    I enjoy, lead to I found exactly what I used to
    be looking for. You’ve ended my 4 day long hunt! God Bless you man. Have a great day. Bye.

  3. Magnificent items from you, man. I have take into account
    your stuff prior to and you’re simply extremely great. I really like what you’ve obtained here, certainly like what you’re stating and the best way through which you say it. You make it entertaining and you still take care of to stay it wise. I can not wait to read much more from you. That is actually a terrific site.

  4. Alejandrina says:

    I every time used to study paragraph in news papers but now as I am a user
    of internet therefore from now I am using net for posts, thanks to
    web.

  5. If some one wishes to be updated with most up-to-date
    technologies therefore he must be visit this web
    site and be up to date all the time.

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