Protean

From time to time these days I have to put my DBA hat back on and review database schema designed by those whose focus is on application development. What I often find is that attempts to improve performance through indexing have been done in vain as the query optimizer either doesn’t use them enough to merit their creation or they have been created on the wrong columns.

Frequently, optimisation is referred to as a dark art, but here I am going to attempt to give some basic pointers to help enighten the novice database designer…

(more…)

In my previous post on the pivot component, we created a report from a partially populated translation table.

In this post I’m going to show how we re-import our completed report back to our SQL Server table using the SSIS Unpivot component.

(more…)

The SQL Command OLE DB component doesn’t like inline comments when using the Native SQL Server OLEDBConnection. Leave them in your SQL Code and you get the following error:-

An OLE DB error has occurred. Error code: 0×80004005.  An OLE DB record is available.  Source: “Microsoft SQL Native Client”  Hresult: 0×80004005  Description: “Syntax error, permission violation, or other nonspecific error”.

Take them out and hey presto…

It may be me, but as yet I haven’t seen too much written about the SSIS pivot and unpivot components.

Whilst they are not the easiest to configure, when faced with the option of importing to SQL Server and using a UNION or the new PIVOT clauses, I actually prefer the SSIS component.

Rather than actually explain the component, I thought it might be useful to go through a real example, so here goes…

(more…)