With the release of SQL Server 2016 comes many great new features. One of these is the implementation of row level security in the database engine. This blogpost will cover the aspects of this new feature – including: Setup Best practice Performance Possible security leaks Introduction The row level security feature was released earlier this year to Azure – following Microsoft’s cloud-first release concept. A past big issue with the SQL Server engine was that in only understands tables and columns.
One of the new features in SQL Server 2016 – and there is a lot – is the ability to stretch the on premise databases to an Azure environment. This blogpost will cover some of the aspects of this – including: Primarily setup – how to get started Monitoring state of databases that are in ‘stretch mode’ Daily work with stretch databases Backup – what’s good to know With the release of SQL Server 2016, the new feature called stretch database is also released.
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.
This just got in: Second shot is back! You can now retake your exam for free, until jan 12 2016. Yay! Go get ’em… More info here
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.
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.
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.
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?
The Power Query tool has just been updated and there are some pretty neat features rolled out: New Transfomation: Use Headers as First Row. Column Name field in Merge Columns dialog. Quick Access Toolbar in Query Editor dialog. Warning for stale data previews in new queries. Function Invocation from Search. Preserve Excel Number formats in the worksheet on query refresh. Along side this update – there is also an updated Salesforce connector for Power Query which you need to install after installing the above update.
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: