Wednesday 24 April 2013

SSIS OLE DB source with Stored Procedure

Introduction
Stored Procedure (SP) is an important factor of very SQL Developer. This article is related to, how to handle the Stored Procedure by SSIS.

Mapping the SP from SSIS OLE DB source
The steps are mentioned bellow.

Step-1 [ Create the Base Table and Stored Procedure ]       
Here the name of the base table is "Tbl_ProductMaster" and by the use of stored procedure called "sproc_FindProduct" we are just going to find the product details by passing the parameter product id.

-- Creat the Base Table
IF OBJECT_ID('Tbl_ProductMaster') IS NOT NULL
   BEGIN
     DROP TABLE Tbl_ProductMaster
   END
GO

CREATE TABLE Tbl_ProductMaster
       (PRODID         INT          NOT NULL IDENTITY PRIMARY KEY,
        PRODNAME       VARCHAR(50)  NOT NULL,
        PRODCATEGORY   VARCHAR(50)  NOT NULL);
GO

-- Inserting Records
INSERT INTO Tbl_ProductMaster
       (PRODNAME, PRODCATEGORY)
VALUES ('Tooth Paste', 'Dental'),
       ('Tooth Brush', 'Dental'),
       ('Saving Brush', 'Cosmetics'),
       ('Saving Gel', 'Cosmetics');
      
-- Creating the Stored procedure
IF OBJECT_ID('sproc_FindProduct') IS NOT NULL
   BEGIN
     DROP PROCEDURE sproc_FindProduct;
   END
GO                          

CREATE PROCEDURE sproc_FindProduct
       (
          @var_ProductId  INT = 0
       )  
AS
BEGIN
   SELECT PRODID, PRODNAME, PRODCATEGORY
    FROM  Tbl_ProductMaster
     WHERE PRODID =  @var_ProductId;
END      

-- T-SQL Testing of procedure
EXECUTE dbo.sproc_FindProduct
        @var_ProductId = 2
       
-- Output Result
PRODID      PRODNAME          PRODCATEGORY
2           Tooth Brush       Dental       


Step-2 [ Control Flow Setup ]                
Drag a Data Flow Task in the Control flow tab.



Step-3 [ Creating a Package level variable ]
Go to the Menu bar àSSIS àVariables



In the variable window click on the add variable.
Name              : var_ProductId
Scope              : Package level
Data type        : Int32
Value               : 2



Step-4 [ Editing the OLE DB Source ]
Right click the Data Flow Task and select the Edit, it will open the Data Flow tab. Right click the OLE DB Source and open the OLE DB Source Editor.
In OLE DB Source Editor Select the OLE DB Connection manages. In our case it is "JOYDEE-PC.TEST_DB". In the Data Access mode select the SQL Command. In the SQL Command text writes down the following code.

EXECUTE dbo.sproc_FindProduct ?

The "spproc_DindProduct" is our stored procedure name and the "?" is used for passing parameters.



Step-5 [ Stings the parameters for our Stored Procedure ]
In the OLE DB Source editor to set the parameters just click on the "Parameters …" button. The Set Query Parameter dialog appears.



As we have single parameters in our SP, so we are using single "?" in SQL command with the name of our stored procedure hence the Set Query Parameter dialog appears with Parameter 0.

Then in the valuables section we select the User::var_ProductId the variable that we make before and in Parameters we provide the Stored procedure Parameter name. In our case it is @var_ProductId.



EXECUTE [dbo].[sproc_FindProduct]   ?


Important Note
A lot of developer discuss about this error mentioned bellow

The SQL command requires a parameter named "@ParameterName", which is not found in the parameter mapping.

This error came due to the developer not perfectly provide the value in the Set Query Parameter value window. There are two columns in Set Query Parameter value dialog box mentioning bellow.

Parameters
Variables
This must be the stored procedure parameter. In our case it is @var_ProductId
This must be the package level variable that we created earlier. In our case it is var_ProductId


Step-6 [ Flat file Destination and run the Package]
Now drag a flat file destination and drag the OLE DB Source data flow path in it. Then configure the flat file destination.
Here I assume that you know to configure the flat file destination. If you need guidance please follow my previous post.



The final output in text file



Hope you like it.


Posted by: MR. JOYDEEP DAS

Tuesday 23 April 2013

Version Controlling of Database Object

Introduction

I got a lot of publish related to source code version controlling. But not get any database object version controlling posts.
To understand what i mean to say please read this case study.

Case study
Take a scenario that we have 10 clients and we maintain the same database objects for all of our clients. We need some version controlling for our client database objects. Only the higher version script of DB objects can be compiled and create object in the database not the lower version.

What to do
Here we are using SQL server extended property to solve this problem. In our case we are taking Trigger as a database object.

Creating version control SP

-- [ Version Control Procedure ]
IF OBJECT_ID('UP_VERSIONCONTROL') IS NOT NULL
   BEGIN
     DROP PROCEDURE UP_VERSIONCONTROL;
   END
GO  
CREATE PROCEDURE UP_VERSIONCONTROL
  (
    @param_Type       VARCHAR(1)   = NULL, -- A-Addition D-Deletin U-Updation
    @param_TblObject  VARCHAR(MAX) = NULL,
    @param_TrgObject  VARCHAR(MAX) = NULL,
    @param_Version    VARCHAR(50)  = NULL
   )
AS
DECLARE @v_VersionDescr sql_variant;
BEGIN
   SET @v_VersionDescr = @param_Version;
  
   IF ISNULL(@param_Type,'')= 'A'
      BEGIN 
                 EXEC sys.sp_addextendedproperty
                           @name = N'MS_DescriptionExample',
                           @value = @v_VersionDescr,
                           @level0type = N'SCHEMA', @level0name = 'dbo',
                           @level1type = N'TABLE',  @level1name = @param_TblObject,
                           @level2type = N'TRIGGER',@level2name = @param_TrgObject;
         END               
   ELSE IF ISNULL(@param_Type,'')= 'D'  
           BEGIN
              EXEC sp_dropextendedproperty
                   @name = N'MS_DescriptionExample',
                   @level0type = N'SCHEMA', @level0name = 'dbo',
                   @level1type = N'TABLE',  @level1name = @param_TblObject,
                              @level2type = N'TRIGGER',@level2name = @param_TrgObject;
           END
        ELSE IF ISNULL(@param_Type,'')= 'U'
                BEGIN
                   EXEC sp_dropextendedproperty
                                         @name = N'MS_DescriptionExample',
                                         @level0type = N'SCHEMA', @level0name = 'dbo',
                                         @level1type = N'TABLE',  @level1name = @param_TblObject,
                                         @level2type = N'TRIGGER',@level2name = @param_TrgObject;
                                        
                              EXEC sys.sp_addextendedproperty
                                         @name = N'MS_DescriptionExample',
                                         @value = @v_VersionDescr,
                                         @level0type = N'SCHEMA', @level0name = 'dbo',
                                         @level1type = N'TABLE',  @level1name = @param_TblObject,
                                         @level2type = N'TRIGGER',@level2name = @param_TrgObject;            
                END 
             ELSE IF ISNULL(@param_Type,'')= 'R'
                     BEGIN
                         SELECT * FROM fn_listextendedproperty('MS_DescriptionExample',
                                                               'SCHEMA',
                                                               'dbo',
                                                               'TABLE',
                                                               @param_TblObject,
                                                               'TRIGGER',
                                                               @param_TrgObject);
                                                               
                     END
END  

Create the Base Table

IF OBJECT_ID('TBL_TESTING') IS NOT NULL
   BEGIN
     DROP TABLE TBL_TESTING;
   END
GO  
CREATE TABLE TBL_TESTING
       ( ID     INT           IDENTITY PRIMARY KEY NOT NULL,
         STNAME VARCHAR(50)   NOT NULL,
         MARKS  DECIMAL(20,0) NULL);
GO

INSERT INTO TBL_TESTING
            (STNAME)
VALUES ('JOYDEEP'),('RAJESH'),('RAHIM');           
GO

Creating Trigger Object

-- [ Creating Trigger Objects ]

DECLARE @VERSION_NO DECIMAL(20);
SET @VERSION_NO = 200;

IF OBJECT_ID('TRG_TESTING') IS NOT NULL
   BEGIN
     IF OBJECT_ID('TMP_VERSIONCTRL') IS NOT NULL
        BEGIN
           DROP TABLE TMP_VERSIONCTRL;
        END
     CREATE TABLE TMP_VERSIONCTRL
          (TYPEOFOBJECT    sql_variant,
           NAMEOFOBJECT    sql_variant,
           OBJECTNAME         sql_variant,
           VERSIONNO       sql_variant);
          
     INSERT INTO TMP_VERSIONCTRL
     EXEC UP_VERSIONCONTROL
                @param_Type       = 'R',
                @param_TblObject  = 'TBL_TESTING',
                @param_TrgObject  = 'TRG_TESTING'
               
    
     IF NOT EXISTS(SELECT * FROM TMP_VERSIONCTRL)
        BEGIN
           DROP TRIGGER TRG_TESTING;
        END
     ELSE
        BEGIN 
           IF (SELECT CONVERT(DECIMAL, VERSIONNO) FROM TMP_VERSIONCTRL) < @VERSION_NO
              BEGIN
                DROP TRIGGER TRG_TESTING
              END
           ELSE
              BEGIN
                PRINT 'Can NOT detete Trigger as Version control restricts for Lower version Install'
              END  
        END   
   END
