Deploy SSIS Packages to different Deployment Locations

AIM :-  In this Tutorial, we will show you how to Deploy SSIS Package to various Deployment locations.

Description :-  We will divide this post into 2 parts for in-depth learning and better understanding :-

Part 1 :- In this, will deal grab knowledge on various Deployment locations.

Part 2 :- Further, we will learn how to Deploy SSIS Packages with all available locations.

Before getting into the Technical part, I would like to introduce myself. Friends, I am Uday Krishna and this is my first article for “PhpRing”. I am passionate for MSBI suite and will be working on MSBI section of PhpRing Tutorials. I hope you all will like my first post on how to deploy SSIS packages.

Part 1. Deployment Locations

Let’s now understand SSIS Package deployment. Once our package is ready, we have the following 3 options to deploy SSIS Package. These 3 Deployment locations are explained below.

1. Deploy SSIS Package to the SQL Server (msdb Database).
2. Deploy SSIS Package to the Package Store.
3. Deploy SSIS Package to the File System.

SSIS package is actually just an XML file. We will take each Deployment location and will try to understand the differences between them.

Step 1.  Deploy SSIS Package to SQL Server :-

In this scenario, all SSIS packages will be deployed into msdb system database.

Step 2.  Deploy SSIS Package to Package Store :-

In this scenario, all SSIS packages will be deployed under package store (C:\Program Files\Microsoft SQL Server\110\DTS\Packages). SSIS is made aware of this location through the Folder tab inMsDtsSrvr.ini.xml configuration file and which is located in SSIS installation directory C:\Program Files\Microsoft SQL Server\110\DTS\Binn. This configuration file also contains SqlServerFolder which is pointing to msdb database and the configuration file looks as pasted below –

Step3.  Deploy SSIS Package to the File System :-

In this scenario, packages will be deployed in the Normal file system. Normal File system can be either file or folder.

Step4.  Understand difference between Package Store and File System :-

The difference between Package store and normal File system is that when Packages are deployed into any folder other than package store (C:\Program Files\Microsoft SQL Server\100\DTS\Packages). The packages cannot be accessed from the Stored Packages => File system location when SQL Server management studio connected.

File system accesses only the packages which are placed under File System Folder location fromConfiguration file. You can change the Package store location by changing the Storepath tag in configuration file and you can also add multiple folders to the package store by repating 9-12 lines in configuration file. Find the below modified configuration file where I have added another folder to Package store.

NOTE :-  Be careful when you are doing modifying system configuration file.

Stored packages now have two folders as part package store which is shown below. SSIS serviceneeds to be restarted to reflect changes once configuration file is modified.

With this we completed our First part on how to Deploy SSIS Packages to various different Deployment locations. I hope you liked this part on Understanding different Deployment locations. In ourNext part, I will explain how to Deploy SSIS Packages by using BIDS/SSDT tool or directly from Integration server (SQL server management studio) or Command tool (DTUTIL.exe) into above mentioned locations.

Part 2.  Deploy SSIS Packages

In the Previous part, I have explained about Different locations where our SSIS packages can bedeployed. In this part, I will explain the available tools by which we can Deploy SSIS packages in those locations.

Option 1.  Deploying Packages from SQL server management studio :-

Launch SQL Server Management Studio and connect to Integration Services. You can seeobject explorer as shown below.

There are two locations (File system and MSDB(Specified in package store MsDtsSrvr.ini.xml)) under Stored Packages where our   packages will be deployed.

To Install either on File system/MSDB database, you have to select import package option. This will be popped up when you Right click on the File System/MSDB option which will then displays a Import Package dialog box as shown below.

Import package dialog box is the one where we have to specify the package details which we are going to deploy. If you select File System option, Import package deploys package in the specified file system location in the XML file MsDtsSrvr.ini.xml. If you select MSDB, import package deploys package in MSDB database

1. Package Location : Specify the package location where deployable packages are located. Available options are File System, SQL Server, and SSIS Package store.
2. Server : You have to specify server name from where our packages are selected and this option is enabled only when SQL Server and SSIS package store options in Package location selected.
3. Authentication : You need to specify type of authentication (windows/SQL authentication) used to access saved packages and authentication Options will be enabled when SQL Server option is selected.
4. Package Path : Specify Package path where packages are saved.
5. Package Name : Specify the name of the package to be used after deployment is done.
6. Protection level : Tells SSIS how to deal with sensitive data stored within packages. Available Protection level are mentioned below.

