SSIS - Configuring Integration Service Catalog

Introduction

Integration service catalog is the new feature of SQL Server started from SQL Server 2012. It is the easy way to deploy the Project. This article is dedicated to the SSIS Developer of 2008 who are now working on SSIS 2012 or 2014. Hope it will be interesting.

How we DO That

Step – 1 [ We have a Package with Project Level Parameters ]

The Project level parameters are described bellow

Name	Data Type	Value
pFlatFileSource	String	E:\ClassSSIS\FlatFile\StudentDetails.txt
pSQLServerName	String	JOYDEEP-LAPTOP\JOYDEEPSQL12
pSQLDBName	String	PracticeDB

Step – 2 [ Use of Project Level Parameters in the Package ]

We used the project level parameters in the Connection String Property of box Flat File Connection manager and OLEDB Destination Connection Manager.

Step – 3 [ Configuring the Integration Service Catalog ]

Integration Service Catalog à Right Click à Create Catalog …

Step – 4 [ Now Deploy the SSIS Project ]

First compile the Project and the deploy it.

Right Click the Project Solution  à Deploy

Here we choose the SQL Server name and Integration Service Catalog Path with Sub Folder.

Step – 5 [ Configuring the Environments ]

Now we have to understand the Environment

It is telling us where we want to run the SSIS package. For example the Development, QA or Production Environment.

Our package have some package level variable and the value of those variable is different depends on the environment.

Step – 6 [ Configure the Project with Specified Environment that we Configure ]

Project à Right Click à Configure

Hope you like it.

Posted by: MR. JOYDEEP DAS

Multi CTE is a Single SELECT Statement

Introduction

In this article we are going to demonstrate a simple example of CTE. How we can use multiple CTE is a single SELECT statement. Hope it will be informative and you will enjoy it.

How we use Multi CTE is SELECT

Step – 1 [ Create Base Table and Put Values in it ]

IF OBJECT_ID(N'[dbo].[tbl_Employee]', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_Employee];

   END

GO

CREATE TABLE [dbo].[tbl_Employee]

   (

     EmpID      INT         NOT NULL PRIMARY KEY,

     EmpName    VARCHAR(50) NOT NULL,

     EmpGrade   CHAR(1)     NOT NULL

   );

GO

IF OBJECT_ID(N'[dbo].[tbl_EmployeeSal]', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EmployeeSal];

   END

GO

CREATE TABLE [dbo].[tbl_EmployeeSal]

   (

     EmpID      INT           NOT NULL PRIMARY KEY,

     Sal        NUMERIC(18,2) NOT NULL,

     Bonus      NUMERIC(18,2) NULL

   );

GO

ALTER TABLE [dbo].[tbl_EmployeeSal]

ADD CONSTRAINT  FK_EmpID FOREIGN KEY(EmpID) REFERENCES [dbo].[tbl_Employee](EmpID);

GO

INSERT INTO [dbo].[tbl_Employee]

       (EmpID, EmpName, EmpGrade)

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

       (102, 'Deepasree Das', 'B'),

       (103, 'Soumen Bhoumik', 'C');

GO

INSERT INTO [dbo].[tbl_EmployeeSal]

       (EmpID, Sal, Bonus)

VALUES (101, 5000.00, 200.00),

       (102, 4000.00, 180.00),

       (103, 2500.00, 100.00);

GO

Step – 2 [ Using Multi CTE is a Single SELECT Statement ]

;WITH EmployeeDetails

     (EmpID, EmpName, EmpGrade)

AS

(

    SELECT EmpID, EmpName, EmpGrade

      FROM   [dbo].[tbl_Employee]

),

     EmployeeSalary

     (EmpID, Sal, Bonus)

AS

(

    SELECT EmpID, Sal, ISNULL(Bonus, 0) AS Bonus

      FROM   [dbo].[tbl_EmployeeSal]

)

SELECT  a.EmpID, a.EmpName, a.EmpGrade, b.Sal, b.Bonus,
        b.Sal + b.Bonus AS TotalSal

FROM    EmployeeDetails AS a

        INNER JOIN EmployeeSalary AS b ON a.EmpID = b.EmpID;

Output:

EmpID   EmpName                      EmpGrade          Sal                Bonus      TotalSal

101         Joydeep Das                    A                           5000.00        200.00      5200.00

102         Deepasree Das                 B                           4000.00        180.00      4180.00

103         Soumen Bhoumik            C                           2500.00        100.00      2600.00

Step – 3 [ Using a CTE Reference within other CTE is Single SELECT Statement ]

;WITH EmployeeDetails

     (EmpID, EmpName, EmpGrade)

AS

(

    SELECT EmpID, EmpName, EmpGrade

      FROM   [dbo].[tbl_Employee]

),

     EmployeeSalary

     (EmpID, EmpName, EmpGrade, Sal, Bonus, TotalSal)

AS

(

    SELECT b.EmpID, b.EmpName, b.EmpGrade, a.Sal, a.Bonus,

           a.Sal + a.Bonus AS TotalSal

    FROM   [dbo].[tbl_EmployeeSal] AS a

           INNER JOIN EmployeeDetails AS b ON a.EmpID = b.EmpID

)

SELECT  *

FROM    EmployeeSalary;

Output:

EmpID   EmpName                      EmpGrade          Sal                Bonus           TotalSal

101         Joydeep Das                    A                           5000.00        200.00          5200.00

102         Deepasree Das                 B                           4000.00        180.00         4180.00

103         Soumen Bhoumik            C                           2500.00        100.00         2600.00

Hope you like it.

Posted by: MR. JOYDEEP DAS

SSIS – Using BULK INSERT Part -II

Introduction

This article is the continuous of my previous article named SSIS – Using BULK INSERT. If you not read it earlier you can move on the link.

In this article we are just using the Format File and BULK INSERT command with SSIS component. Hope it will be informative.

What the Scenario is

We have a Flat file and we want to copy the flat file to a MS SQL Server table by using BULK INSERT command. We also use the Format file in XML or Text file format.

How We Do that

 Step – 1 [ The Destination Table Objects ]

--- Destination Table Objects ---

IF OBJECT_ID(N'[dbo].[tbl_StudentDetails]', N'U')IS NOT NULL

   DROP TABLE [dbo].[tbl_StudentDetails];

GO

CREATE TABLE [dbo].[tbl_StudentDetails]

    (

        StdRoll   INT         NOT NULL PRIMARY KEY,

        Stdname   VARCHAR(50) NULL,

        StdClass  INT         NULL,

        Section   CHAR(1)     NULL

      )

GO

Step – 2  [ The Control Flow of Package ]

Step – 3 [ The Variables ]

Name	Type	Default Value
vFormatFile	String	E:\Preparation\SSIS\BCP\FmtStudentDetails.xml
vSQLInstance	String	JOYDEEP-LAPTOP\JOYDEEPSQL12
vTextFile	String	E:\Preparation\SSIS\BCP\test.txt

Step – 4 [ The Execute SQL Task Named Truncate Destination ]

This is nothing but TRUNCATING the Destination for Full Load Purpose.

TRUNCATE TABLE [dbo].[tbl_StudentDetails];

Step – 5 [ Execute Process Task named Create Format File ]

This process task is used for creating the format file of Table.

The Expression is

" PracticeDB.dbo.tbl_StudentDetails  format nul -c -f " + @[User::vFormatFile] +" -x -S "+ @[User::vSQLInstance]  +" -T -q -t;"

Evaluated Value

PracticeDB.dbo.tbl_StudentDetails  format nul -c -f E:\Preparation\SSIS\BCP\FmtStudentDetails.xml -x -S JOYDEEP-LAPTOP\JOYDEEPSQL12 -T -q -t;

Step – 6 [ Execute Process Task named Execute BCP ]

The Expression is

" PracticeDB.dbo.tbl_StudentDetails IN "+ @[User::vTextFile]  +"  -f "+ @[User::vFormatFile]  +" -S "+ @[User::vSQLInstance]   +" -T"

Evolutes Expression

PracticeDB.dbo.tbl_StudentDetails IN E:\Preparation\SSIS\BCP\test.txt  -f E:\Preparation\SSIS\BCP\FmtStudentDetails.xml -S JOYDEEP-LAPTOP\JOYDEEPSQL12 -T

Step – 7  [  Run the Package and Observation ]

SELECT * FROM [dbo].[tbl_StudentDetails];

Hope you like it.

Posted by: MR. JOYDEEP DAS