Press "Enter" to skip to content

Day: January 27, 2021

SSIS Framework Manager Community Edition

Published 2021-01-27 by Kevin Feasel

Andy Leonard has a new product announcement:

I’m excited to announce SSIS Framework Manager CE (Community Edition) is available for download at DILM Suite! SSIS Framework Manager CE is designed to support SSIS Framework Community Edition, providing a GUI to facilitate SSIS Framework Application creation, configuration, and management.

Three views are supported in this initial edition: Catalog, Application, and Package. The Catalog view incorporates the same Catalog treeview used in SSIS Catalog Browser (also free) and SSIS Catalog Compare (not free):

Click through to see what’s included.

Comments closed

Debugging DAX via Tooltips

Published 2021-01-27 by Kevin Feasel

Matt Allington has a workaround for us:

DAX is a tricky language; on the surface it is deceptively simple, but under the hood it can quickly become complex and it can take many years to master. If you have ever typed a formula and crossed your fingers when you press Enter, then you know what I mean. If you are reading this article, you are no doubt already on your own DAX learning journey.

As you become more competent at DAX, you will start to write more complex formulas that behave differently depending on the filters in your visuals.  One such example is the P&L report that I shared in 2020.  This report contains quite a tricky formula. It was built slowly and methodically, one step at a time, until it was working as desired.  This is the best way to write DAX.  If you want to see how I do it, then go back to the article linked above and watch the video.  While I recommend this as the best approach to writing formulas, the reality is that sometimes you will need to go back to a formula you wrote previously and debug that formula.  Maybe it is not working correctly, or maybe you need to enhance it for some reason.  Whatever the reason, today I am sharing with you my technique to debug complex formulas using tool tips.

Read the whole thing.

Comments closed

Faster String Concatenation with SQL Server

Published 2021-01-27 by Kevin Feasel

Steve Stedman has some tips for people who need to combine strings:

What the previous show as that the longer the string gets the slower the concatenation is. So instead we declare a second VARCHAR(MAX) variable called @stringBuilder, and each time through the loop we concatenate to that, then every thousandth time through the loop we take the @stringBuilder variable and concatenate it on to the @bigString, then clear out the @stringBuilder variable. This keeps the @stringBuilder variable relatively short, and reduces the number of concatenations to the @bigString to roughly 1/1000th the original.

Click through for a demo of the process. I don’t think I’ve been in too many situations where string concatenation was a performance killer in SQL Server, but it’s good to know.

Comments closed

App Locks and Read Committed Snapshot Isolation

Published 2021-01-27 by Kevin Feasel

Michael J Swart has a tip for those who have RCSI turned on and are using app locks:

The procedure sp_getapplock is a system stored procedure that can be helpful when developing SQL for concurrency. It takes a lock on an imaginary resource and it can be used to avoid race conditions.

But I don’t use sp_getapplock a lot. I almost always depend on SQL Server’s normal locking of resources (like tables, indexes, rows etc…). But I might consider it for complicated situations (like managing sort order in a hierarchy using a table with many different indexes).

Click through to see how it normally works and how you should switch things up if you’re using Read Committed Snapshot Isolation.

Comments closed

Using the Model Database

Published 2021-01-27 by Kevin Feasel

Garry Bargsley explains the utility of the model database:

Below I will outline the model database, which has a unique purpose for SQL Server.

The model database is an empty shell of a database that has the sole purpose of providing a database template for any new User Database created in SQL Server. With that being said, you can make configuration changes to the model database and then expect those baseline settings to be applied to all new user databases.

Read on to see some of the things you can set. I was a bit disappointed in some of the things which model doesn’t apply across to other databases, but it does carry over quite a bit.

Comments closed