SSIS Variables

Introduction

Using variable is an important factor of all SSIS Developers. We must understand how we use the variables and the scope factors of the variable. In this article I am trying to discuss about it. Hope it will be informative. Here we are using MS SQL Server 2008 BIDS.

From where we find Variable Window

1.     SSIS Menu à Variables

2.    Right click the work area and select the Variables from the shortcut menu.

Type of Variables

SSIS supports two types of variables: user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. We can create as many user-defined variables as a package requires, but we cannot create additional system variables.

Variables Window

The columns name of the variables window are described bellow

Columns Name	Description
Name	Here we specify the name of variable.
Scope	To create a variable, first decide on the scope of the variable, whether it is the entire package, a container or a specific task. A variable is created within the scope of a package or within the scope of a container, task, or event handler in the package. Because the package container is at the top of the container hierarchy, variables with package scope function like global variables and can be used by all containers in the package. Similarly, variables defined within the scope of a container such as a For Loop container can be used by all tasks or containers within the For Loop container. If a package runs other packages by using the Execute Package task, the variables defined in the scope of the calling package or the Execute Package task can be made available to the called package by using the Parent Package Variable configuration type.
Data Type	It specifies the data type of the variables. SSIS supports the following data types. ·         Boolean ·         Byte ·         Char ·         DateTime ·         DBNull ·         Double ·         Int16 ·         Int32 ·         Object ·         SByte ·         String ·         UInt32 ·         UInt64
Values	The value of a user-defined variable can be a literal or an expression. A variable includes options for setting the variable value and the data type of the value. The two properties must be compatible: for example, the use of a string value together with an integer data type is not valid.
Namespace	Integration Services provides two namespaces, User and System. By default, custom variables are in the User namespace, and system variables are in the System namespace. We can create additional namespaces for user-defined variables and change the name of the User namespace, but we cannot change the name of the System namespace, add variables to the System namespace, or assign system variables to a different namespace.
Raise Changed Event	When the property is set to True, the On Variable Value Changed event is raised when the variable changes value.

The Tools Bar of Variables Window

Toolbar	Descriptions
Add Variable	Adds a user-defined variable.
Delete Variable	Deletes the selected user-defined variable.
Show System Variables	Toggles between a list that includes system variables and one that does not. User-defined variables are blue, and system variables are gray.
Show All Variables	Toggles between a list that includes all variables and one that includes only those variables within the scope of the package or the selected container or task. The list will include system variables only if the Show System Variables option is selected.
Choose Variable Columns	Launches the Choose Variable Columns dialog box, where you can select which information is shown in the Variables pane

Populate Variables Using Execute SQL Tasks

Drag and drop the Execute SQL Tasks in the work area of Control flow tabs. Right click the Execute SQL Tasks and select edit.

General Tab:

In the General Tab of the Execute SQL Task Editor Select our Connection to the database where we will run your SQL Statement and enter your SQL statement. The following property of the bellow mentioned figure is important.

On the Result Set tab:

In the Result Name key in the name of the field being return in your SQL Statement.  In the Variable Name select the user-define variable that we created.

Using Variables in SQL Statements

Drag and drop the Execute SQL Tasks in the work area of Control flow tabs. Right click the Execute SQL Tasks and select edit.

General tab:

Select our Connection to the database where you will run your SQL Statement. Enter our SQL Statement and placing a ? where each variable is to be used.

On the Parameter Mapping tab: 

Select the user-define variables in the Variable Name drop down which corresponded to the first variable (?) being used in your SQL Statement. Set the Data Type to NVARCHAR, since we are using a string variable. Set the Parameter Name to 0.  This indicates the order in which the parameter is appearing in the SQL statement.  We can enter them in the pane in any order we want, but the Parameter Name must contain numbers starting from 0 to however many parameters we have minus one.  So if we have two parameters we must have one Parameter Name = 0 and one Parameter Name = 1.  You can reverse the order, but they must both exist.

References

http://msdn.microsoft.com/en-IN/library/ms141085.aspx

Hope you like it.

Posted by: MR. JOYDEEP DAS

UDF within CHECK Constraint

Here I am trying to demonstrate the uses of User define Function (UDF) within CHECK Constraint.

For this I am taken an example case study. Here we have a table named T_STOCK. It contains columns like.

IDNO	INT	Primary Key
RATE	DECIMAL(20,2)
AGREEDRATE	DECIMAL(20,2)

 The CHECK Constraint must check the AGREEDRATE< RATE. But in the business logic for some free sample the RATE should be Zero. If this constraint presents the Insert statement not allows inserting records. To get this Insert we are using a function named fnCHECKRATE. It checks that if the RATE is zero it allows the Insert records and if REATE is > 0 than it check with AGREEDRATE by this condition AGREEDRATE< RATE.

-- Craeting the Base Table --

IF OBJECT_ID('T_STCOK') IS NOT NULL

   BEGIN

     DROP TABLE T_STCOK;

   END

GO

CREATE TABLE T_STCOK

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

        RATE       DECIMAL(20,2) NOT NULL,

        AGREEDRATE DECIMAL(20,2) NOT NULL);

       

