SSIS Package Deployment Automation

Introduction

Package Deployment is important aspect in the overall SSIS package development life cycle.  Deployment could be done manually but it is time consuming effort, particularly when deploying to MSDB. Unfortunately, for deployment related activities; BIDS does not have anything apart from generating manifest file.  It is beneficial to have something that helps and does package deployment directly from BIDS.
This article describes the steps to use DOS based utility that will deploy the Packages automatically to the MSDB Database. The 1st section of the article has information on required files that will do the deployment where as the 2nd section describes the Automation of package deployment from BIDS.

Dos Based Utility:
We will create two .BAT/.CMD (DOS batch file) files, one for deploying current active package and another for deploying all packages from current solution. These batch files usage “Dtutil” to get the things done.
Command used in them is:-
dtutil /FILE “%PackagePath%” /Copy SQL;”%TargetFolder%%PackageName%” /DestS  “%TargetServer%” /Q
Note: /Q Switch signifies that utility will overwrite the package without asking the user whenever package with the same name already exists on a location.
Both utilities take three Input Parameters. They are:-
i) TargetServer
ii) TargetFolder
iii) ItemPath

TargetServer and TargetFolder variables are read from the “EnvironmentVariables.cmd” and Item Path is passed as an argument to it.

Following is the required code:

@Rem DeployAllPackages.cmd
 
@Echo Off
SET ItemFolder=%~dp1

if exist "%ItemFolder%EnvironmentVariables.cmd" call "%ItemFolder%EnvironmentVariables.cmd"
 
SET LastChar=%TargetFolder:~-1%
 
@Rem Add the Slash "\" if not Present at End of string
IF "%TargetFolder%" NEQ ""  IF "%LastChar%" NEQ "\"  SET TargetFolder=%TargetFolder%\
 
Echo TARGETSERVER :: %TargetServer%
Echo TARGETFOLDER :: %TargetFolder%
 
FOR %%i IN ("%ItemFolder%*.dtsx") DO Call :RUNDTUTIL "%%i" "%%~ni%"
GOTO :EOF
 
 
:RUNDTUTIL
SET PackagePath=%~1
SET PackageName=%~2
 
ECHO PACKAGEPATH :: %PackagePath%
ECHO PACKAGENAME :: %PackageName%
ECHO.
ECHO dtutil /FILE "%PackagePath%" /Copy SQL;"%TargetFolder%%PackageName%" /DestS  "%TargetServer%" /Q
dtutil /FILE "%PackagePath%" /Copy SQL;"%TargetFolder%%PackageName%" /DestS  "%TargetServer%" /Q
 
 
:EOF

@Rem DeployPackage.cmd
 
@Echo Off
SET PackagePath=%1
SET PackageName=%~n1%
SET ItemFolder=%~dp1
 
ECHO %Itemfolder%
 
if exist "%ItemFolder%EnvironmentVariables.cmd" call "%ItemFolder%EnvironmentVariables.cmd"
 
SET LastChar=%TargetFolder:~-1%
 
@Rem Add the Slash "\" if not Present at End of string
IF "%TargetFolder%" NEQ ""  IF "%LastChar%" NEQ "\"  SET TargetFolder=%TargetFolder%\
 
 
Echo TARGETSERVER :: %TargetServer%
Echo TARGETFOLDER :: %TargetFolder%
ECHO PACKAGEPATH :: %PackagePath%
ECHO PACKAGENAME :: %PackageName%
ECHO.
 
ECHO dtutil /FILE %PackagePath% /Copy SQL;"%TargetFolder%%PackageName%" /DestS  "%TargetServer%" /Q
dtutil /FILE %PackagePath% /Copy SQL;"%TargetFolder%%PackageName%" /DestS  "%TargetServer%" /Q

@Rem EnvironmntVariabls.cmd
 
@rem Specify the values without enclosing them in Double Quotes "" . 
@rem Does not matter if values has spaces or slashes in them
@rem TargetServer is SQL Server instance that host the MSDB
@rem TargetFolder is folder in MSDB that will contain these packages
	 
 
@set TargetServer=LocalHost
@set TargetFolder=SS IS\SS IS

Integrate Dos Utilities with SSIS Project

Once these utilities are created, we need to integrate them with SSIS Project. Below step will list out the details about their integration with the Project.
i) Create above mentioned files on your local hard drive.
ii) Open the SSIS Project in BIDS
iii) Go to solution Explorer and right click on the project.
iv) Click on Add link and then on “Existing Item” link. Once this is done, browse to the location where EnvironmentVariables.cmd files is being downloaded and add both these files.
v) This file will be shown up under “Miscellaneous” folder as shown below.

EnvironmentVariables.cmd file will be copied to the same location that contains the Packages.

i)                    Copy the DeployPackages.cmd and DeployAllPackages.cmd files from the downloaded location to the Binn folder of SSIS Install Directory.

Generally Binn Folder Location is “C:\Program Files\Microsoft SQL Server\90\DTS\Binn”, if the SSIS Install Directory was chosen as “C:” drive. If some other install directory is used, Please fund the binn folder accordingly.

ii)                   Now, Click on “Tools” in the Toolbar.

iii)                 Click on “External Tools” Link and it will open a dialog box to add the utility.

iv)                 Click on Add button in the Dialog box.

v)                  Insert some text in the “Title” Text box. Let’s Say “Deploy Package to MSDB”

vi)                 Go to the command Section and browse to the physical location that contains “DeployPackage.cmd” File. Location of the file is the binn folder of SSIS Install directory.

Go to the Argument Section, Add “$(ItemPath)” variable. Once all this is done, Dialog box should look like as shown below :-

i)                    Click on OK.

ii)                   Repeat Steps from (vii) to (xiii) to add “Deploy All Packages to MSDB” utility to the Project. This should look like the following :-

After these Steps, “Deploy Package to MSDB” and “Deploy All Packages to MSDB” will be shown under “Tools” Section.

If we need to deploy current Package, Change the Variable values in “EnvironmentVariables.cmd” file and click on “Deploy Package to MSDB”. This should deploy the current Package to the MSDB Database.

If we need to deploy all the Packages of a Project, Change the Variable values in “EnvironmentVariables.cmd” file and click on “Deploy All Packages to MSDB”. This should deploy all the Packages to the MSDB Database.

Regards

Rakesh Mishra

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook