Sunday 24 January 2016

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

Sunday 3 January 2016

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