GO 

CREATE TRIGGER TRG_TESTING
   ON  TBL_TESTING
   AFTER INSERT
AS
BEGIN
  IF EXISTS(SELECT * FROM inserted)
     BEGIN
                UPDATE TRG_TESTING SET MARKS = 50
                WHERE  ID=(SELECT ID FROM inserted);
     END
END
GO
-- Contain When the Trigger run first time
EXEC UP_VERSIONCONTROL
     @param_Type       = 'R',
     @param_TblObject  = 'TBL_TESTING',
     @param_TrgObject  = 'TRG_TESTING',
     @param_Version    = '100'
    
GO

To understand the above T-SQL please read it carefully. It's self explanatory.

Hope you like it.

Posted by: MR. JOYDEEP DAS

Monday 22 April 2013

Examples SSIS (Part-1) With Mr. Joydeep Das (E-Book)


Hello Friends,

The e-learning material for Microsoft SQL Server Integration services (SSIS) is ready for download.


Hope you like it and it will be helpful in your profession life.

Download details

Name of e-material : Examples SSIS (Part-1) With Mr. Joydeep Das
Upload date             : 22-04-2013




Thursday 18 April 2013

SSIS Data Types

Introduction

Data types are very important factors when we work with SSIS. As we know very well about MS SQL Server data types but when we learning SSIS, sometimes we are confuse about data types of SSIS.

As the SSIS data types are different from the SQL Server data types because it not only supports the T-SQL but also supports others database systems such as Jet, DB2, and Oracle. With this it must supports Excel spreadsheets, comma-separated values (CSV) files, text files, directory services, and other sources.

Category of SSIS Data Types
We can divide the SSIS data types into following category

Numeric: Types that support numeric values formatted as currencies, decimals, and signed and unsigned integers. SSIS supports more numeric types than any other kind.

String: Types that support ANSI and Unicode character strings.

Date/Time: Types that support date values, time values, or both in various formats.

Binary: Types that support binary and image values.

Boolean: A type to handle Boolean values.

Identifier: A type to handle globally unique identifiers (GUIDs).

Comparing SSIS and SQL Server data types
The bellow chart compares the SQL Server and SSIS data type to understand it properly.

 SSIS Data Types
SSIS Expression
SQL Server
single-byte signed integer
(DT_I1)

two-byte signed integer
(DT_I2)
smallint
four-byte signed integer
(DT_I4)
int
eight-byte signed integer
(DT_I8)
bigint
single-byte unsigned integer
(DT_UI1)
tinyint
two-byte unsigned integer
(DT_UI2)

four-byte unsigned integer
(DT_UI4)

eight-byte unsigned integer
(DT_UI8)

float
(DT_R4)
real
double-precision float
(DT_R8)
float
string
(DT_STR, «length», «code_page»)
char, varchar
Unicode text stream
(DT_WSTR, «length»)
nchar, nvarchar, sql_variant, xml
date
(DT_DATE)
date
Boolean
(DT_BOOL)
bit
numeric
(DT_NUMERIC, «precision», «scale»)
decimal, numeric
decimal
(DT_DECIMAL, «scale»)
decimal
currency
(DT_CY)
smallmoney, money
unique identifier
(DT_GUID)
uniqueidentifier
byte stream
(DT_BYTES, «length»)
binary, varbinary, timestamp
database date
(DT_DBDATE)
date
database time
(DT_DBTIME)

database time with precision
(DT_DBTIME2, «scale»)
time(p)
database timestamp
(DT_DBTIMESTAMP)
datetime, smalldatetime
database timestamp with precision
(DT_DBTIMESTAMP2, «scale»)
datetime2
database timestamp with timezone
(DT_DBTIMESTAMPOFFSET, «scale»)
datetimeoffset(p)
file timestamp
(DT_FILETIME)

image
(DT_IMAGE)
image
text stream
(DT_TEXT, «code_page»)
text
Unicode string
(DT_NTEXT)
ntext

References

Hope you like it.


Posted by: MR. JOYDEEP DAS