SQL Server

Summing Float datatypes returns different results depending on the row order.

By Peter De Lange | by Peter De Lange

Nobody would recommend anyone they like to use the Float datatype. But, if somebody does, I am always looking for different ways in which the FLOAT datatype may surprise people. Just so I can show some examples. Recently a LinkedIn post triggered me to look for another one, and I found it on StackExchange. SQL Server’s SUM() can produce different results depending on the order rows are aggregated! The example here: If you want SQL…

Continue reading

SQL Server

Detailed logging while deploying DACPAC using sqlpackage.exe

By Peter De Lange | by Peter De Lange

In Visual Studio your database project is published without error to your environment. But somehow it fails in your deployment pipeline where it is published using sqlpackage.exe. Would it not be great if you could get some detailed logging of what exactly is going wrong?Add the /Diagnostics parameter to your publish command to get get more logging then you can handle 😉 Example below: In this case I found that my build was incorrectly overwriting…

Continue reading

Extract Load Transform SQL Server

File System Task error could not find a part of the path

By Peter De Lange | by Peter De Lange

If you are trying to move a file using the SSIS filesystem task and you receive this error: “Could not find a part of the path…” Googling for answers may result in the hint to use the rename action instead. If that indeed works, you are most likely ignoring the original problem which may be that your destination path contains the filename. To correctly setup the File System Task for file move actions the source…

Continue reading

Extract Load Transform SQL Server

Could not create DTS.Application because of error 0x80040154

By Peter De Lange | by Peter De Lange

If you encounter the following error: Could not create DTS.Application because of error 0x80040154 From calling DTEXEC.exe. For instance like so: It could well be the case that the DTEXEC that is being called is not compatible with the database server you want to run the SSIS package on. Particularly your development environment may be susceptible to this problem. To solve this, search for all the different DTEXEC.exe versions on your machine and find the…

Continue reading

Extract Load Transform

How fast can SQL Server write to disk, and why am I not reaching those speeds? Speed test results

By Peter De Lange | by Peter De Lange

In my previous blog post I set up an experiment to figure out what is the fastest setup for SQL Server and SSIS to extract and load a file into a table. In this post I will be discussing the results of this experiment. The experiment The experiment considered a simple two step ELT process: The different variables that were tested: Total performance Taking the best performing scenario it looks like we can extract and…

Continue reading

Extract Load Transform

How fast can SQL Server write to disk, and why am I not reaching those speeds?

By Peter De Lange | by Peter De Lange

A question that regularly comes up in ETL scenarios is whether SQL Server can read and write faster if you divide your tables and indexes over multiple filegroups and your filegroups over multiple files. My standard thinking on this was always as follows: Depending on whether you can write those files to different disks or not this will be the case. In a single disk scenario it should not matter at all. I started out…

Continue reading

Uncategorized

My first experience with Ethernet over power.

By Peter De Lange | by Peter De Lange

One of the rooms in our house is a Wi-Fi dead zone and after thinking about this for some years I finally decided to buy a Powerline adapter to solve this problem. I considered several alternatives which I will discuss below. Alternative solutions for Wi-Fi dead zone The solution space for solving the Wi-Fi dead zone problem was constrained by lack of power sockets at convenient locations that would enable implementing a mesh or repeater…

Continue reading

Team process

Onboarding new team members

By Peter De Lange | by Peter De Lange

What is the first thing that happens when you add a new team member to a data team? Output of the team as a whole will decrease. This is is not a bad thing on it’s own. However it may be an unexpected outcome for the person who sees the increase in costs that come with hiring extra people. Why does this happen? While no output is coming from the team member being onboarded, the…

Continue reading