For the purpose of example, I have selected package stored in File system and saved as calc package name under package store as shown below.

Option 2:  Installing SSIS packages using DTUTIL tool :-

dtutil.exe is a command tool used to manage SSIS packages. By using this tool you can copy, move, and delete packages from the three locations file system, Microsoft SQL Server database or Package store. You can check existence of the package using this tool. This tool identifies the storage type by/SQL /FILE and /DTS option. DTUTIL command tool is a better option when packages are to be deployed as batch command.

DTUTIL Syntax :-  DTUtil /option [value] [/option [value]].  Below table lists all Options and its values to be used in the tool.

In this example I will show how to copy a package Stored in File system to 3 available options.
Example 1 :- DTUTIL /FILE Package.dtsx /COPY SQL;SQLpack1 /DestUser sa /DestP S@lSERVER2013
Example 2 :- DTUTIL /FILE Package.dtsx /COPY DTS;SQLpack1
Example 3 :- DTUTIL /FILE Package.dtsx /COPY FILE;C:\SQLpack1
If you want get help about each command, you can type the dtutil /help on the command prompt which gives complete information about the command with examples.

Option3 :  Deploy packages using BIDS/SSDT environment :-

SSIS 2012 has two Deployment models – Project deployment model and Package deployment model. Before proceeding further first will understand what is Project deployment model and package deployment model.

Project deployment model :- Microsoft introduces this new deployment model in SSIS 2012. In this model, all the packages and variables will be saved as single file with an .ispac extension.  This model is enabled by default when SSIS project is created using SSIS 2012. In this new model, new concept parameter has been introduced to assign values. There is one nice article already written on “PhpRing Tutorials” explaining this concept. You can check the article here – New way of Deployment model in SSIS .

Package deployment model :- Package deployment model is the old process is used to deploy packages from one computer server to another server. This model also manages package dependencies such configuration, connections and other files required for package.

In this example, we will learn how to use this model to Install packages using 2012. For the purpose of this example, I have created SSIS project with two packages as shown below. These two packages execute select command on the specified database.

As I explained in Project deployment model, integration project in 2012 will be created with Project deployment model by default. In order change Package deployment model Right click on the integrationproject explorer and select the option convert to package deployment model as shown below.

When integration project is converted to Package deployment model, all the configuration will be converted as per package deployment model requirement and project will not be converted into package deployment model if any project deployment model features are used in the project such asparameters.
1.  If we have not used any of the project deployment, SSIS project will be converted successfully with below message.

2.  If our Project is used project deployment model, it will end up with below error message when tried to convert.

Once the project is converted to Package deployment model, solution explorer will be shown as below.

Here project.params removed as this feature is only part of project deployment model. You canconvert this project again to project deployment model (Rightclickà Select convert to project deployment model option).  After package is converted into the model –

1. Go to Properties of the Integration project.
2. Select Deployment utility under configuration properties.
3. Change Deployment utility option to true (this is false by default).
4. Provide Deployment path where deployment utility bundle will be saved.
5. Bin\Deployment is default location and this can be changed to any path.

This deployment Model (Package) options will only be displayed when SSIS project is converted toPackage deployment model.
Build the application which creates deployment bundle in the specified path. This bundle consists of package files and other package variables added to the integration project and deployment utility will be created by this name <package name>.SSISDeploymentManifest. Copy the deployment utility folder to destination server and click on Deployment manifest utility and select the location where our packages will be installed which will then install packages either on File system or SQL server based up on selected option.

With this we complete our both parts of the topic Deploy SSIS Packages to Different Locations. I hope both the parts clear your concepts on How to Deploy SSIS Packages and What are various Deployment Locations. Your feedback is valuable and if you have any queries, Please drop a comment below. You can subscribe to our Fb page for more – PhpRing Tutorials FB Page.

SSIS Package Deployment and Run by SP

Introduction

After a long work with SSIS package by developing, testing, bug fixing etc, we have to deploying the package. This process is very important and we should take care of deploying process. We must understand how the package executed, when the package executed etc before choosing the right path of deployment.

Different type of options of deployment

With SSIS package there are three types of options to deploying the package.

1. By using the Deployment Utility

2. By using the Command line Executable

3. With SQL Server Management Studio

Now we have to understand each and every option to choose the right path of deployment.

Package Description

To perform this demonstration we have a simple package named ETLToCopyData.dtsx. It just copy data from source table to destination table. It has dynamic connection string and XML package configuration. If have you any problem, related to those configuration please see my previous article for help.

