Deploying a SSIS Package on Target Machine

we can deploy the SSIS package in in three ways.

The easiest way is File system to deploy the package.
in easy and understandable words, SSIS package actually is XML file, what deployment does is copies SSIS package with the configuration and paste it at given specific place. The place could be anywhere out of three. First is MSSQLSERVER/DTS/Packages, second is in MSDB database, and third is in Package store.

Let’s start with example.
there are three ways to deploy a SSIS package.

1. Deployment utility from SSIS package itself.
2. by using DTUTIL.exe command utility provided by SQL Server.
3. using SSMS (Management Studio)

using SSIS Package deployment Utility

by using this utility Microsoft SSIS technology creates SSIS Package installer in your installation folder.
with using this utility you will get one Manifest file, where your current project is.
the path will be
c:\\yourfolder\projectname\bin\Deployment

let’s see the process.

if the project is not open, open it and select properties of project.

let’s create one test package.

select properties of the package and you will get this page.

as you can see the option CreateDeploymentUtility is set to False.
and we have default path as well, which is bin\Deployment

to deploy our package we have to set the property CreateDeploymentUtility to True.

and Apply it.

once you build your package and run it.
now once you build and run the package you will get the Manifest file along with the package.

my package is stored in this folder

c:\folder\SSIS\TestPkg\

in bin folder you will get Deployment and package itself.

the below image shows the content of Deployment folder which are package itself and one manifest file.

so to perform the deployment you will have to copy and run the Manifest file on to the target machine.
and there you go you package will be in the target machine with configuration file and everything.

————————————————————————————————————————————————————————————————————————

Second way is to use DTUTIL.exe

SQL Server gives an utility which does deployment, but it command line statement.
the utility is DTUTIL, and it can be used only by command prompt.

in the coming example, I will show how to deploy a TestPkg.dtsx package to the file system, package store or SQL Server.

To deploy to the file system, you can use DOS copy command or DTUTIL command

the given example shows the command line to deploy the package to the File System using DTUTIL command.

dtutil / TestPkg.dtsx /Copy File;C\Yourfolder\TestPkg.dtsx

To deploy a package to a Package Store, use this command.

dtutil / TestPkg.dtsx /Copy DTS; TestPkg.dtsx

To deploy a package to a SQL Server, use this command.
this command will deploy the package to your local macine’s default SQL Sever instance.

dtutil / TestPkg.dtsx /Copy SQL;TestPkg.dtsx

if you are deploying your package to different SQL Server instance you need to add this in your command.

/DestinationServer “Servername\yourInstanceName”

————————————————————————————————————————————————————————————————————————

Third way is to use SSMS (Management Studio) itself.

just logon to the Intergration services in SSMS.

remember that the integration services should be running into the system.

so the intial screen you will get is.

so once you enter into the SSMS, you should be getting this below screen.

As you can see in above image, there are two nodes under the Stored Procedure.
File System and MSDB.
File System is the package store with the default location in SQL Server 2008 r2.
the location is C:\Program Files\Microsoft SQL Server\90\DTS\Packages.
MSDB is the MSDB database.

here I wil show the deployment the TestPkg.dtsx package from the location in to the project folder to the package store.

and to the MSDB database.

To Deploy the package to the package store, just right click on the File System and select the option Import Package from the menu.

select package path to get the package.

give the package name and press OK to import the package.

To Deploy the package to the MSDB Database, just right click on the MSDB and select the option Import Package from the menu.

That’s about it..

Good Luck.. Try out Deploy the packages..