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.

Posted by: MR. JOYDEEP DAS

SSIS Calling the Child Package

Introduction

Subroutine is an important part of the workflow. By using Executing Package Task control flow task we can use the existing package as a part of our SSIS workflow. It reduce the coding and easier for maintenance.

Use of Execute Package Task

As per MSDN

Breaking down complex package workflow:

This task lets us break down workflow into multiple packages, which are easier to read, test, and maintain.

Reusing parts of packages:

Other packages can reuse parts of a package workflow. For example, we can build a data extraction module that can be called from different packages. Each package that calls the extraction module can perform different data scrubbing, filtering, or aggregation operations.

Grouping work units:

Units of work can be encapsulated into separate packages and joined as transactional components to the workflow of a parent package. For example, the parent package runs the accessory packages, and based on the success or failure of the accessory packages, the parent package either commits or rolls back the transaction.

Controlling package security:

Package authors require access to only a part of a multi package solution. By separating a package into multiple packages, we can provide a greater level of security, because we can grant an author access to only the relevant packages.

Case Study

Here we are taking a simple case study to understand the process.

We have two package called Parent Package and Child Package. The Parent Package read a table objects called tblEmployee_Records and search for Grade-A holder employee. If he fined the records then it called the child package and stores the records in another table objects called tblGradeA_Employee_Records.

Here the parent package supplied the Grade of "A" to the Child Package and the child Package retrieve all the records of Grade-A and store it in destination table. 

As it is a simple demonstration, Limitation of this case study is only one employee of each grade can exists on the source table objects.

How we configure that

Step-1 [ Creating Source and Destination Table Object ]

-- Source Table Objects

IF OBJECT_ID(N'tblEmployee_Records', N'U') IS NOT NULL

   BEGIN

      DROP TABLE tblEmployee_Records;

   END

GO

CREATE TABLE tblEmployee_Records

       (EMPID     INT               NOT NULL IDENTITY(1,1) PRIMARY KEY,

        EMPNAME   VARCHAR(50)   NOT NULL,

        EMPGRADE  VARCHAR(7)    NOT NULL);

-- Inserting Records

INSERT INTO  tblEmployee_Records

       (EMPNAME, EMPGRADE)

VALUES ('joydeep Das', 'A'),

       ('Sangram Jit', 'B'),

       ('Debayan Bishwas', 'C'),

       ('Koushik Acharya', 'D');

-- Listing Reords

SELECT * FROM tblEmployee_Records; 

EMPID	EMPNAME	EMPGRADE
1	joydeep Das	A
2	Sangram Jit	B
3	Debayan Bishwas	C
4	Koushik Acharya	D

-- Destination Table Objects

IF OBJECT_ID(N'tblGradeA_Employee_Records', N'U') IS NOT NULL

   BEGIN

      DROP TABLE tblGradeA_Employee_Records;

   END

GO

CREATE TABLE tblGradeA_Employee_Records

       (EMPID     INT           NOT NULL PRIMARY KEY,

        EMPNAME   VARCHAR(50)   NOT NULL,

        EMPGRADE  VARCHAR(7)    NOT NULL);

Step-2 [ Control Flow and Data Flow tasks of Parent and Child Package ]

Step-3 [ Declaring Variables of Parent and Child Package ]

Step-4 [ Execute SQL Task Editor Configuration ]

First we define the Connection and in the SQL Statement we provide SQL string like this.

SELECT CONVERT(varchar(7), EMPGRADE)EMPGRADE

FROM tblEmployee_Records;

Step-5 [ Foreach Loop Editor Configuration ]

Step-6 [ Execute SQL Tasks within the Foreach Loop Container Configuration ]

First we define the Connection and in the SQL Statement we provide SQL string like this.

SELECT CONVERT(VARCHAR(7), EMPGRADE) AS EMPGRADE   

FROM   tblEmployee_Records

WHERE  EMPGRADE = ?

Resultset : Single Row

Step-7 [ Parent Package Precedence Constraint Editor Configuration ]

Step-8 [ Execute Package Task Editor Configuration ]

Step-9 [ Package Configuration Organizer – Child Package ]

Step-10 [ Child Package – OLE DB Source and OLE DB Destination ]

This configuration is as before. If have you any confusion, please refer to my previous article.

Source OLE DB Editor Configuration

Step-11 [ Run the Package – Parent Package ]

Step-12 [ Analyze the Destination Table Objects ]

SELECT * FROM tblGradeA_Employee_Records   

EMPID                  EMPNAME                      EMPGRADE

1                              joydeep Das                       A

Hope you like it.

Posted by: MR. JOYDEEP DAS

SSIS Merge Transformation

Introduction

Merge Transformation is used when we get data from two different data source and merge them in order specified and load it into destination. The Merge Join transformation provides an output that is generated by joining two sorted data sets using a FULL, LEFT, or INNER join. This is similar to database join operation.

This transformation has two inputs and one output. It does not support an error output.

Case Study

Here we have two table objects called “tbl_Merge_Employee” and “tbl_Merge_Dept” contains records employee and department.

According to the Employee Code sorting at ascending order we merge them both (JOIN) and store the output into a flat file called MergeResult.txt.

How to Configure it

Step-1 [ Creating the Source Table Objects ]

-- Create the Base Table

IF OBJECT_ID(N'tbl_Merge_Employee', N'U') IS NOT NULL

   BEGIN

      DROP TABLE tbl_Merge_Employee;

   END

GO

CREATE TABLE tbl_Merge_Employee

       (EMPCODE     INT         NOT NULL PRIMARY KEY,

        EMPNAME     VARCHAR(50)  NOT NULL,

        EMPGRADE    VARCHAR(1)   NOT NULL);

GO

IF OBJECT_ID(N'tbl_Merge_2', N'U') IS NOT NULL

   BEGIN

      DROP TABLE tbl_Merge_Dept;

   END

GO

CREATE TABLE tbl_Merge_Dept

       (EMPCODE     INT          NOT NULL PRIMARY KEY,

        EMPDEPT     VARCHAR(50)  NOT NULL);

GO  

-- Inserting Records

INSERT INTO tbl_Merge_Employee

       (EMPCODE, EMPNAME, EMPGRADE)

VALUES (1, 'Joydeep Das', 'A'),

       (2, 'Sukamal Jana', 'A'),              

       (3, 'Sangram Jit', 'B'),

       (4, 'Debayan Biswas','C');

GO

INSERT INTO tbl_Merge_Dept

       (EMPCODE, EMPDEPT)

VALUES (1, 'Development'),

       (2, 'Development'),              

       (3, 'Implementation'),

       (4, 'Support');      

-- Retriving Records

SELECT EMPCODE, EMPNAME, EMPGRADE FROM tbl_Merge_Employee;

SELECT EMPCODE, EMPDEPT FROM tbl_Merge_Dept;     

EMPCODE           EMPNAME                          EMPGRADE

1                              Joydeep Das                      A

2                              Sukamal Jana                     A

3                              Sangram Jit                        B

4                              Debayan Biswas                C

EMPCODE           EMPDEPT

1                              Development

2                              Development

3                              Implementation

4                              Support

Step-2 [ SSIS Data Flow Tasks ]

Step-3 [ OLEDB Source Editor Configuration ]

Step-4 [ Sort Transform Editor Configuration ]

Step-5 [ Merge Join Transform Editor Configuration ]

Step-6 [ Flat File Destination Editor Configuration ]

Step-7 [ Run the SSIS Package ]

Step-8 [ Analyze the Flat file destination ]

Hope you like it.

Posted by: MR. JOYDEEP DAS