By using the Deployment Utility

Step-1 [ Setting the Project Property ]

We can do this by right click the Project name (in our case it is Test_Project) and select the property from shortcut menu. Then choose the Deployment Utility options and make the CreateDeploymentUtility property to TRUE. By default it is FALSE.

Here the DeploymentOutputPath property value is important. Here in our example it is "bin\Deployment" that means the deployment file is stored in this path.

Step-2 [ Now Build the Package ]

After build we find three types of files in the destination folder. Remember the destination path is mentioned at the DeploymentOutputPath property of the project. The types are Integration Service package (In our case it is ETLtoCopyData with dtsx extention), Integration service Deployment Manifest (In our case it is Test_Project .SSISDeploymentmanifest) and Integration Service Configuration (in our case it is Test_Project_Config .dtsConfig).

We can copy this file to any location and by double click we can run it.

By using the Command line Executable

Microsoft provides an executable utility called the DTUTIL.EXE that can be used to deploy the SSIS package. We can use this utility directly or by making a batch file.

We must execute this utility from command prompt.

We must go to the path of the package and execute the below scripts.

DTUTIL /FILE EtltoCopyData.dtsx

/COPY

SQL;Test_Project

In order to deploy the package at the file system level then go with the below script

DTUTIL /FILE EtltoCopyData.dtsx

/COPY

FILE;C:\SSIS\SSISPackage1.dtsx

For better understanding

http://bennyaustin.wordpress.com/2010/01/30/ssis-package-deployment-method-file-system-or-server/

With SQL Server Management Studio

Please note that this approach required integration services to be connected and must be in running mode in order to use this deployment option.

In the object browser we can see something like below

Click OK to deploy.

For detail understanding

http://shahfaisalmuhammed.wordpress.com/2010/01/14/deploying-integration-services-packages-using-importexport-feature-in-ssms/

Stored Procedure to Run the SSIS Package

Here we develop a stored procedure (SP) to run the package. We can run this package from scheduler also.

It's a ready to run SP, we just supplied the parameter to run this SP only.

/*

   EXEC [DBO].[SP_BI_PROCESSPACKAGE]

     @p_PackageFile  = 'C:\SSISDeployment\ETLFinFundPosition.dtsx', 

     @p_ConfigFile   = 'C:\SSISDeployment\EtlFinFundPosition_PkagConfig.dtsConfig'

*/

IF OBJECT_ID(N'SP_BI_PROCESSPACKAGE', N'P') IS NOT NULL

   BEGIN

      DROP PROCEDURE [DBO].[SP_BI_PROCESSPACKAGE];

   END

GO  

CREATE PROCEDURE [DBO].[SP_BI_PROCESSPACKAGE]

       (

                     @p_PackageFile             VARCHAR(1000) = NULL,

                     @p_ConfigFile     VARCHAR(1000) = NULL

       )

AS

BEGIN

              SET NOCOUNT ON;

              DECLARE @v_PackageFile     VARCHAR(2000),

                         @v_ConfigFilr   VARCHAR(2000),

                         @v_CMD               VARCHAR(2000),

                         @v_FILENAME     VARCHAR(2000) ;

     

        CREATE TABLE #DIR

        ([FILENAME] VARCHAR(8000));

       

        SET @v_FILENAME = '';

         

              EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1

              RECONFIGURE

              EXEC SP_CONFIGURE 'XP_CMDSHELL', 1

              RECONFIGURE

 

        SET @v_PackageFile = 'dir /B '+ @p_PackageFile

        SET @v_ConfigFilr  = @p_ConfigFile;

       

       

              INSERT #DIR

        EXEC MASTER..XP_CMDSHELL @v_PackageFile;

      

       

       

        DELETE #DIR

        WHERE  FILENAME IS NULL

               OR FILENAME LIKE '%NOT FOUND%';

              

       WHILE @v_FILENAME < (SELECT MAX(FILENAME) FROM #DIR)

             BEGIN

                SELECT @v_FILENAME =  MIN(FILENAME)

                 FROM  #DIR

                  WHERE FILENAME > @v_FILENAME;

                 

                        SET  @v_CMD =  'DTEXEC /F "'+ @p_PackageFile +'"  /CONFIGFILE "'+@v_ConfigFilr +'"'

                       EXEC MASTER..XP_CMDSHELL @v_CMD

            END

      DROP TABLE #DIR

END     

GO

Hope you like this.