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.
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
In order to deploy the package at the file system level then go with the below script
DTUTIL /FILE EtltoCopyData.dtsx
For better understanding
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
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.
@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
DROP PROCEDURE [DBO].[SP_BI_PROCESSPACKAGE];
CREATE PROCEDURE [DBO].[SP_BI_PROCESSPACKAGE]
@p_PackageFile VARCHAR(1000) = NULL,
@p_ConfigFile VARCHAR(1000) = NULL
SET NOCOUNT ON;
DECLARE @v_PackageFile VARCHAR(2000),
@v_FILENAME VARCHAR(2000) ;
CREATE TABLE #DIR
SET @v_FILENAME = '';
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1
EXEC SP_CONFIGURE 'XP_CMDSHELL', 1
SET @v_PackageFile = 'dir /B '+ @p_PackageFile
SET @v_ConfigFilr = @p_ConfigFile;
EXEC MASTER..XP_CMDSHELL @v_PackageFile;
WHERE FILENAME IS NULL
OR FILENAME LIKE '%NOT FOUND%';
WHILE @v_FILENAME < (SELECT MAX(FILENAME) FROM #DIR)
SELECT @v_FILENAME = MIN(FILENAME)
WHERE FILENAME > @v_FILENAME;
SET @v_CMD = 'DTEXEC /F "'+ @p_PackageFile +'" /CONFIGFILE "'+ @v_ConfigFilr +'"'
EXEC MASTER..XP_CMDSHELL @v_CMD
DROP TABLE #DIR
Hope you like this.
Posted by: MR. JOYDEEP DAS