SQL Server: 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.