Monday, September 29, 2008

SSIS Package Deployment Utility

For deploying packages created by business analysis developers in SQL Server Integration Services, Business Intelligent development studio introduces Package Deployment Utility. This deployment utility can create a deployment package by means of which you can deploy your package in:

1. File System
2. SQL Server job

Just take properties of your project and go to Deployment Utility tab.

Set the "Create Deployment Utility" as "True" and specify the "Deployment Path".

As soon as you build your project deployment utility is created in the above specified folder with the package file. The file type of Deployment Utility is "Integration Services Deployment Manifest". The extension of the deployment package is "*.SSISDeploymentManifest".

When you run this manifest file. The package deployment wizard is started which helps in deploying the package.

As discussed above, you can also specify the deployment destination for our SSIS package.

If you choose to install in the file system then you just have to specify the destination folder and start the wizard. If you choose otherwise and install in the SQL Server instance, then you have to specify the SQL Server instance in which we want to install this package.

Wednesday, September 24, 2008

SSIS Unit Testing Framework

Hi guyz,

I have always been displeased being not able to unit test my packages
created in SSIS. This was because there was no way to do this.

But the days of darkness are gone. SQL Server Integration Services
Community has felt this need of the day and has come up with a Unit Testing
Framework. Now guyz! if you are not from development background even then
you don't need to worry because no .net code has to be written to write
your unit tests. All you have to create is an XML files with your commands.
So you don't need to know any .net code. Still I have not found out if I
want to write some .net code then how can I do that.

The thing I liked most is that this Unit Test framework is based on xUnit.
It means it follows the same setUP, test. and tearDown flow. There is also
support for Test Suites. The developers of Microsoft Dynamics AX should be
happy to listen to this news.

To keep ease in consideratino, a GUI has been added to create Unit Tests.

I am copying a sample XML used for this framework from codeplex.

You can download it from the following link:
SSIS Unit Testing Framework

You can see that it is just version 1.0 release in Aug, 2008. So
don't expect it to be all bug free.

You would be glad to know that the utility is available as .

So I can't wait to put my hands on this. Share your experience with me
about this.


Saturday, September 13, 2008

Grouping Set (SQL Server 2008)

People cannot help appreciating GROUP BY clause whenever they have to get a DISTINCT from any result set. Additionally whenever any aggregate function is required GROUP BY clause is the only solution. There has always been requirement get these aggregate function based on different set of columns in the same result set. It is also safe to use this feature as this is an ISO standard.

Though the same result could be achieved earlier but we have to write different queries and would have to combine them using UNION operator. The result set returned by GROUPING SET is the union of the aggregates based on the columns specified in each set in the Grouping set.

To understand it completely first we create a table tbl_Employee.

Now we populate table with some the following rows:

After populating with the rows, we select some rows using Grouping Sets.

The result of this statement is as follows:

You can see that the result set contains rows grouping by each set in the specified Grouping Sets. You can see the average salary of employees for each region and department. You can also appreciate the average salary of employees for the organization (NULL for both Region and Department). This was the result of empty Grouping Set i.e. ().

Before 2008, if you had to get the same result set, following query had to be written:

By looking at the above query, you can appreciate the ease provided by Grouping Set to the developer.

CUBE Sub Clause for Grouping
This is used to return power ‘n’ to 2 for ‘n’ elements.

The above query is equivalent to the following query:

ROLLUP operator for Grouping
This is used to return ‘n+1’ grouping sets for ‘n’ elements in the hierarchy scenario.

This is equivalent to the following query:

Thursday, September 11, 2008

Extended Stored Procedures (From C, C++ DLL)

There are three types of stored procedures in SQL Server.
1. Standards Stored Procedures
2. CLR Stored Procedures
3. Extended Stored Procedures

Today we are going to discuss about Extended Stored Procedures. If you are new to SQL Server world (just a few years old in SQL Server environment) then it might be the first time when you have ever heard of this name. So let’s take a look what they are:

There are sometimes requirement to perform operations which are very difficult or impossible to do in SQL e.g. WIN32 operations like killing a system process, writing to the event log, encrypting data to a database, parsing XML, shelling to the command-line. Back in earlier days, we needed something to support such operations. For these types of operations Extended Stored Procedures were introduced in SQL Server earlier. Now you would say: what is its requirement now, when you can write these operations in managed code using CLR Stored Procedures? But just wait a moment and believe that you are living in a world of SQL Server 7. There is no .net framework yet developed. Now answer how would you do this? Your answer must be using C++ DLLs. Yes! This is the same that Extended Procedures were introduced for.

In SQL Server 2005, even with introduction of CLR Stored Procedures, extended stored procedures remained part of the DBMS. But because of this there always existed a backdoor for hackers into your server because these stored procedures were basically unmanaged code and had known issue of Buffer Overflow.

It is good to know that these stored procedures will no more be part of a new version of SQL Server. All of you, who are planning to move to next version of SQL Server after 2008 version, make this part of plan to rewrite all your extended stored procedures in a CLR compliant language. At least what we can do is that we don’t develop any new logic using extended stored procedures and keep porting our earlier extended stored procedures to CLR.


Monday, September 8, 2008

Microsoft Project Zermatt (Claims based security in .net)

Today the most important topic is security whether it is security of the
people's life or it is about their identity. For life saving, we have law
enforcement agencies. For security of identities, different mechanisms do
exist. Microsoft Code Name Zermatt is a step towards the same direction.

In many different applications and APIs there are different ways to build
identity support. Even with .net identity can be seen in different places.
When we have security so widely spread, it is difficult to maintain it. And
that is where hackers and phishers work hard to find a way to get some
place into your application.

