Wednesday 31 July 2013

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
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.
/*
   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

Thursday 25 July 2013

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

Sunday 7 July 2013

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