Cascading IF-ELSE-ELSEIF A.K.A. Nested IF-ELSE Statements in a SSIS Expression

Today it will be a concise post, but I hope quite helpful and informative.

As most SSIS developers know SSIS Expressions are used to drive several key tidbits of a package components as the Conditional Split, Package Variables value assignments, Derived Column Transformation, form a SQL statement for Execute SQL task, drive precedence constraints, set package properties as connection string for example and more. So it is hard to underestimate the importance of the SSIS Expressions for a SSIS developer.

However, the SSIS expressions look hard to use for some beginners yet, feel un-natural to most people. This fact does not undermine the power and flexibility of the SSIS Expression. The only hard to swallow portion in my opinion is the code bloat versa editing space ratio that limits the effectiveness of writing long expressions. Luckily, there is a free tool to help: SSIS Expression Editor & Tester! I do not see any dramatic improvements in regard to the SSIS Expression in the forthcoming SQL Server “Denali”, but it will add a number of features that made me excited, perhaps I shall dedicate a separate post on this subject, but I will mention one: Expression Task. This task will help to assign a result of an expression to a variable. So the SSIS Expressions are here to stay and important to know.

And now I will share the most commonly asked question on MSDN SSIS forum: “how do I handle or process multiple IF-ELSE conditions”?

The answer is it is simple!

As we know, the conditional statement in SSIS Expression has the following notation:

«boolean_test» ? «true_result» : «false_result»

The trick is the right part can accept yet another [nested] IF-ELSE, and then more:

@[User::MyStringVar] == "TEST1" ? "IT WAS TEST1": 

@[User::MyStringVar] == "TEST2" ? "IT WAS TEST2": 

@[User::MyStringVar] == "TEST3" ? "IT WAS TEST3":

"MATCH NOT FOUND"

The end result of running this expression is it will check the MyStringVar contents and try to pick the first match, if the match not found it will produce “Match not found”.

This expression is good for a variable assignment, for a Derived Column transformation one should use:

[MyStringVar] == "TEST1" ? "IT WAS TEST1": 

[MyStringVar] == "TEST2" ? "IT WAS TEST2": 

[MyStringVar] == "TEST3" ? "IT WAS TEST3":

"MATCH NOT FOUND"

It is easy to expand this expression further to more choices by just adding lines before the last assignment.

How to capture a Header or Trailer Count Value in a Flat File and Assign to a Variable

Recently I had several questions concerning how to process files that carry a header and trailer in them. Typically those files are a product of data extract from non Microsoft products e.g. Oracle database encompassing various tables data where every row starts with an identifier. For example such a file data record could look like:

HDR,INTF_01,OUT,TEST,3/9/2011 11:23

B1,121156789,DATA TEST DATA,2011-03-09 10:00:00,Y,TEST 18 10:00:44,2011-07-18 10:00:44,Y
B2,TEST DATA,2011-03-18 10:00:44,Y
B3,LEG 1 TEST DATA,TRAN TEST,N

B4,LEG 2 TEST DATA,TRAN TEST,Y

FTR,4,TEST END,3/9/2011 11:27

A developer is normally able to break the records using a Conditional Split Transformation component by employing an expression similar to

Output1 --  SUBSTRING(Output1,1,2) == "B1"

and so on, but often a verification is required after this step to check if the number of data records read corresponds to the number specified in the trailer record of the file.

This portion sometimes stumbles some people so I decided to share what I came up with.

As an aside, I want to mention that the approach I use is slightly more portable than some others I saw because I use a separate DFT that can be copied and pasted into a new SSIS package designer surface or re-used within the same package again and it can survive several trailer/footer records (!).

See how a ready DFT can look:

The first step is to create a Flat File Connection Manager and make sure you get the row split into columns like this:

After you are done with the Flat File connection, move onto adding an aggregate which is in use to simply assign a value to a variable (here the aggregate is used to handle the possibility of multiple footers/headers):

The next step is adding a Script Transformation as destination that requires very little coding.

First, some variable setup:

and finally the code:

As you can see it is important to place your code into the appropriate routine in the script, otherwise the end result may not be as expected.

