SSIS 2012 - New expression language functions(ReplaceNull,Left, Token, TokenCount)

SQL Server 2012 is full rich of new features for SSIS, really many new useful stuff, such as the script task debug and the renovated Flat File Source, that could handle uneven number of columns...


Today I am going to demonstrate 4 new SSIS expression functions...


1-Left

Which exactly the opposite of the RIGHT functions, and similar to the LEFT function in T-SQL, which allows you to grab a number of characters from your string, starting at point zero or in other words from the LEFT side of the string.
http://msdn.microsoft.com/en-us/library/hh231081.aspx

2-ReplaceNull

Which as the name means, it replaces your NULL values, with another string expression.  Before you would use ISNULL which returns Boolean injunction with a condition to change the value. ISNULL(LastName) ? "Unknown last name":LastName 
http://msdn.microsoft.com/en-us/library/ms141184.aspx
http://msdn.microsoft.com/en-us/library/hh479601.aspx

3- TokenCount

Which allows you to separate your string into tokens based on a specified delimiter, and returns back the numbers of occurrence of that expressions (Number of Tokens)
http://msdn.microsoft.com/en-us/library/hh213135.aspx

4-Token 

Substrings your string into Tokens based on a specified delimiter and brings back the number of requested token.
http://msdn.microsoft.com/en-us/library/hh213216.aspx

Let's use a demo as I learn better by examples...


The demo script as following..will create a test table, insert 3 records..

1 2 3 4 5 6 7 8 9 10 11 12 13

/* Create Table */CREATE TABLE FunctionsDemo( ID INT identity(1,1), Name VARCHAR(50), SSN Varchar(11), Phone Varchar(12)) /* Insert Test Records */INSERT INTO FunctionsDemo VALUES ('James','157-15-8877','201-342-3355') ,('John',NULL,'123-456-8965') ,('Liz','212-91-5618',NULL)

I'll basically add a "OLE DB Source" to get the records from the database, then a derived column to use these expressions goodies.


The package should look like that...I'll assume that you know how to use the source and will skip its configurations.

 And here's my expressions at work, in the Derived Column Transformation.

Which I’m using the above various expression, to get the last 4 digits of the SSN, to validate if the phone number in the right format, replace a null SSN or to get the area code from the phone number, which is simple and basic but will give you the idea of how you could use these new functions.
 And here's the results

The package was created using SQL 2012..

The package can be downloaded from https://www.box.com/s/1c5d52b9f0d42134068a

Hope that helps someone...