Power BI annoyances

We are currently analysing data from multiple sources with Power BI and have a fairly simple data model. We were surprised when we found that Power BI cannot join data using multiple data columns. The answer is to generate a unique compound key across both tables. Here is an excellent blog post with a worked example by Reza Rad. Relationship in [...] Read more

Old school cross tabs and PIVOT.

In all data integration jobs you will come across a problematic data source. It's either a SCADA system, old Payroll software or a selection of log files - it is all part of the fun of getting useful information out of digital data. For over 10 years now I've specialised in the Microsoft SQL Server product and I've become very used to T-SQL. This [...] Read more

Manage long running jobs in SQL Server Agent

It has been a very busy few months at Ventient Energy, some news coverage is here, so I've not had the time to post as often as I would like. However, I have some great Power BI and SSIS posts lined up so stay tuned. In this quick post we want to monitor SQL Server Agent jobs and take action if a job takes longer than some known duration. In my [...] Read more

Zip and unzip tasks in SSIS

I've been doing a lot of work with SQL Server Integration Services (SSIS) downloading zip files and unzipping the data. There isn't a "native" zip task in SSIS at the time of writing but I've always used the Execute Query task with 7Zip since back in my SQL 2005 days (7zip has been around since 1999!). I'll go through that in more detail later in [...] Read more

Using Powershell in SSIS.

Powershell is a great tool for managing SQL Server, Azure and all things Microsoft (okay - most things Microsoft, I still haven't been able to automate many Reporting Services tasks). If you are a DBA managing SQL Server then I strongly recommend that you look at dbatools.io which will supercharge your Powershell toolset. Powershell can allow you [...] Read more

< 1 2 3 4 5 6 7 >