Project Zermatt is about developing CLAIMS AWARE applications in .net
environment. It is designed to be simple to understand and open to use.
According to Microsoft, You don't need to be a security expert to implement
security based on Zermatt. A developer should be able to implement its
design based on Zermatt.

Microsoft is talking about identity providers everywhere. In SQL Server it
is doing that with addition of EKM i.e. Enhanced Key Management. Like
there, In Zermatt it has option to add custom identity providers.They are
also referred to as STS i.e. Security Token Services. It provides security
by providing a new CLAIMS MODEL including API support to build custom STS.
These STS implement WS-TRUST protocol.

From the very start of this Project, Micrsoft wanted it to support both B2C
(Business to Consumer) and B2B (Business to Business) Model. For B2C, it
should support based application and for B2C, we mean supporting
WCF based applications.

For building Zermatt based applications or services you need to have .net
framework 3.5.

With Visual Guard, People came to the idea of centralized security. But
still at the time of mergers and acquisitions, it is very difficult to
maintain that idea of centralized security and developers had to look at
two different places to verify the identity of a user. By using Zermatt
across the industry, people will no more have this problem. So Zermatt is
towards a step for providing EXTRA FEDERATION for security.

To understand Zermatt first we need to understand what is CLAIMS BASED
security model? This is becaue it is the essence of Zermatt. for claims
based identity mode, user comes with all the information related to his
identity. Just by looking at this identity information user is
authenticated. Remember that this list of claims is also called SECURITY
TOKEN. They are generally signed by issuing authority to provide legitimacy
to the user.

For claims based security model a user needs a list of claims in order to
be authenticated by application (The application doesn't need to receive
username and password any more). The identity of a person is identified
through these claims. These CLAIMS are provided by some AUTHORITY which
have been assigned this role. So based on this discussion, we have
identified three roles.
1. User
2. Claim provision authority
3- Application

These three roles are named as CLIENT, ISSUING AUTHORITY and RELYING PARTY
(also called CLAIMS AWARE or CLAIMS BASED application) respectively in the
context of Project Zermatt. The issuing authority uses SECURITY TOKEN
SERVICE (STS) to provide tokens to clients. You can use built-in or custom
STS. There is complete framework support provided with Zermatt to create
your custom STS. The clients may be smart (active) or Browser based
(passive) clients.

Microsoft has used several WS-* standards to implement Zermatt for smart
clients. They are as follows:

This is used to retrieve policy by the Relying Party.

This is the standard for defining the structure of the policy.

This describes the working between smart client and Issuing Authority. This
deals with requesting and issuing security token.

For browser based applications these WS-* based standards are replaced by

In order to understand the tokens issued by the Issuing Authority, the
token must be in a standard format. This has been made easier by adopting
SAML (Security Assertion Markup Language) for security tokens by most
STS(s). SAML is a standard XML standard to represent claims. With this XML
based tokens platform independence for security can be achieved far easier.
This platform independence can appear even bigger if you include the
feature of Single Sign On with it.

Saturday, September 6, 2008

Object Search (SQL Server 2008)

It has always been difficult to find out the exact object if we don't exactly know the name or type of an object. In SQL Server Management Studio 2005, it is impossible to search any object because no such option exists in the first place. As a database professional, I don't remember the names of all the objects all the times. I may remember what the name should be like but not the exact name. If I know the exact name, I don't remember whether it is stored procedure or function (if there is no naming convention). To resolve all these problems, I need a search tool within the management studio environment.

SQL Server 2008 seems to answer our prayers by providing 'Object Search'. The feature is available as part of 'Object Explorer Details'. This window can be used independently and also with 'Object Explorer' window. This provides primitive search of database objects in SQL Server 2008. By 'primitive', I mean it has several limitation which would be felt by professionals using this tool.

To search any object, just type in the search bar in 'Object Explorer Details' window. There is wild card character available. This is same i.e. '%', which means zero or more characters. This character has the same behavior as T-SQL.

Object Explorer Details window can run in isolation or with the Object Explorer window.

1. It is not possible to search a particular type of object e.g. you can not search all stored procedures starting from 'CDC_'.

2. It is not possible to search negation e.g. we can not search all the objects which does not contain 'CDC_'.

3. Software professionals are generally comfortable with regular expressions for search. Regular Expressions are not supported for search.

Friday, September 5, 2008

Resource Governor (SQL Server 2008)

I have written an article about Resource Governor. A feature introduced in SQL Server 2008.

Click: SQL Server 2008 Resource Governor

Thursday, September 4, 2008

T-SQL Debugger (SQL Server 2008)

Gone are the days in which you could not debug your code in SQL Server Management Studio. The only option that SQL Server developers had was to add their stored procedures and other programming objects into Visual Studio project and debugging their code from the Visual Studio IDE. With SQL Server 2008, Microsoft has included full-fledged debugger in SQL Server Management Studio.

Like other debugging tools, we can add break-points in our T-SQL code. Pressing F9 would add the break-point as generally is the standard in other Microsoft tools.

There are different windows available for debugging the code. The windows include:
1. 'Local'
2. 'Call Stack'
3. 'Output'
4. 'Command Window'
5. 'Threads'
6. 'BreakPoints'
7. 'Watch'

You can 'Step Into', 'Step Out' or 'Step Over' the code. There are same short-cut keys available as in other Microsoft tools.

The 'Quick Watch' feature is also available. With this feature, you can evaluate any expression evaluated under the environment of execution of current code.

You can also toggle, delete or disable breakpoints.

Enjoy this feature!

SQL Server Agent Proxy

What would you have to do if you want any job step to be executing with a different credential than SQL Agent Service Account. The answer is SQL Server Agent Proxy. I have written a useful article about SQL Server Agent Proxy on codeproject. You can access the article on the following link.
Click: SQL Server Agent Proxy