Archive

Archive for April, 2010

SSIS – Setting Options in Error Output

April 26, 2010 Leave a comment

Many data flow components in SSIS allow you to set options when an error or trucnation occurs. The options you can set for Error or Truncation are:

  • Fail Component
  • Ignore Error
  • Redirect Row

You would set it to Redirect Row if you want to use a Script Transformation component to write the error into a destination.

Define 3 columns for the  Script Transformation component: Error Source Column, ErrorCode, ErrorColumn

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode)

    End Sub

End Class

An error output example:

100ty
,-1071607676,185,The data value cannot be converted for reasons other than sign mismatch or data overflow.

200yu
,-1071607676,185,The data value cannot be converted for reasons other than sign mismatch or data overflow.

300rr
,-1071607676,185,The data value cannot be converted for reasons other than sign mismatch or data overflow.

Categories: SSIS Best Practices

SSIS – Expression Language

April 23, 2010 Leave a comment

I’ve has very little exposure to the expression syntax in SSIS so far. Expression language in SSIS is another powerful feature.

Here is the Integration Services Expression Reference on MSDN: http://msdn.microsoft.com/en-us/library/ms141232(SQL.90).aspx

Here is a nice blog on the expression language in SSIS: http://sqlblog.com/blogs/andy_leonard/archive/2009/01/30/an-introduction-to-the-ssis-expression-language.aspx

The syntax shares similarities with languages such as: C++, C#, Java, etc.

  • == for comparison
  • = for assignment

Here is what is said on MSDN:

Expressions are a combination of symbols (identifiers, literals, functions, and operators) that yields a single data value. Simple expressions can be a single constant, variable, or function. More frequently, expressions are complex, using multiple operators and functions, and referencing multiple columns and variables.

The following SSIS elements can use expressions:

  • The Conditional Split transformation, which implements a decision structure, based on expressions, to direct data rows to different destinations. Expressions used in a Conditional Split transformation must evaluate to TRUE or FALSE. For example, Column1 > Column2.
  • The Derived Column transformation, which adds new columns to a data flow and populates the columns or updates existing columns with values created using expressions. For example, Column1 + ” ABC”.
  • Property expressions, which can use an expression to set property values. For example, “The processing of ” + @TaskName +” is completed”.
  • Variables, which can use an expression to set their value. For example, GETDATE().
  • Precedence constraints, which can use expressions to specify the conditions that determine whether the constrained task or container in a package runs. Expressions used in a precedence constraint must evaluate to TRUE or FALSE. For example, @A > @B.
  • For Loop container, which can use expressions to build the initialization, evaluation, and increment statements the looping structure uses. For example, @Counter = 1.

Expressions include three different elements: the expression grammar, the expression evaluator, and the expression builder. The expression grammar defines expression syntax and the operators, functions, and data types available for use in expressions.

The expression evaluator parses expressions and determines whether expressions adhere to the rules of the expression grammar.

The expression builder, available in the dialog boxes for the Conditional Split and Derived Column transformations, is a graphical tool for building expressions. The expression builder provides a list of available data columns, system and user variables and a list of functions and operators. To build expressions, you can drag and drop items from either list onto the expression column. The expression builder automatically adds needed syntax elements such as the @ prefix on variable names.

Categories: SSIS Best Practices

SQL – Proveit Test (SQL Sever 2005 for Developers)

April 23, 2010 Leave a comment

Hi Peter,

I did take the test last night with a 62% score. I had to remind myself that my score is still slightly above national average, which is 50%, to still feel pround of myself.
 
It was a tough test, as you said, in the sense that it is not testing your SQL skills, rather it’s testing how much you know (or used) the new enhancements in version 2005. I don’t regret taking the test, even if it means that the recruiter will not talk to me again. At least now I know how much I don’t know about those enhancements.
 
Thanks for keeping an eye out there for me.

Sherry Li

From: Friend Peter

Subject: ProveIt test
Date: Fri, 23 Apr 2010 08:25:38 -0700

Hi Sherry,

Sorry I missed your IM last night.  I’ve had to take those before.  Sometimes they use proveit and sometimes they use brainbench.  I find the tests are somewhat similar to the Microsoft certification exams in that there are some very easy questions, and some hard ones which are only hard because they are asking questions about features that you may have never used.  For instance, I’ve done very little with XML, and I remember there being several questions about using XML clauses in SQL queries.  I had to guess on those.  Some companies  (staffing companies and/or end client companies) request these tests as a way to further screen their candidates, but it only happens to me about 10% of the time.  

I will look out for SQL opportunities for you at (). 

Peter

SSIS – Derived Column Transformation

April 23, 2010 Leave a comment

Derived Column transformation offers considerable flexibility and versatility combined with extreme ease of use. It can generate output data rows through custom-built expressions (constructed with a wide range of built-in functions and operators), which, besides referencing input columns might also leverage values stored in package variables.

