Month: September 2008

SSIS, write message for interacting with the user.

SSIS, Sql Server Integration Services, is a full functional development environment. Unfortunately when we are building a package, before register and release the package, we need to Log, trace or debug the script. This process is a MUST if we are using a script task, a task which give you the availability to work directly with C# or VB.NET.

Package Example.

Create a simple SSIS project and insert a Sequence Container, then in the sequence container insert a Script Task and choose the C# language.

script log

Please don’t take care about my screenshot, I know the package fails, but because I toke a screenshot and I’m lazy …
This is just an example, ok?




Now let’s create a couple of Global Variables in our package and let’s call it: VARIABLE01 and VARIABLE02.

Finally let’s go to edit our script task (right click on the task, edit script in the properties).

Some C# code.

First of all in out package class let’s go to create a function that return our variable value.

   1: private string ReturnVariableValue(string name)
   2: {
   3:     string value = string.Empty;
   4:     value = (string)Dts.Variables[name].Value;
   5:     return value;
   6: }

And now, let’s write this if block in the main routine:

   1: if(ReturnVariableValue("VARIABLE01") == string.empty
   2: {
   3:     WriteLog(string.format("The variable value is {0}",
   4:       ReturnVariableValue("VARIABLE01"));
   5:     Dts.TaskResult = (int)ScriptResults.Success;
   6: }else{
   7:     WriteLog("The variable is empty!!");
   8:     Dts.TaskResult = (int)ScriptResults.Success;
  10: }

Now we are ready to view how we can interact with the users in the SSIS package.
It’s very important that in the main routine you assign a result for the package

execution, don’t forget!!

Write to the Log, to the Output or to the Windows.

Well, now we need to build our WriteLog function. We have 3 way to do that.

The first one is to write directly on the Log:

   1: Dts.Log(message, 999, null);

The second one is to write to the Output, so when you will run the package in the Business Intelligence IDE, you will see the message in the output console:

   1: Dts.Events.FireInformation(
   2:   -1, 
   3:   "Check Variables", 
   4:   message, 
   5:   string.Empty, 
   6:   -1, 
   7:   ref False);

Or you can send a MessageBox:

   1: System.Windows.Forms.MessageBox.Show(message);

Code Snippet, the best!

If you have a technical blog like me, one of the most frustrating thing is the ability to copy and paste well formatted code into your blog. I have found a lot of JavaScript plug-in but no one works as I want.

Today I have found on windows live gallery a cool plug-in for windows live writer, Code Snippet.

This plug-in is written in C# and works very well with Windows Live Writer and SubText. I show you some example.


As you can see from the previous picture, it’s a very useful plug-in for a NET blogger. You can:

  1. View different color for alternative lines and choose the color and the border line
  2. View the rows number
  3. Embed the CSS in the PRE tag or use your blog style

Cool Plug-in, in my opinion the best found on the web until today!!

Beginning ASP.NET MVC

Finally is out and I’m a friend of the Author (Simone Chiaretta)!!


ISBN: 978-0-470-43399-7


500 pages

March 2009


Beginning ASP.NET MVC is for developers who have .NET and ASP.NET experience, but want to enhance their level of knowledge and need to learn about the MVC framework. The book is simple and basic in its approach, because it allows readers to learn the concepts in a straightforward, uncomplicated way, but it still assumes a level of programming background and knowledge. This appeals to those who don’t want to get bogged down in learning ASP.NET, but need to know how to get the most out of ASP.NET MVC. The book covers all the main topics about ASP.NET MVC, and applies all of the latest Microsoft technologies to demonstrate the benefits of its usage. 

The book covers these key topics:

The concept of Test-Driven Development and unit testing
The principles of MVC pattern and the role of MVC pattern in TDD
An introduction to ASP.NET MVC and reasons to have such a new technology
How MVC pattern is implemented in ASP.NET MVC
A detailed discussion about the main elements of ASP.NET MVC including model, view, controller and routing
A detailed discussion about the main classes in ASP.NET MVC and how the abstraction and isolation is achieved for them
How to unit test an ASP.NET MVC application
Separate topics for authentication, authorization and AJAX
How to move from traditional ASP.NET webforms to ASP.NET MVC
Case studies to show the discussed topics in a practical and applicable way

You can buy a pre-sell on at this address.

SQL Server, how to implement FULL TEXT search.

One of the most useful function that I like in SQL Server is the FULL TEXT search. When you have a table or some tables with string fields, like VARCHAR or NVARCHAR, you know that running LIKE clause is CPU consuming.

In order to run this example you must before activate the FULL TEXT search capability in your SQL engine. To do that:

  1. Open the SQL Server surface area configuration.
  2. Open the Services and Connections page.
  3. Configure the service to be enable.

Now you must run this Stored Procedure in order to activate the service in the Management Studio:

   1: EXEC sp_fulltext_database 'enable'

Last step is to activate the service for the tables, fields you want to use.

  1. Right click on the table and choose: Full text index>>define new …
  2. Define the columns that will take part in the index, the schedule and the file group (consider a separated file group for big data)

Now we can run our examples. We will use the two clause for implementing a FULL TEXT search, the CONTAINS and the FREETEXT.

Please refer to the previous post for the table schema ().

Using the CONTAINS clause.

The CONTAINS function search the exact word matches and word prefix matches. Let’s go to search all the addresses with the word Street.

   1: SELECT 
   2:     E.FirstName, E.LastName, A.Street
   3: FROM 
   4:     Employee AS E INNER JOIN Address AS A
   5: ON
   6:     E.Employee_id = A.Employee_fk
   7: WHERE CONTAINS(A.Street, 'Street')

And this is our result:

   1: Carl    Mark            Main Street
   2: Carl    Mark            Front Street
   3: Pier    Francoise       Red Street
   4: Mary    Stevenson       White main street

In this case with the CONTAINS clause we will get all the rows where there is a word Street but not the rows where the word Street  is inside another word like: MainStreet. To do that we need this syntax:

   1: WHERE CONTAINS(A.Street, '"*street"')

If you note, you need a double quote and a * before to find everything with street at the end.

Using the FREETEXT clause to get more results.

If you need to match all the result with the street word inside and similar results, you need to use the FREETEXT clause.

With the freetext clause, SQL generates various forms of the search term, breaking single words into parts.

   1: SELECT 
   2:     E.FirstName, E.LastName, A.Street
   3: FROM 
   4:     Employee AS E INNER JOIN Address AS A
   5: ON
   6:     E.Employee_id = A.Employee_fk
   7: WHERE FREETEXT(A.Street, 'street')

The result here will be:

   1: Carl    Mark         Main Street
   2: Carl    Mark         Front Street
   3: Pier    Francoise    Red Street
   4: Pier    Francoise    mainstreet

SQL server, write correlated subqueries.

A subquery is a query that is nested into another query. When you work with T-SQL you have different ways to use subqueries, and every method has different performance results.

Our example is a Database with two tables, take a look on the image below:


 In this case we have a main table Employee with a one-to-many relation to the Address table.

Where value IN. (non correlated example)

The first query I want to show you is:

   1: SELECT 
   2:     E.FirstName, E.LastName
   3: FROM 
   4:     Employee AS E
   5: WHERE 
   6:     E.Employee_id IN
   7: (SELECT 
   8:     A.Employee_fk 
   9: FROM Address AS A)

In this case the SELECT will search all the Employee id in the address table and will return a list of id used by the first query to show the employee found. In this case we will not view the employees without and address. This is a non correlated example, because SQL will search before in the Address table, without know nothing about the employee table, than it will match the list of id with the employee table.

Where value EXISTS. (correlated example)

The second query will use the EXISTS clause:

   1: SELECT 
   2:     E.FirstName, E.LastName
   3: FROM 
   4:     Employee AS E
   6: (SELECT 
   7:     A.Employee_fk 
   8: FROM 
   9:     Address AS A
  10: WHERE 
  11:     E.Employee_id = A.EMployee_fk)

In this case the query is using a correlated value from the outer query. The inner query match the id’s with the id’s in the outer query.

The performance for this queries are totally different if you are working with thousand of rows.

To understand which one is better, you should run both queries with an execution plan and try to build the right clustered index to run the most performing query.