SSIS Email Task Delay Validation
Recently I was trying to send a file via Email in SSIS. For the connection to the file I was using a variable as the file name changes everyday. This was causing an issue because it validates the whole package before the file gets generated and the package would fail. Read More…

MDS “Service Unavailable” Error
After restarting a server that has MDS on, I could no longer access MDS via the browser. This seemed very strange as I have restarted the server multiple times and it had never done this before. Turns out the Application Pool had stopped for MDS. So I started it up and then it would stop again, frustrating me further. Read More…

Limiting SSIS to a Single File Type
A while ago I was having some issues with SSIS and moving files. There was a hidden file called thumbs.db When SSIS was running the move file task it would fail because it can’t place duplicate files in the same location. The thumbs.db file was the problem. Even though it was hidden as a windows file, SSIS still saw it and wanted to move it. Read More…

SSIS Error – the path for isserverexec.exe cannot be found
Recently been working with getting multiple SQL Instances on one machine to work. When I uninstalled an old one it affected the latest one. Then all of a sudden my ETL jobs failed. It did not make sense at all seeing as I removed a different instance. Got the following error when trying to run my ETL jobs or when trying to deploy the jobs: “the path for isserverexec.exe cannot be found” Read More…

Indexing Temp Tables
While busy with query performance tuning and trying to see whether the query would run better using a CTE’s (Common Table Expression) or Temp tables and comparing times, I realised I forgot to think about adding indexes on my temp tables which could increase the performance. Read More…

PowerBI Campaign/Brand Management for Twitter
PowerBI has released a fantastic template for Twitter called Campaign/Brand Management for Twitter. After following the “wizard” you will have a complete report filled with sophisticated analytics. Read More…

Windows Update KB2919355 Not Applicable
When trying to install SQL, Windows Update KB2919355 is a prerequisite, and on a new server this is not always installed and the SQL install stops until it is rectified. Read More…

Setting Up SSIS in SQL Server Management Studio
Before you can deploy a SSIS project you need to set up SSIS in SQL Server Management Studio. When installing SQL you will select to install SSIS, however, it does not setup the SSISSDB or configure any settings for SSIS. Read More…

GenesisOne™ T-SQL Source Code Unscrambler™
Have you ever worked with code that is impossible to read and decipher? You could use SQL Prompt which will format the code into a readable format, however what if there is so much going on you are still unable to decipher what is happening? Read More…

MDS Backup Strategies
Recently a colleague and I had a discussion about which method is the best to use for backing up MDS (Master Data Services) for disaster recovery. We came to the conclusion it all depended on the SQL environment version you were recovering to. Below are the 2 different methods for backing up an MDS database. Read More…

Row Versioning-based Isolation Levels
Following on from my previous post on SQL Transaction Isolation Levels, we get to the juicier part of Row Versioning-based Isolation Levels. Read Committed Snapshot and Snapshot isolation levels allow for the use of row versioning. By using the row versioning-based isolation levels, it will improve read concurrency by eliminating locks for read operations. Read More…

SQL Transaction Isolation Levels
Isolation levels can be quite useful when retrieving data and inserting data to maintain data integrity. Isolation levels control the locking and row versioning behaviour between SQL statements issued by a connection to SQL server. It defines the degree to which one transaction must be isolated from resource or other modification made by other transactions. Read More…

Contained Database
A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. Basically it includes all database settings and the metadata within itself thereby resulting in no configuration dependencies on the instance of the SQL Server Database Engine where the database is actually installed. Read More…

Temp Table VS Table Variable
There have been many discussions on whether a temp table is better to use or a table variable. It depends on what you would like to do. They both have their pros, cons and limitations. Read More…

SSIS Package Validation
Ever wonder why your SSIS packages are taking forever to load? This is because SSDT is trying to validate your packages. What happens is SSIS checks the data sources in the package(s) that are open and checks to ensure that the external metadata is valid. If the metadata is not valid you will get error/warning messages. Read More…

Limit Memory Used By SQL
Ever noticed SQL using most of the Memory (RAM) on the server and all your other applications start slowing down? This could be because SQL is using the maximum amount of memory it can get a hold of. Read More…

SSRS Reporting Server Recycle Time
Many people complain about their reports taking a long time to load when they access them first thing in the day. This is due to the reporting server’s recycle time. What happens is SSRS regularly restarts the application domain after a specific time period. Upon first request to the report server after the application domain has been restarted, SSRS needs to load all the settings and this takes quite a long time. Read More…

Detecting Row Count Anomalies with Simple Linear Regression
A follow up from my previous post on Detecting ETL Duration Anomalies with Simple Linear Regression, I thought I would share how to detect Row Count Anomalies with Simple Linear Regression. This is very useful to detect when tables row counts all of a sudden grow by an abnormal amount, or are flushed unexpectedly. Read More…

Master Data Services (MDS) 2016 Prerequisites for IIS
This is just a high-level overview of the prerequisites that are needed for IIS. You need to have IIS installed on the server you are installing MDS on in order for MDS to work. The easiest way to check if you have all the prerequisites for IIS installed is to open up Master Data Services Configuration Manager. Read More…

Detecting ETL Duration Anomalies with Simple Linear Regression
Thought I would share a piece on detecting row count anomalies using Simple Linear Regression. This post will give you a brief overview of what simple linear regression is and how you can use it to your advantage with testing. In this post I will be demonstrating how to use Simple Linear Regression to detect ETL duration Anomalies. Read More…

Registered Servers in SQL Server Management Studio
Many people hear about Registered Servers in SSMS and then completely forget about it and how useful it can be if they are in a large environment where they need to connect to many servers. Registered Servers allows you to save the server details in SSMS and give it a friendly name so when you need to connect to the server you can just double click on it. Read More…

Formatting Shortcuts in SSDT for SSIS Packages
When developing SSIS packages the tasks and transformations tend to be misaligned after dragging them onto the designer from the SSIS toolbox. You can correct this by selecting Format > Auto Layout > Diagram. When you have multiple packages in your SSIS project this can become quite tedious. Read More…

Retrieving Data Types of All Columns in a Database
Thought I would share a quick post about retrieving all the data types of all columns in a database. This can be quite a handy script when you are comparing development and production databases to make sure all the data types match in each table. Read More…

Creating a “Run As” Account for Scheduled ETL Jobs
When working in large SQL production environments that have multiple jobs doing different things you might want the ETL jobs to run as another account other than SQL Server Agent. This can help with security by limiting the amount of access the account has to the databases the packages in the ETLs are connecting to. Read More…

Email Job Execution History in HTML Format
Knowing if your jobs have succeeded or failed can be quite handy. Especially when it comes to knowing whether your backups are failing or if you have ETLs that are failing. You can choose to set up email notifications on each job, although if you have multiple servers you could be receiving a lot of email throughout the day. Read More…

Scripting Multiple SQL Server Agent Jobs
Thought I would share this useful tip on how to script multiple SQL Server Agent jobs into one script in order to execute it on another server. Read More…

Management Data Warehouse (MDW)
Management Data Warehouse (MDW) is an awesome feature in Microsoft SQL Server that allows you to collect data about your server and view it in a readable format. MDW is a relational database that contains the data that is collected from a server that is a data collection target. This is very useful for DBA’s. Read More…

Management Data Warehouse (MDW) Reports
Management Data Warehouse reports can be very useful and you can get quite a lot of information regarding your server out of them. In this post I will be showing some of the reports and how to get access to them. Read More…

Parameter Sniffing in Microsoft SQL Server
Parameter Sniffing in SQL Server is SQL Server’s effort to reduce the CPU overhead by caching the query execution plan and using it for all similar queries instead of recompiling the query each time at execution. Read More…

Setting up FILESTREAM and FileTable in SQL Server
In my previous blog post I wrote about what FILESTREAM and FileTables are. In this post I explain how to set FILESTREAM and FileTables up. Read More…

FileStream and FileTable in SQL Server
FileStream and FileTable are used to store unstructured data in the database. You can store BLOBs such as Word documents, PDFs and images. Read More…

Preparing for the MCSA and MCSE Exams
I recently took the exams to get my MCSA and MCSE in BI and spent quite a few hours preparing for the exams. I thought I would share how I prepped for the exams. Read More…

Running Visual Studio as a different user
When you are working as a consultant at clients and your machine is not on their network and you need to develop in Visual Studio with the credential they provide, it can be quite handy to run your Visual Studio with the Windows credentials they provide. Read More…

SQL OUTPUT Clause
For my first post I thought I would share about the SQL OUTPUT clause which returns information from modified rows (INSERT, DELETE, UPDATE). Read More…