Here are some basics of using the Derived Column transformation. In the Derived Column transformation editor, we can hard code a value in teh expression for the derived column, for example a space ” “, or we can drag the Null function for the DT_WSTR data type into the Expression textbox, and then enter NULL(DT_WSTR, 50) into the expression textbox. We can also use a ternary syntax:

[Test Condition] ? [True Operation] : [False Operation]

Example: IsNull(CustName) ? ” ” : CustName

In the Derived Column dropdown, we can also select “<add as new column>” to add a new derived column.

The Derived Column Transformation seems to be very flexible and powerful. The mathematical and string parsing functions are easily understood. Other ETL-specific functions, such as NULL fields, type-casting, and ternary conditional operations, are not as commonly understood.

Categories: SSIS Best Practices

SQL – Cross Join

April 23, 2010 Leave a comment

A cross join (or Cartesian Product join) will return a result table where each row from the first table is combined with each row from the second table. The number of rows in the result table is the product of the number of rows in each table. If the tables involved are large, this join can take a very long time.

A cross join can be specified in two ways: using the JOIN syntax or by listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria.

SELECT * FROM A CROSS JOIN B

SELECT * FROM A, B

I prefer the first syntaxt because itwill be obvious to other developers who might need to modify my code in teh future.

This is an example where I used CROSS JOIN to combine every date in the month with every transaction types:

SELECT A.DT
 ,  B.REFERRAL_SOURCE_GROUPING
 ,  C.TYPE_ORDER
 ,  C.REFERRAL_TYPE
 INTO #REFERRAL_TYPE
 FROM ( SELECT DISTINCT DT
    FROM #REF ) A
 CROSS JOIN
 ( SELECT ‘1) Early Intervention Referrals’  AS REFERRAL_SOURCE_GROUPING UNION ALL
  SELECT ‘2) Collections Referrals’ AS REFERRAL_SOURCE_GROUPING UNION ALL
  SELECT ‘3) HES Referrals’ AS REFERRAL_SOURCE_GROUPING ) B
 CROSS JOIN
 ( SELECT 1 AS TYPE_ORDER, ‘Non-HEART Ref’ AS REFERRAL_TYPE UNION ALL
  SELECT 2 AS TYPE_ORDER, ‘HEART Ref’ AS REFERRAL_TYPE UNION ALL
  SELECT 3 AS TYPE_ORDER, ‘HEART Recmdtn’ AS REFERRAL_TYPE UNION ALL
  SELECT 4 AS TYPE_ORDER, ‘WFHM’ AS REFERRAL_TYPE ) C

Categories: SQL Challenge

SQL – Common Table Expression (CTE)

April 21, 2010 Leave a comment

CTE exist in DB2 long before SQL Server 2005 introduced it. A CTE is a "temporary result set" that exists only within the scope of a single SQL statement. It allows access to functionality within that single SQL statement that was previously only available through use of functions, temp tables, cursors, and so on.

with myCTE as (
select col = ‘How are you’ )

select * from myCTE

Like a derived table, a CTE lasts only for the duration of a query but, in contrast to a derived table, a CTE can be referenced multiple times in the same query. So, we now we have a way of calculating percentages and performing arithmetic using aggregates without repeating queries or using a temp table.

CTE and recursion

More interesting, is the use of recursion with CTEs. The table defined in the CTE can be referenced in the CTE itself to give a recursive expression, using union all. Here is an example:

WITH QUEUE_HIERARCHY (QUEUE_ID, DEPT_ID, NM, QUEUE_DESC, PARENT_QUEUE_ID, WATERFALL_ORDER)
         AS   
         (    SELECT    QUEUE_ID
            ,        DEPT_ID
            ,        NM
            ,        QUEUE_DESC
            ,        PARENT_QUEUE_ID
            ,        WATERFALL_ORDER
            FROM    DB2SCHEMA.T_QUEUE_DEF 
            WHERE    PARENT_QUEUE_ID IN ( 90301982)
            UNION ALL
            SELECT    A.QUEUE_ID
            ,        A.DEPT_ID
            ,        A.NM
            ,        A.QUEUE_DESC
            ,        A.PARENT_QUEUE_ID
            ,        A.WATERFALL_ORDER
            FROM    DB2SCHEMA.T_QUEUE_DEF AS A, QUEUE_HIERARCHY AS B
            WHERE    A.PARENT_QUEUE_ID = B.QUEUE_ID
         )
         SELECT * FROM QUEUE_HIERARCHY ORDER BY PARENT_QUEUE_ID, NM

Categories: SQL Challenge

SSIS – Package Configuration

April 21, 2010 Leave a comment

As we develop, test and deploy SSIS packages, we need to specify different parameter values for things like database connection strings, file and folder paths, etc. without having to edit the individual SSIS packages to make these changes.

SSIS provides package configuration values to allow developers to dynamically configure those parameter values. Configuration values can be stored in environment variables, XML files, registry settings, and a table in a SQL Server database.  In each case you are storing and maintaining configuration parameter values outside of the SSIS package then using those stored values when executing the package.

Categories: SSIS Best Practices
%d bloggers like this: