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.
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…
We try to avoid SQL Server user-defined functions unless we can be certain that there is no other way to achieve what it is we’re asking of them. They aren’t very performant as they calculate on a row by row basis rather than the set based operations that our esteemed RDMS is renowned for.
Having said this I am going to post a table function I created recently for use as part of an SSIS import package that transforms an incoming single row CSV into roughly 100 records each time it runs.
The content team in our organisation faces the soul-destroying task of entering records as well as comma delimited strings on to an Excel spreadsheet which in turn is imported into 2 SQL tables.
There is no straight forward way to split the csv string into a SELECT list, but here is what I have done…
Ever been stuck trying to figure out what people are doing on your server, but unable to tell from DBCC InputBuffer what statements the users are running? This is one of the most useful scripts I’ve come accross and helps enormously when trying to analyse what is happening on your SQL Server.
It is often the case that users will query your database with tools like MS Access or Business Objects which are quite difficult to track. Well this useful tool helps to solve that problem…