TRY_CONVERT, TRY_CAST to Find Failed Data Conversion

Problem: Whenever you have similar error messages during data loading by SSIS, it indicates that you have issues between data type conversion. How to find those outliners / failed conversion out?

• [OLE DB Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".
• [OLE DB Destination [2]] Error: There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[NPI] on OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data".

Solution: Using TRY_CONVERT or TRY_CAST Functions in SQL Server 2012

TRY_CONVERT and TRY_CAST Functions introduced in SQL SERVER 2012 is very handy tools to dig out those failed data conversion or casting. If the conversion/cast is successful then it will return the value of the specified data type; Else it will return a NULL value. For example, for the following conversion, when using CONVERT function, it will give error message like “Error converting data type varchar to bigint”.

SELECT CONVERT(bigint, '8906UP')

while using TRY_CONVERT function, it will give NULL as a result.

SELECT TRY_CONVERT(bigint, '8906UP')

So the quick way to locate those outliner in NPI column is to run the query shown below:

SELECT NPI FROM Provider
WHERE try_convert(bigint, NPI) IS  NULL

Difference Between COALESCE and ISNULL

ISNULL is a T-SQL function to replace NULL values as COALESCE does. At some cases these two functions get the same results but there are some differences. Below is a summary of their differences and I hope that it will help you decide which function to use when you need to determine the one with better performance or better concurrency control.

	COALESCE	ISNULL
Standard ANSI SQL?	Yes	No (Only for T-SQL)
Numbers of Parameters	Two or more parameters.	Two parameters only.
Data type determination of the resulting expression	Determined by the data type of parameters with the highest precedence	Determined by data type of the first parameter
Allow both inputs to be untyped NULLs?	No. For SELECT COALESCE(NULL, NULL); , it will return an error. You need to have at least one of the null values be a typed NULL.  SELECT COALESCE(CAST(NULL AS INT), NULL);	Yes. For SELECT ISNULL(NULL, NULL); it will return a NULL typed as an integer.
NULLability of the resulting expression	Considered to be NULL with non-null parameters.	Always considered NOT NULLable
Working with subquery as input parameters	When executing COALESCE((subquery), 1), the subquery is evaluated twice.  you can get different results depending on the isolation level of the query.	Evaluated only once.

How to: Create Hierarchies in PowerPivot for Excel 2013

Just like hierarchies in SSAS, hierarchies in PowerPivot for Excel 2013 provides a very convenient way to let you group data through different layers. Today we will use date dimension to demonstrate how to create hierarchies in PowerPivot.

First of all, you need to be in the Diagram View of the PowerPivot Window. Click Diagram View button under Home Tab.

Create Hierarchies

Select the table you would like to build hierarchies in. Here we choose DimDate table. Then click the “Create Hierarchies” button on the upper right corner of the table. After renaming the hierarchy you can drag the fields from the table down to the hierarchy that was just created. An alternative is to select a column first and then right click the column > Create Hierarchies.

Add Hierarchies

Right click the column that you would like to add to a hierarchy. A column can only be in one hierarchy. If a column already exists in a hierarchy, there won’t have an option to “Add to Hierarchy”.

If you would like to add a column from another table, you need to use RELATED DAX function to add columns from one table to another table. Before you can add columns, you must create a relationship between these tables.

Adjust Hierarchy Levels

If you would like to adjust the levels between hierarchies, right click any one and move up or down, or remove.

After hierarchies are created, you can directly drag it to PivotTable Fields just as columns.