Friday, September 19, 2014

SSIS Expressions

Expressions
An expression is a combination of constants, variables, parameters, column references,
expression functions, and/or expression operators, allowing you to prescribe at design time
how a specific value will be determined at run time. Expressions are used to determine values
dynamically in an automated process, rather than having these values set manually and in
advance using constants.
Expressions are written in a special expression language native to SSIS.(c++,c#)

In SSIS 2012,Expression improvements
  • 4000 character limit is removed
  • New functions – LEFT, TOKEN, and TOKENCOUNT
Left:

Returns the left part of a character expression with the specified number of characters.
Template:
LEFT( «character_expression», «number» )
Example:
LEFT( [ProductName],3)

TOKEN

The new TOKEN expression is an interesting one.  It returns a string after a specified Token delimiter.  You can pass in multiple delimiters for you expression to parse and also specify the occurrence number you would like to return.  That means if you set the occurrence to 3 it would return the third instance of the token.  SSIS describes this function as:
Returns the specified occurrence of a token in a string. A token may be marked by a delimiter in a specified set of delimiters. The function returns an empty string if the occurrence is not found. The string parameter must evaluate to a character expression, and the occurrence parameter must evaluate to an integer.
Template:
TOKEN( «character_expression», «delimiter_expression», «occurrence» )
Example:
TOKEN("new expressions can be fun"," ",2)
Result:
expressions

TOKENCOUNT

TOKENCOUNT would likely be used in combination with the previously discussed TOKEN function.  The TOKENCOUNT function returns back the number of times a Token delimiter appears in a string value.  This would likely be plugged into the TOKEN expressions for the number of occurrences when trying to find the last occurrence.  SSIS describes this function as:

Returns the number of tokens in a string. A token may be marked by a delimiter in a specified set of delimiters. The string parameter must evaluate to a character expression.

Template:
TOKENCOUNT( «character_expression», «delimiter_expression» )
Example:
TOKENCOUNT("new expressions can be fun"," ")
Result:
5

REPLACENULL

Again this function answers the SSIS haters who don’t like the fact that there is an ISNULL function in the expression language but it doesn’t work like the T-SQL ISNULL. Currently if you wanted to accomplish the T-SQL ISNULL you would have do write an expression like this:
ISNULL(OrderDateSK) ? 19000101 : OrderDateSK
This uses the ISNULL function that returns back True or False if the field is NULL and also uses a conditional operator to determine how to react when it is NULL.  The REPLACENULL function will work much more like the T-SQL ISNULL function.  This function is described as:

Returns the value of the second expression parameter if the first expression parameter is null.

Template:
REPLACENULL( «expression», «expression» )
Example:
REPLACENULL(  [OrderDateSK] , 19000101 )
Result:
19000101 (if OrderDateSK is evaluated as NULL)

FINDSTRING(StringValueToLookInto, StringValueToLookFor, Occurrence)
Remarks

FINDSTRING works only with the DT_WSTR data type. character_expression and searchstring arguments that are string literals or data columns with the DT_STR data type are implicitly cast to the DT_WSTR data type before FINDSTRING performs its operation. Other data types must be explicitly cast to the DT_WSTR data type. For more information, see Integration Services Data Types and Cast (SSIS Expression).
FINDSTRING returns null if either character_expression or searchstring are null.
Use a value of 1 in the occurrence argument to get the index of the first occurrence, 2 for the second occurrence and so forth.
The occurrence must be an integer with a value greater than 0.

Ex:
1. FINDSTRING(“You should say hello to people when you meet”, “hello”, 1)
This will return value 16, which is the place from where the string “hello” starts.

When this function returns 0, it means that the function did not find any character/string that matches. Following example will return 0 because the string value “bye” is not found.

2.FINDSTRING(Produts,"DVD",1) > 0 ? "Company Sells DVD" : "Company Donot sell DVD"

3. Following example will show, how we can use FINDSTRING function with a date. Let say we want to find out what day is today if today is Saturday or Sunday we want to display “Weekend day WOW” else we want to display Monday to Friday as “Work Day”.

(FINDSTRING((DT_STR,20,1252)DATEPART("dw",GETDATE()),"1",1) == 1 || FINDSTRING((DT_STR,20,1252)DATEPART("dw",GETDATE()),"7",1) == 1) ? "Weekend day WOW" : "Work Day"


More efficient:

UPPER(SUBSTRING(Name,1,1)) != "A" rather than SUBSTRING(UPPER(Name),1,1) != "A"
,because only one character is converted to uppercase. 

Examples of Advanced Integration Services Expressions

This section provides examples of advanced expressions that combine multiple operators and functions. If an expression is used in a precedence constraint or the Conditional Split transformation, it must evaluate to a Boolean. That restriction, however, does not apply to expressions used in property expressions, variables, the Derived Column transformation, or the For Loop container.
The following examples use the AdventureWorks and the AdventureWorksDW2012 Microsoft SQL Server databases. Each example identifies the tables it uses.


  • This example uses the Product table. The expression evaluates the month entry in the SellStartDate column and returns TRUE if the month is June or later.
    DATEPART("mm",SellStartDate) > 6
    
  • This example uses the Product table. The expression evaluates the rounded result of dividing the ListPrice column by the StandardCost column, and returns TRUE if the result is greater than 1.5.
    ROUND(ListPrice / StandardCost,2) > 1.50
    
  • This example uses the Product table. The expression returns TRUE if all three operations evaluate to TRUE. If the Size column and the BikeSize variable have incompatible data types, the expression requires an explicit cast as shown the second example. The cast to DT_WSTR includes the length of the string.
    MakeFlag ==  TRUE && FinishedGoodsFlag == TRUE && Size != @BikeSize
    MakeFlag ==  TRUE && FinishedGoodsFlag == TRUE  && Size != (DT_WSTR,10)@BikeSize
    
  • This example uses the CurrencyRate table. The expression compares values in tables and variables. It returns TRUE if entries in the FromCurrencyCode or ToCurrencyCode columns are equal to variable values and if the value in AverageRate is greater that the value in EndOfDayRate.
    (FromCurrencyCode == @FromCur || ToCurrencyCode == @ToCur) && AverageRate > EndOfDayRate
    
  • This example uses the Currency table. The expression returns TRUE if the first character in the Name column is not a or A.
    SUBSTRING(UPPER(Name),1,1) != "A"
    
    The following expression provides the same results, but it is more efficient because only one character is converted to uppercase.
    UPPER(SUBSTRING(Name,1,1)) != "A"
    

Non-Boolean expressions are used in the Derived Column transformation, property expressions, and the For Loop container.
  • This example uses the Contact table. The expression removes leading and trailing spaces from the FirstName, MiddleName, and LastName columns. It extracts the first letter of the MiddleName column if it is not null, concatenates the middle initial and the values in FirstName and LastName, and inserts appropriate spaces between values.
    TRIM(FirstName) + " " + (!ISNULL(MiddleName) ? SUBSTRING(MiddleName,1,1) + " " : "") + TRIM(LastName)
    
  • This example uses the Contact table. The expression validates entries in the Salutation column. It returns a Salutation entry or an empty string.
    (Salutation == "Sr." || Salutation == "Ms." || Salutation == "Sra." || Salutation == "Mr.") ? Salutation : ""
    
  • This example uses the Product table. The expression converts the first character in the Color column to uppercase and converts remaining characters to lowercase.
    UPPER(SUBSTRING(Color,1,1)) + LOWER(SUBSTRING(Color,2,15))
    
  • This example uses the Product table. The expression calculates the number of months a product has been sold and returns the string "Unknown" if either the SellStartDate or the SellEndDate column contains NULL.
    !(ISNULL(SellStartDate)) && !(ISNULL(SellEndDate)) ? (DT_WSTR,2)DATEDIFF("mm",SellStartDate,SellEndDate) : "Unknown"
    
  • This example uses the Product table. The expression calculates the markup on the StandardCost column and rounds the result to a precision of two. The result is presented as a percentage.
    ROUND(ListPrice / StandardCost,2) * 100
    

Wednesday, September 17, 2014

Monday, September 15, 2014

What’s new in SSIS for SQL Server 2012





What’s new in SSIS for SQL Server 2012
·         Logging Changes
·         Undo/Redo
·         Package Parameters
·         DQS and SSIS
·         Flat File Improvements
·         Shared Data Sources
·         Script Component Debugging
·         Name-based metadata mapping
·         CDC in SSIS
·         Environments
·         10+. Designer Improvements

· 


·