GO

-- Creating Function for CHECK constraint --

IF OBJECT_ID('fnCHECKRATE') IS NOT NULL

   BEGIN

      DROP FUNCTION dbo.fnCHECKRATE;

   END

GO  

CREATE FUNCTION dbo.fnCHECKRATE

(

     @v_RATE       DECIMAL(20,2),

     @v_AGREEDRATE DECIMAL(20,2)

)

RETURNS BIT

AS

BEGIN

     DECLARE @v_RETVAL BIT;

     SET @v_RETVAL = 0;

    

     IF @v_RATE <=0

        BEGIN

           SET @v_RETVAL = 1;

        END

     ELSE

        BEGIN

           IF @v_AGREEDRATE < @v_RATE

              BEGIN

                 SET @v_RETVAL = 1;

              END

           ELSE

              BEGIN

                 SET @v_RETVAL = 0;

              END  

        END

       

     RETURN @v_RETVAL;

END

GO

-- Create Constraint with Function Call --

IF NOT EXISTS(SELECT *

              FROM   sys.sysconstraints

              WHERE  id = OBJECT_ID('T_STCOK')

                     AND constid = OBJECT_ID('cons_Chk_RATE'))

     BEGIN

           ALTER TABLE T_STCOK

           ADD CONSTRAINT cons_Chk_RATE CHECK (dbo.fnCHECKRATE(ISNULL(RATE,0), ISNULL(AGREEDRATE,0))=1)

    END

GO

-- Inserting Records --

INSERT INTO T_STCOK

       (RATE, AGREEDRATE)

VALUES (20, 5);

(1 row(s) affected) 

INSERT INTO T_STCOK

       (RATE, AGREEDRATE)

VALUES (5, 20); 

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "cons_Chk_RATE". The conflict occurred in database "ATS", table "dbo.T_STCOK".

The statement has been terminated.

INSERT INTO T_STCOK

       (RATE, AGREEDRATE)

VALUES (0, 5);

 

(1 row(s) affected)      

Hope you like it.

Posted by: MR. JOYDEEP DAS

SSIS Precedence Constraints

Introduction

The SSIS control flow defines the workflow for that package. It determines the order in which tasks and containers will execute. In certain condition the tasks will run only when a set of defined conditions are met.

Precedence constraints link the individual tasks together and determine how the workflow moves from one task to another. Precedence constraints are only used with Control flow Tasks and not with Data Flow. The connections between objects within the control flow are done by precedence constraints and in data flow are data path.

As per MSDN

"Precedence constraints link executables, containers, and tasks in packages into a control flow, and specify conditions that determine whether executables run. An executable can be a For Loop, Foreach Loop, or Sequence container; a task; or an event handler. Event handlers also use precedence constraints to link their executables into a control flow.

A precedence constraint links two executables: the precedence executable and the constrained executable. The precedence executable runs before the constrained executable and the execution result of the precedence executable may determine whether the constrained executable runs. The following diagram shows two executables linked by a precedence constraint."

So now we can understand how important the Precedence constraints are. In this article I am trying to discuss about it.

Precedence constraints of Control Flow Tab

Here I provide a pictorial representation of Control flow tabs with different precedence constraint.

In the above, please look that the some lines are solid, some lines are dashed and some have function symbols (Fx).  Each of the line represents the different type of implementation of Precedence constraints.

Solid Line

Here the Solid line represents the logical AND Precedence that means that the tasks will run only when all the constraints condition are met.

Dashed Line

The dashed line represents the logical OR Precedence that means that the tasks will run either of the Precedence constraints condition is met.

Function Symbols

The Function symbols represent the SSIS expression that has been embedded in the constraint for evolutions.

Precedence constraints Editors

Within the control flow we can edit the Precedence constraints by double click the constraint or by right click and then select edit.

When we look at the Precedence constraints editors there are two sections

1.    Constraint Operations

2.    Multi Constraint

Constraint Operation

Constraints are simply the execution status of the upstream tasks.

Evaluation Operators

This dropdown contains

Constraint: 

The precedence constraint is evaluated solely on the option selected in the Value property. For example, if you select Constraint as the Evaluation operation option and select Success as the Value option, the precedence constraint will evaluate to true only if the precedence executable runs successfully. When the precedence constraint evaluates to true, the workflow continues and the constrained executable runs.

Expression: 

The precedence constraint is evaluated based on the expression defined in the Expression text box. If the expression evaluates to true, the workflow continues and the constrained executable runs. If the expression evaluates to false, the constrained executable does not run. Please note that when the Expression option is selected, the Value property is greyed out.

Expression and Constraint: 

The precedence constraint is evaluated based on both the Value property and the expression. Both must evaluate to true for the constrained executable to run.

Expression or Constraint: 

The precedence constraint is evaluated based on either the Value property or the expression. At least one of these properties must evaluate to true for the constrained executable to run.

Multiple Constraints

If the constraint tasks have multiple constraints, we can choose how the constraint interoperates to control the executions of the constrained tasks.

Logical AND: All precedence constraints that point to the constrained executable must evaluate to true in order for that executable to run. This is the default option. If it is selected, the arrow is solid.

Logical OR: Only one precedence constraint that points to the constrained executable must evaluate to true in order for that executable to run. If this option is selected, the arrow is dashed.

ShowAnnotation Property of Precedence constraints

Specify the type of annotation to use. Choose Never to disable annotations, AsNeeded to enable annotation on demand, ConstraintName to automatically annotate using the value of the Name property, ConstraintDescription to automatically annotate using the value of the Description property, and ConstraintOptions to automatically annotate using the values of the Value and Expression properties.

Example to understand the Precedence constraints

Let's take a simple example to understand the Precedence constraints with Expression.

Step-1 [ The Case Study ]

Here we have 3 Tasks Task-A, Task-B and Task-C. The Task-A Execute a SQL Statement and determines that number of records exists in the Table objects named "MY_EMPDETAILS". If there is no records exists the SQL statements return 0. If there is no records found after execution of Task-A, the execution flows moves to Task-C else Task-B. The case study is diagrammatically representing bellow.

Step-2 [ Create the Base Table and Insert Some Records ]

IF OBJECT_ID('MY_EMPDETAILS') IS NOT NULL

BEGIN

  DROP TABLE MY_EMPDETAILS;

END

GO 

CREATE TABLE MY_EMPDETAILS

   ( EMPID    INT          NOT NULL IDENTITY PRIMARY KEY,

     EMPNAME  VARCHAR(50)  NOT NULL,

     DEPT     VARCHAR(25)  NOT NULL);

GO

INSERT INTO MY_EMPDETAILS

       (EMPNAME, DEPT)

VALUES ('JOYDEEP DAS', 'Team Lead'),

       ('SUDIP DAS', 'Manager'),

       ('TUHIN SHINAH', 'Developer'),

       ('SANGRAM JIT', 'SR.Developer'),

       ('DEBAYAN BISHWAS', 'Developer'),            

       ('RUHOOL AMIN SARKAR', 'SR.Developer');

Step-3 [ Open the BIDS and Create the Package Scope Variables ]

The variables of the SSIS have different scope. Here we must create the Package Scope variable and it is important.

To create SSIS variables go to SSIS menu à Variables

Or right click on the work area of control flow tabs and select Variables.

Here I am creating a variable with the name of v_EmpCount with Package level scope.

Step-4 [Designing Control Flow ]

Using three Execute SQL task, design the control flow like bellow

Step-5 [Editing Task-A Execute SQL Statement ]

Here the marked properties are very important and we must provide value on them. In the SQL Statement property write-down the following SQL statement.

SELECT  CONVERT(INT, COUNT(EMPID)) AS TOTALCOUNT

FROM    MY_EMPDETAILS

Step-6 [ Now open the Precedence Constraint Editor ]

Double click the precedence constraint or right click and edit. In the Expression section writes this

@[User::v_EmpCount]>0

Follow the above steps for Task-B and Task-C and other precedence constraint exression write @[User::v_EmpCount] == 0. From the property

Step-7 [ Set the Precedence Constarint propery ]

Execute the Package

Hope you like it.

Posted by: MR. JOYDEEP DAS

Database Unit Testing

Introduction

As database developer we always must ensure that all the database objects are working perfectly and provide output as expected.  But in my professional life I see most of the junior developers are not taken care of that, result unnatural output and not working properly. Here the unit testing of database is very important for each and every person related to database development. I think a junior database developer must understand what I want to say. In this article I am going to discuss about the unit testing of database.

What is unit testing

First we must understand what the unit testing is. If I am going to a bookish definition of unit testing it is a method by which individual units of source code, sets of one or more modules together with associated control data, usage procedures, and operating procedures, are tested to determine if they are fit for use. Here the fit for use is the main key factors. We must question our self that is it really fit for our application or application architecture.

Type of Unit testing

There are two type of unit testing. The Black box unit testing is Inspects from outside and the White box unit testing related to tool under the cover.

Database Unit testing

Database unit testing is used for feature testing of our individual modules like stored procedures, triggers or user defined functions etc. it ensure that subsequent changes of database is not losing my objects functionality.

To write unit tests, we first need to create an isolated database development environment that is separate from our production database and we must take care of the following underlying points.

1.    Create a database project.

2.    Import the schema from your production database to that project.

3.    Deploy the database project to create a database that uses the same schema as the production 

     database.

4.    Populate that database with test data.

Test Condition

It's a pre-defined test conditions to test things. For an example number of rows returned in a result set. For performance point of view the example is the total amount of time that is required to execute.

Each pre-defined test condition has one Assert method which is defined on the base Test Condition class and it is called when evaluating a test condition.

Database Unit Test Case by Visual Studio

Please note that the Database Unit Testing is only supported for VS2010 Premium and Ultimate editions.

You can find a good example from this MSDN link. The blog is written by Atul Verma.

http://blogs.msdn.com/b/atverma/archive/2010/07/28/how-to-unit-test-sql-server-2008-database-using-visual-studio-2010.aspx

 Hope you like it.

Posted by: MR. JOYDEEP DAS