// Enlarge AdventureWorksDW2012

SQL Server t-sql 0 min.

featured image Enlarge AdventureWorksDW2012 I could use the AdventureWorks2012 database, but I needed the clean datawarehouse tables in order to have minimum data maintennance when testing the BIML scripts. I could not find it, and figures out it was faster to make my own. So heavily inspired by this post from Jonathan Kehayias (blog), I’ve made a script that can be used to enlarge the dbo.FactInternetSales table. The script creates a new table called dbo. [...]

// SSIS Expressions I Often Use

SQL Server SSIS 2 min.

featured image SSIS Expressions I Often Use If either you are doing your SSIS by hand or using the BIML framework, you’ve came across the expressions and the expression-builder. This is a helper list, with my most often used, and wich are allways forgotten when I need them, of my commonly used SSIS expressions. Strings Filename from fully qualified pathstring RIGHT([FilePath],FINDSTRING(REVERSE([FilePath]),”\”,1) – 1) Folderpath from fully qualified pathstring SUBSTRING([FilePath], 1, LEN([FilePath]) – FINDSTRING(REVERSE([FilePath] ), “\” ,1 ) + 1) Foldername from fully qualified pathstring TOKEN[FilePath],”\”,TOKENCOUNT([FilePath],”\”) – 1) This is only for SSIS2012 and onwards. [...]

// Fully automate the BIML expansion

SQL Server BIML 1 min.

featured image Fully automate the BIML expansion With the MIST application from Varigence – this is possible through the command line util that ships with the installation. When the installation of MIST has finished, you’ll find a new .exe-file in the installation folder called bimlc.exe. This file is the core engine of the command line util. According to the online documentation found here – the command line util is pretty straight forward. The observant reader will notice that the link points to a documentation for hadron. [...]

// Referencing the same custom script task in SSIS with BIML

SQL Server BIML 1 min.

featured image Referencing the same custom script task in SSIS with BIML On one of my most recent projects we had a custom script task that we needed to implement in the same package several times. The code of the custom script is in its own biml-file in the project and referenced from the main biml-file. We kept getting a mysterious error when executing the package in debug mode from Visual Studio. (No warning upon building the packages). After alot of debugging and hair pulling hours, we finally got our arms around the bug. [...]

// Install SQL Server in 5 mins

featured image Install SQL Server in 5 mins

Install SQL Server in 5 mins

You know this everybody – you need to install a whole new SQL Server on the customers Windows Server.
And the customer doesn’t have the time or money to wait for you to click around the GUI and sit around and watch the progress-bar move every second.

So why not do this in a fully automatic way and in the same time save both effort and expensive time doing so?

[...]

// Split delimited string into rows

SQL Server t-sql 1 min.

featured image Split delimited string into rows On several occasions I’ve been assigned the task to split a delimited string into rows. I’ve done this in different ways, but never thought about the performance or stability of the different approaches for doing this. So here’s my 25 cents and findings. My first solution to this was to code a function to traverse through the string and insert a new value to a temp table for every delimiter found in the string: [...]

// Multidimensional ROLAP partitions and updatable columnstore indexes – the new black?

featured image Multidimensional ROLAP partitions and updatable columnstore indexes – the new black? I came across a colleague of mine, who asked me if the new updatable columnstore index and ROLAP partitions in a Multidimensional cube is the new trend of fast and no-latency Business Intelligence. Well – here is my 25 cents. I’ll start with the updatable columnstore indexes. With SQL Server 2014 Microsoft introduces updatable columnstore indexes. Which in short terms defines that the columnstore no longer has to be dropped/disabled when loading data to the table. [...]

// SSISDB crash – Certificate, asymmetric key or private key data invalid

SQL Server ssis 2 min.

featured image SSISDB crash – Certificate, asymmetric key or private key data invalid One of my longterm customers had a database-server crash a while ago. The server would not spin up after reboot. The SSISDB crash also gave problems. The whole installation including tweeks and other stuff had to be re-attached to a new and fresh installation of SQL Server 2012. It was s fairly young BI-server, so the damage was not that big after all. Untill today… SSISDB crash The development of SSIS project to maintain the BI sollution had to be deployed to the SSISDB catalog. [...]

// Dynamic partitioning tabular cube

featured image Dynamic partitioning tabular cube In every project on Business Intelligence there comes a time when the code needs to be deplyed to the production environment. No more development, no more manual work. It is time for dynamic partitioning. But what about the partitions on the tabular cube? Do we really need to tell and learn the DBA how to handle that on a periodic plan? The answer is simple: No! Thanks to the XMLA language, the DMV’s for SSAS instances (both tabular og multidimensionel) and SSIS we can do the partitioning dynamic based on the current data in the datawarehouse. [...]

// SQL 2012: Performance monitoring the light and right way

featured image SQL 2012: Performance monitoring the light and right way Ever had that awesome SQL tracer build up that does just the right thing for your system to do some performance monitoring – well I know that I had. And someday you might need just the same trace again. But now you need to build it again… Here comes the feature Extended Events in place. It was first introduced in the SQL 2008 version. The feature is a good and lightweight event-driven mechanism for collecting information about your SQL server. [...]