c99fef2a

We need a new DevOps Database tool

mark.wojciechowicz@gmail.com (Mark Wojciechowicz) — Sun, 23 May 2021 20:26:20 GMT

Database deployments have had two dominant models over the past decade or more – a declarative model and a migration script approach. The major tools for the declarative model are SSDT (SQL server data tools) and BIML by Varigence (I have no experience with the latter). The rest of the market is made up of the migration script model with the leading tools being Ready Roll and Liquibase. Both approaches have their own challenges. In the migration script paradigm:

The definition of the code is not the source of truth. That is buried in the accumulation of the migration scripts. So viewing object level differences in source control is impossible
Scripts need to be applied in the specific order that they are checked in. This makes merge conflicts a common nuisance.
It’s easy enough to write a bad script and removing or undoing that change can also be painful.
If there’s schema drift in the target database, things can get messy pretty quick, either leaving the database in a unique unknown state or the deployment failing altogether.
What do you do with functions and procs? Since we are not comparing differences during deployment, you need to deploy this code each time in case there are changes.

On the model based approach:

External dependencies (database references) are awful to deal with on anything but a greenfield project. i.e. database1 has a proc that updates data in a table in database2. Database2 has a view that selects from tables in database1 and database2. Which database do you deploy first? The answer is neither, you cannot have circular references like this so you need to fix your code.
The memory footprint of a large database project (and all of its reference databases) can bring visual studio to its knees. It will take a long time to open the project and resolve all the references.

In both approaches, the team needs a lot of discipline. They need to adopt the new process and only apply changes this way or whatever they deploy out of band will be overwritten. I happen to prefer the model based approach because it addresses the schema drift issue, drawing the development team back into the practice of using the tool. SSDT is a fantastic tool and has a million great features, including tracking the rename of objects with an automation to help facilitate it. Besides the two issues noted above, another bit problem is that it only works with SQL Server. So what do we do about other relational databases, MySQL, Postgres, Snowflake…?

I think we need another tool. I think it’s unfeasible to try to do everything that SSDT does, but in the very least it should fill the model based gap with these features in mind:

Provide a model based approach for any popular relational database that follows ANSI standards
Schema Compare between the project and the database or two live databases
Reverse Engineer existing databases
Define the project as a collection of databases. This will remove the issue of circular database references because both databases can be deployed at the same time. It won’t address real circular references, that’s still on the developer.
Emphasize a small memory footprint, which will be even more challenging with the point noted above.
Default to best practices, such as using an additive approach. i.e. Do not delete objects not in the project, ignore column order in table deployments.
Provide the other essentials:
Dependency tracking
Rename operations
Rollback deployment scripts on error
Pre and post deployment scripts
One time migration scripts, a hybrid model will allow for flexibility when you need it

How do you refresh test, structurally and data-wise?

Fri, 17 May 2019 23:47:40 GMT

Upgrading SSAS Tabular 2016 -> 2017

Thu, 28 Mar 2019 01:19:02 GMT

If you've tried to upgrade an existing database from SSAS 2016 to 2017 using SSDT, you may have encountered this error:

Deploying the '<some database>' database to '<The Target Server>'.

The JSON DDL request failed with the following error: Failed to execute XMLA. Error returned: 'The operation cannot be performed because it references an object or property that is unavailable in the current edition of the server or the compatibility level of the database.

We opened a ticket with Microsoft to resolve this, after banging around with the problem for way to long. The short answer is SSDT cannot upgrade an existing database to 2017. Instead, you need to do this via an xmla script, changing the database name as appropriate:

Case Insensitivity in SSAS Tabular

mark.wojciechowicz@gmail.com (Mark Wojciechowicz) — Wed, 27 Mar 2019 00:24:21 GMT

Tabular models store data in a highly compressed form and sometimes this has unexpected consequences. In what I encountered today, we had a column which was input by users. Since this was test data, it had all sorts of nonsense with values like:
aaa
AAA
aAA
The tabular model looks at this through the lens of collation. In the instance of case insensitive models, all three values are considered the same and only one value will be stored. In fact, the first value it encountered will be the winner. So the only output from the model was "aaa."
I can't imagine this is a big issue - I mean case sensitive data models are a nightmare. But it's definitely something to be aware of.
Here's more on that:
https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/table-properties-ssas-tabular?view=sql-server-2017

Posting to #Slack from SQL Agent

mark.wojciechowicz@gmail.com (Mark Wojciechowicz) — Thu, 08 Feb 2018 17:53:46 GMT

2018-02-08
Post to slack from SQL Agent? Why on earth would I want to do that? Because email sucks and slack is cool. Also, SQL agent doesn't shed to much light on what went wrong in an SSIS package execution. It just says go look at the logs, which is not very helpful.