As the last step you would use the regular Script Component to compare the variable value obtained from the DFT above to a package variable value obtained say via a Row Count component to determine if the file being processed has the right number of rows.

Age this month calculation in Crystal Reports

I keep having lots of fun developing enterprise reports with the excellent Crystal Reports.

Today I needed to calculate the age of a person for this month. The typical use of this formula would be something like “determine if an employee is turning 65 years old this month).

I was able to come with the following Crystal Formula Syntax:

// Age this month calculation 
WhileReadingRecords;
dateTimeVar _1stOftheMonth := CurrentDate; 
dateTimeVar LastOftheMonth := CurrentDate; 

// Get the run date and make it the 1st day of the current month
_1stOftheMonth := Date(Year(_1stOftheMonth), Month(_1stOftheMonth), 01);

// Get the next month's 1 st day. Subtract 1 day to get the last day of the current month
LastOftheMonth := DateAdd( "d", -1, DateAdd("m", 1, _1stOftheMonth) );

// Proceed with the age calculation taking into consideration the leap year
if (Month(LastOftheMonth) * 100) + Day(LastOftheMonth) >=
   (Month(CDate({Employee_Master.BirthDate})) * 100) + Day(CDate({Employee_Master.BirthDate}))  
then 
    Year (LastOftheMonth) - Year(CDate({Employee_Master.BirthDate}))  
else 
    Year (LastOftheMonth) - Year(CDate({Employee_Master.BirthDate})) - 1;

An interesting nuance about this formula: if one substitutes the CurrentDate (on line 3 and 4)  to an event date of interest (being a certain date or a database field) it is possible to calculate the age on this specific date!

Free SQL IDEs Review – SQL Everywhere vs. Toad for SQL Server

Recently I have been given a short term assignment to develop a number of reports in Crystal Reports.

Upon arriving to the client location I quickly found out that the only tool I will be using to create the reports is basically the Crystal Reports itself.

My initial look at the database quickly revealed that the database structure is far from perfect and that it has some confusing table relationships, another hurdle was in the fact that several columns in various tables were user customizable for values to hold without any hint onto where the value is used and the relationship to other database objects.

I definitely had a challenge figuring out what and how the right SQL queries should be developed. To my dismay the matters were marred even further by my account being read-only so the possibility of creating database diagrams to visualize the data was not feasible.

After some short peering into what links Crystal Reports produces I realized I am lucking a tool that can close the gap.

Just as an aside, every morning I am normally reading a few blogs, one of them I literally read while having my first cup of coffee is an excellent one by Chris Alcock The Morning Brew in which it was just not long ago mentioned about the free tools for SQL Server by Aaron Bertrand. Bingo! I thought, I need to take them for a test drive! Not knowing anything about the tools I decided to download both.

A short wait for the downloads and Toad for SQL Server 5.0 gets installed first. Surprisingly, the free license is only for a few people in an organization (clever!). Well, it impressed me with a clean interface, but cluttered with panes and panels it quickly made me a little slow navigating here and there, my first 5 min and I think these are the most critical ones for the software spent and I see something – Object Explorer:

this must be the one I murmured to myself that has the ability to find an object based on its name. Nice, but it turned out to work only as a filter on the top level objects, so for example if a database is highlighted then applying a filter it will only show objects matching the criterion. Not particularly useful. At least for me, I needed to find where a specific column name is used. Hmm, let’s give SQL Everywhere a try! OK, it is installed in a snap! My first ehh, seconds, I find in View the Object Browser, good, search for my objects, whoa! I got a large set of results to go through! Nice, I got my columns, tables, indexes, functions and what not listed, gosh, even the T-SQL for this object as to be added is shown:

Cool, and besides, in addition a streamlined version is available through the Objects tab on the Explorer pane:

Double-clicking on the column name takes to it in a given table.

Good now we are ready to write some useful SQL queries.

Well, I did not want to give up yet on Toad for SQL Server and went on to writing a complex join. So I am typing in SELE… hmm, I notice the intellisense does not pop up with suggestions, well all right, after all I know how to type, now seems it does not support suggesting on keywords, or it may be my impression. Now when I arrived to the join part to another table the intellisense does show up and tries to guess my join, and to my amusement the choice at the top actually makes sense. Not bad! Now I try to do the same in SQL Everywhere and and start with typing SELE… wow wait what is going on? Ahm three choices appeared: scf, ssf and st100, I hit tab and get SELECT COUNT(*) FROM nice! Similarly ssf expands to SELECT * FROM and SELECT TOP 100 * FROM respectively.

Not a bad productivity improvement for free!

OK, now some ugly T-SQL Code is churned I would be ashamed to copy and paste to a Command in Crystal Reports, let’s beatify it first! OK, I still love Toad, ahm, where SQL Format or something like that is? It is there but disabled, well, turned out it is disabled in SQL Everywhere too, pity! No problem, I can that even without installing a single piece of software, going to http://www.sqlinform.com/online.phtml and whoa I have my long SQL looking good!

I thought I deserve to rest and I started to play around with the settings, surprisingly, both editors offered a lot of customization, Toad even made me laugh by offering a pumpkin skin and a Valentine’s skin:

 

What a jolly amphibian !

(I guess the creators of it thought a developer would change its appearance based on kind of a holiday approaching).

SQL Everywhere sports a more humble approach to its appearance and to me it looks more tasteful and more importantly, practical. These are simply color schemes.

As far as the productivity goes, both tools offer a good code snippets support, though SQL Everywhere has a more functional set of snippets, one thing, it is only very short.

Now let’s highlight some interesting differences. The first that comes to mind is how the tools handle the connection to the database. While SQL Everywhere offers an on-demand connection (default) thus unloading the server, Toad seems to not to offer any customization of this kind. Others are “As early as possible” and “On First Use”.

Another nifty feature is “Execute Here” (if you click on a database name). It is particularly useful if you want to run the same SQL statement against one database and the another, for this purpose I guess it offers data set comparison features but it was not available in the free editions, I guess it would be as easy to do by dumping the results to a file and compare using say ExamDiff.

For database administrators viewing active processes in SQL Everywhere (by pressing F2)

is just so simple it blows the mind compare to how you get this in SSMS, sure this feature is gonna be loved!

In closing, I would highlight some features I found lacking or not to may satisfactory:

First, I am bothered a lot by both IDEs not able to output the results of a query to text. However it is easily compensated by the “export to csv” functionality.

Second, is inability to edit data right in the grid in SQL Everywhere.

Third, while SQL Everywhere offered search within the grid with results, none of this appeared working in Toad (I am curious if this does work in the paid version of the product).

Speaking of the prices, Toad’s Pro version (the cheapest option) is $595 USD vs. $199 for SQL Everywhere, a big leap for not so much more I am tempted to say, also curious if this is the result of the so called “Bran Recognition” paradox marketing?

Well, all in all, both tools are a very good compliment to the SSMS and I hope will wet your appetite for exploring them more and contributing to your bottom line!

As it stands now, my first choice is SQL Everywhere for more flexible, practical and streamlined (e.g. macros support) database development experience.

Useful for your technical blog MSDN Widget with the latest MSDN content

Useful for your technical website or blog: latest MSDN content and news using MSDN widget!
Check out here http://www.msdnwidget.com/ the new MSDN Widget is there. You can customize the content feeds, widget size and skin it with your logo, too.

in reference to: MSDN Widget Generator (view on Google Sidewiki)

The best seen by me so far site dedicated to IIS!

The best site dedicated to IIS I have seen so far!
It is an all-in one portal on valuable resources about IIS including learning, downloads and links to best blogs.

in reference to: The Official Microsoft IIS Site (view on Google Sidewiki)

What’s new in .NET Framework 4 Client Profile

What’s new in .NET Framework 4.0 - Client Profile

Client profile is a new feature aiming at reducing the size of the installation package for desktop based applications and also reducing the possibility for attacks.

In Visual Studio 2010 this will be the default mode for installations unless you are installing a server-side program as say an ASP.Net Web application.

For a complete list of details and to obtain more information please visit the posting by WPF performance and .NET Framework Client Profile team on MSDN http://goo.gl/cjaS