Thursday, 28 March 2013

SSIS Variables

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
Here we specify the name of variable.
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
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.
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

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.


Hope you like it.

Posted by: MR. JOYDEEP DAS

Monday, 25 March 2013

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.

Primary Key


 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 --

       (IDNO       INT           NOT NULL IDENTITY(1,1) PRIMARY KEY,
        RATE       DECIMAL(20,2) NOT NULL,
-- Creating Function for CHECK constraint --
     @v_RATE       DECIMAL(20,2),
     SET @v_RETVAL = 0;
     IF @v_RATE <=0
           SET @v_RETVAL = 1;
           IF @v_AGREEDRATE < @v_RATE
                 SET @v_RETVAL = 1;
                 SET @v_RETVAL = 0;

-- Create Constraint with Function Call --
              FROM   sys.sysconstraints
              WHERE  id = OBJECT_ID('T_STCOK')
                     AND constid = OBJECT_ID('cons_Chk_RATE'))

-- Inserting Records --
VALUES (20, 5);

(1 row(s) affected) 

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.

VALUES (0, 5);
(1 row(s) affected)      

Hope you like it.

Posted by: MR. JOYDEEP DAS

Wednesday, 20 March 2013

SSIS Precedence Constraints

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

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.

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 ]

     DEPT     VARCHAR(25)  NOT NULL);
       (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.


Step-6 [ Now open the Precedence Constraint Editor ]

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


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

Sunday, 10 March 2013

Database Unit Testing

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

 Hope you like it.

Posted by: MR. JOYDEEP DAS

Saturday, 9 March 2013

SSIS Transaction

Continuing my journey with MS SSIS, now it's time to discuss about SSIS transaction. We have to understand the needs of Transaction and how we use the transaction in SSIS. In this article I am trying to discuss about it.

What the Transaction is
Before going to the SSIS Transaction we have to know some facts related to transaction.
Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. The properties of the transaction are mentioned bellow.

Ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.

Ensures that the database properly changes states upon a successfully committed transaction.

Enables transactions to operate independently of and transparent to each other.

Ensures that the result or effect of a committed transaction persists in case of a system failure.

Type of SSIS Transaction
There are two type of transaction that SSIS supports.

Distributed Transaction
Distributed Transaction uses two phase commit transaction with Distributed Transaction Coordinator (DTC) service. We must sure that this service runs before we run our package that uses the service. We don't have to explicitly state BEGIN TRAN, COMMIT TRAN, ROLLBACK TRAN because they are handled automatically by DTC services.

Native SQL Server engine transaction
This transaction uses explicit BEGIN TRAN/COMMIT/ROLLBACK TRAN, explicitly in Execute SQL Task component. We must setting up the involved connection manager's property RetainSameConnection from false to true.

Where we can set the Transaction in SSIS
Transaction support is built in to SSIS.  The property that we have to set for SSIS transaction is TransactionOption property. The TransactionOption property exists at the package level, container level such as Sequence, For Loop, Foreach Loop etc and also at any Control Flow task such as Execute SQL task, Data Flow task, etc. 

Caution about running MSDTC
The built-in transaction support in SSIS makes use of the Distributed Transaction Coordinator (MSDTC) service which must be running. If we execute an SSIS package that utilizes the built-in transaction support and MSDTC is not running, we will get an error message like the following:

Error: 0xC001401A at Transaction: The SSIS Runtime has failed
to start the distributed transaction due to error 0x8004D01B
"The Transaction Manager is not available.". The DTC transaction
failed to start. This could occur because the MSDTC Service is not running.

Options of TransactionOption properties
There are 3 options for TransactionOption properties are mentioned bellow.

NotSupported: The component will not join to transaction scope.
Supported: The component will join parent's transaction scope if exists
Required: The component will join parent's transaction scope if exists, otherwise it will start new component.

Understand the Checkpoints
When we are working with a complicated and long running SSIS package and we want the ability to restart the package if it fails and have it starts at the point of failure. In others words if we don't want to execute the tasks that are already successful if the package restarted. This is done by establishing check points.

To understand it, let's assume that we have 10 tasks in the package. When the package is executed The Tasks -1 to Tasks -5 runs success fully at Tasks-6 it gives an error. When the package restarted for establishing check points it starts from Tasks -6 not from Tasks-1.
To enabling restart ability within a package required first enabling a package to use check points, and second settings the specific tasks and containers to write checkpoints.

Example of Establishing Transactions
 Here is a simple example to understand the steps behind establishing the Transaction.

Step-1 [ Start the Microsoft Distributed Transaction Coordinator (MSDTC) ]
Control Panel à Administrative Tools à Services Console

Step-2 [ Setting TransactionOption Property to Required ]

Example of Establishing Checkpoints

Step-1 [ Open the Package property window and open Control Flow Tabs ]

Step-2 [ Set the SaveCheckpoints property at the package level to True ]

This property allows Checkpoints to be saved during package execution.

Step-3 [ Set the CheckpointFileName property ]

Here we provide a valid path and fine name of the checkpoint files. The package uses this file to maintain their state information. When the package execution fails, it reads the file to determine the last successful task and start the package execution after that task.

Step-4 [ Set the CheckpointUsage property ]

If this property is set IfExist, which causes the package to run from beginning if the Checkpoint file is not present or to run from the identified point if the file exists.

Hope you like it.

Posted by: MR. JOYDEEP DAS