What do we need?

Permission to execute powershell
Permission to read the log tables from ssisdb
A slack url to post to

Getting the url from slack
Once permissions are sorted out, go to https://my.slack.com/services/new/incoming-webhook/
On this page, choose the channel you want to post to and hit the Add Incoming Webhooks Integration button:

In the next page, select the url and save it to the clipboard

Prepare the powershell script
As shown in the script below, set the parameters for the ssis folder, project and package name. Optionally, you can specify the slack channel, though if you don't it'll just use what you specified when creating the url above. Paste in the url to $slackUrl.
Also note the url for $iconUrl is some page where I found an image of the icon for SSMS. This should be set to something more relevant for the job you are running.

The script will execute a query against ssisdb (you might need to adjust the connection information). The results are returned in the form of XML and this is formatted for readability. Lastly, we call invoke-webrequest and post our message to slack:

Add the SQL Agent Step
Next, add a step to SQL Agent. Set the type to "powershell" and paste in the script that we prepared above. Go to advanced and set the step to report failure on success so we have an accurate history of when the SQL Agent job actually does fail. Also, set prior steps to go to this step on failure.

For testing, we can just execute the job from the slack step. However, it's helpful to have a prior package failure or it will have nothing to report. This is what it looks like on slack:

Fixing an SSIS package stuck in Created Execution

mark.wojciechowicz@gmail.com (Mark Wojciechowicz) — Tue, 12 Dec 2017 14:01:20 GMT

Occasionally, you may wind up with an SSIS package that is stuck in the status "Created Execution." In my case, the cause for this has been that new parameters have been added to the project, but a sql agent job has not been updated with values to set them. Then the sql agent job was kicked off and the job fails because it is missing the required values. This is even true when the values have been added to an environment and the sql agent job is setting them via the environment.

Whatever the cause, this is how you fix it:

In SSMS, under the Integration Services node, navigate down to the package and view the all executions report.
Click on the overview link for the one that is in the "created execution" status
Go to the "parameter values" table on the right and find the parameters with no value

4. Connect to SSISDB and execute the following procedure for each missing parameter:

5. Finally, kick off the execution with this stored proc:

Introducing SqlTest

mark.wojciechowicz@gmail.com (Mark Wojciechowicz) — Sat, 05 Aug 2017 14:11:34 GMT

A library to enable Sql and Ssis Unit Testing

One Friday, on the way home from work, it occurred to me that I've probably written some form of this SQL test framework a half a dozen times. I realized that I would save a lot of time for myself and others, who are interested in testing ETL code, if I wrote an open source library. So, I took it upon myself that weekend, to pour all my learnings, from a wealth of mistakes, into a code base that would handle all the boilerplate stuff that you need for writing tests efficiently.

My goal was to create something that was easy to use, easy to debug and easy to get started. SqlTest is meant to be used in a C# test project in conjunction with a test harness like nunit or mstest. I’m well aware that most SQL / SSIS developers do not live in .net and some are downright afraid of it. In fact, another popular testing framework, tSQLt , is an excellent option for many projects because you can write tests in native SQL, plus you get all the goodies that come with it, such as fakes. However, there were several reasons why I chose to go my own way:

tSQLt wraps everything in transactions, so all test behavior is rolled back at the end of each test. This is great for database tests, but no good for SSIS tests which are going to initiate their own connections and transactions.
Nunit has parameterized tests (or TestCase ). This allows you to create a dozen related tests very quickly, each with different input to the same test. This is tremendously efficient.
By going the route of SqlTest and Nunit, this allows a consistent approach to testing for both the database and SSIS.
Database tests in mstest are an elaborate hunk of junk.
1. They don’t allow you to wrap a test in a transaction and roll it back, if you use Setup / tear down
2. They have several layers of set up and tear down methods, one which executes for a given test and another which executes for all.
3. There are 7 different windows to find a test, setup, teardown, initialize, cleanup, asserts and assert properties. Never mind that every test is in its own window. Needless to say, it is a nightmare to debug.
4. Diffs cause eye-pain in source control
5. But they do give you syntax highlighting, which is nice. I’m telling you all this so you know what horrors I’m sparing you from

The initial release of SqlTest contains the essentials for testing. Methods for:

executing any adhoc sql statement for setting up and running a test
fetching a scalar result from the database
executing a package in the catalog
faking a table and dropping the fake
faking a view and dropping the fake

There are certainly some other features which will be useful, like executing a package from the file system for those poor souls who’ve yet to migrate to the project model. And there are some things which are less useful, like asserts on a dataset, which tends to be very fragile. I will plan to add more in time – as well, I hope for some suggested features from you.

There’s documentation about how it works here: https://github.com/MarkWojciechowicz/SqlTest/wiki

I encourage you to try it out today, this is an excellent compliment for any SSDT database project. Just create a new c# project in the same solution. You can install SqlTest from nuget by opening the Package Manager Console in visual studio and typing Install-Package SqlTest.

A sample comes with the install, but below is a quick example to see how to use it.

Enjoy!

How to fail an SSIS Task

mark.wojciechowicz@gmail.com (Mark Wojciechowicz) — Sat, 08 Jul 2017 15:11:00 GMT

SSIS allows the ability to force a task to fail in order to test unhappy paths. It's a bit confusing about how to do this because you could do any of the following:

Add an error to a sql task like divide by zero
Set the execution result to failure in a script task
Change the Forced execution value in the property of the task

The first two options could work, but for the purposes of avoiding changing code, you can just set the ForceExecutionResult property for the task as shown below. The other execution result properties do not do anything and fail package on failure will not help either.

How to make a Jenkins powershell script fail

mark.wojciechowicz@gmail.com (Mark Wojciechowicz) — Fri, 30 Jun 2017 16:49:08 GMT

Jenkins (the CI server) has a myriad of plugins, one of which is for executing powershell scripts. I rely on powershell heavily because I find a number of the other add-ins very limiting. In this particular case, I needed to run nunit tests and I needed the job to fail when the tests fail. I'll post more on the nunit thing at a later date, for which this is a plugin but it won't work for database projects.

When the powershell script throws an error, the build just writes the error to the log and keeps chugging along. The magic button to make the build fail is to add this to the top of the script:

And Bam! Rainy days again!

The Easiest Way to Rebuild a Table in Batch

mark.wojciechowicz@gmail.com (Mark Wojciechowicz) — Sat, 17 Jun 2017 14:50:30 GMT

Recently, I had the task of rebuilding a very large table in order to partition it and put it on a columnstore index. While testing this out in the lower environments, I quickly blew up the log and ran out of disk on the server, so I decided to batch it out instead. Below is an example of the query I used.

In the example, I set up a couple tables for the test. The WHILE loop checks if data exists in the source and then deletes a set of rows out and uses the OUTPUT clause to directly insert them into the new table.

This is a very lightweight operation because we do not need to check if the rows exist in the source table. We just delete a random set of rows until they are all moved. In reality, my batch size was 2 million - this should be adjusted accordingly.

Using .Net Objects in BIML

mark.wojciechowicz@gmail.com (Mark Wojciechowicz) — Fri, 09 Jun 2017 19:23:27 GMT

One handy thing with BIML, the T4 Templating tool for SSIS, is that you can reference cs files which can contain a .net class with helper methods. This can help keep your biml code clean and readable.

In this example, I'll show how to iterate over a list to generate custom dataflow tasks. Some BIML examples illustrate generating dataflows based on a set of tables. However, it does not seem very realistic that the source query and target table look precisely the same. This example will assume we have some custom query and a target table.

Previously, I have used a similar method which pulled metadata from a database table. However, I find that it is difficult to maintain a project across contributors because everyone needs to insure that the table data is refreshed before regenerating the biml. Also, it's difficult to troubleshoot because the data driving the solution is in a separate place from the code. This example puts the code and metadata in one place.

First, we will add a cs file to the project with this silly method:

Write click the project > Add Biml File
Rename the Biml file to PackageSource.cs
Replace the biml template with the following code:

In the PackageSource class we have the properties TargetTable and SourceQuery. The constructor will set these values with a new instance of the class. The method GetSources will return a list of package sources. It creates a list and adds PackageSource objects to them. Any time that we want to add a new source, we simple insert another packageSources.Add() line.

Next, we add another biml file to the project and use the following code:

At the top of the biml file, we add a code file reference. This must be before the biml tag or it won't compile. In this example, I used the reportserver database, figuring that most SQL dev's would have a local copy and be able to compile this code.

In the tasks section, we declare a variable and call the PackageSource.GetSources() method. Then we loop through each package source instance, plugging in the values of a package source - source.TargetTable and source.SourceQuery - into the oledb source and destination components. BIML handles the magic of mapping columns. Once generated, we get a package like this:

Enjoy!

wfh?

mark.wojciechowicz@gmail.com (Mark Wojciechowicz) — Tue, 16 May 2017 13:49:21 GMT

Working from home is a fact of life - how come some businesses haven't caught on to that?

In most places I've worked, wfh was an essential option that helped avoid long commutes, allowed me to take care of personal business (like the dentist or picking the kids up off the bus) and allowed for some sanctuary to code. Some places I've worked used this mode so often that you never had a clue if someone was in the office or at home. This is because they used communication tools so effectively like skype, webex and slack to name a few. There are some other tools that are often classified for project management, which were equally essential for communication - trello, jira or any flavor of kanban. And this is one of the key reasons why wfh was even possible. For those businesses that don't have a work from home policy or, in worse cases, strictly forbid it, they have some catching up to do to remain competitive in the market place. I've turned down plenty of offers for this very reason.

Companies that don't have a wfh policy often manage their workforce by the duration that their workers occupy their seats, with stringent hours or even such policies as unpaid lunches. These desperate actions are in place because the employer is measuring the wrong thing with the wrong tools. Measuring work input seems like a sensible approach, but it creates incentives to work less efficiently, because there is no incentive to succeed or complete work. On the other hand, measuring output turns the focus on what's getting done and ignores how that happens. Should someone have an inspiration in the the middle of the night, they can go work on that to their heart's content. If they are measured by time, there is little point in thinking on a project off hours.

When you begin to measure by stories completed, as in a burn down chart, or monitor the rate of check-ins, you can start to manage to the finish line. The workforce starts to look like a sprint team, as opposed to a bunch of out-of-shape 6th graders, dragging their feet around the track in gym class. Techniques like paired (or mob) programming become an option, because we don't consider two people working on one task as halving our effort, but raising the overall quality of the code. And as the code quality rises, fewer defects are created and the total cost of the code goes down. Skills and techniques are transferred so the team as a whole becomes more effective and the cost of future projects drop.

So why would I turn down a job that has no work from home policy? Clearly it's a more oppressive environment that shuns learning and sharing and smells of desperation and defeat. I think anyone would.

Git Changes Not Detected in an SSDT Project

mark.wojciechowicz@gmail.com (Mark Wojciechowicz) — Tue, 18 Apr 2017 11:58:44 GMT

This issue affects only database projects

I was setting up an SSDT solution in git at a new client, when I discovered a periodic issue in which my changes did not appear in the team explorer window. I knew full well I had made changes, and when I ran git status from the command prompt the unstaged changes were listed. I thought something was wrong with my software install, either with git for windows or visual studio, but it turned out to be another issue altogether. There is a discussion about it here:

http://stackoverflow.com/questions/37704514/visual-studio-2015-database-project-directory-contains-a...

The short answer is that a file with the extension .jfm is used by SSDT for processing and is not essential for source control. This file gets locked by the Esent engine (also used in windows) and blocks git from operating correctly. The file should be added to gitignore and removed from the repo. Once done, team explorer works correctly again.

For the sake of putting this information somewhere, here is what I have in the gitignore file (this is targeted for an MSBI application):

Udacity D3 Class

mark.wojciechowicz@gmail.com (Mark Wojciechowicz) — Thu, 11 Aug 2016 13:46:02 GMT

Market share of music media, 1980 - 2010

The above chart was created in Raw , which is a handy web-based tool for creating quick data visualizations. I came about Raw from a course I started on D3 and Data Visualization , taught by Udacity. I have taken quite a few online classes, but I am super impressed with the material for this course and how well it's presented. I think Udacity really has the online interface down and I am excited to take some more.

This is an embedded powerbi dashboard for a demo

mark.wojciechowicz@gmail.com (Mark Wojciechowicz) — Fri, 03 Jun 2016 19:27:52 GMT

This is for SQL Saturday

So there are some pretty basic steps for embedding content into a webpage.

In the report view, click on File > Publish To Web
Create the embed code and hit next
Copy the link for the iFrame
Paste the iFrame link into your webpage code or blog.

The Search for Intelligent Life

mark.wojciechowicz@gmail.com (Mark Wojciechowicz) — Wed, 04 May 2016 19:31:24 GMT

Looking for intelligent BI in a nearby system

Recently, the company that I work for decided to overhaul my team's BI blog, looking for something a bit more polished. It's true that our posts were raw and often had grammatical errors. It's also true that many of the posts had a nominal number of hits, probably amounting to the number of BI developers in the Philly region or, maybe, the number of twitter followers. But these posts did take a lot of time and thought and, for me, more importantly, it was a place to document those tidbits that I can never seem to remember and always seem to need to look up.

An additional twist is that my employer is really looking for posts that are less technically oriented. They want something that an IT manager might read on a long break in the men's room. Perhaps, something that might make the reader think, "maybe if I turn my Oracle database into a data lake, I'll make that next big promotion." And, while I will help middle management get a bit more middler, my friends and colleagues and I would still like to chronicle our technical wanderings.

This blog will be dedicated to that end.

We come in peace,
Mark Wojciechowicz