An important task in data warehouse projects is incorporating data cleansing in the ETL flows. A very common issue there is dealing with NULL values. In SQL Server 2012, a new function called REPLACENULL was added to the Integration Services (SSIS) expres
I have two sets of data that I want to display in a chart using SQL Server Reporting Services (SSRS). However, the sets have different scales, so I want to plot the chart with a secondary axis. In this tip we show how this can be done.
I have a chart in SQL Server Reporting Services (SSRS), based upon data from a SQL Server table. I want to add a linear trendline to this chart, just like in Excel. In this tip we cover how this can be done.
I created a line chart in Reporting Services (SSRS). In order to analyze the data better, I’d like to add some sort of trendline to the graph, such as a weighted average for example. In Excel it is possible to add this functionality with just a few clicks
I have a data set about durations that I want to plot in a graph. I need to display the minimum, maximum, average and other statistical information in a convenient way. In this tip, I show how to do this using SQL Server Reporting Services (SSRS).
I have a fact table in my data warehouse that tracks durations of certain events as the total number of seconds that have passed. When users browse the cube, I’d like to show these durations formatted as time using the hh:mm:ss format (hours:minutes:secon
At a project we have multiple filegroups created in our SQL Server data warehouse. For example, there was a filegroup to store the fact tables on, a filegroup for the dimensions, a filegroup for heavy indexes and so on. This works great at first, but afte
The previous tip Simple Text Mining with the SSIS Term Extraction Component introduced us to some simple text analysis using Integration Services (SSIS), the Microsoft ETL-tool included with SQL Server. There the Term Extraction component was presented, w
We will explore how you can create your own custom reports and add them to SSMS.
SQL Server Analysis Services – SSAS – sorts dimension attributes alphabetically by default. The tip Change Sort Options on Dimension Attributes in SQL Server Analysis Services explains how you can change this sort order by adjusting the key columns and by
In an SSIS data flow, there are multiple types of transformations. On one hand you have synchronous and asynchronous transformations, but on the other hand you have non-blocking, semi-blocking and fully-blocking components. In this tip, we'll take a close
Microsoft recently released the OData source adaptor for Integration Services (SSIS). Using this source component, it becomes really straightforward to read data from OData sources, such as SharePoint lists. This tip will walk you through the installation
I have a query that executes quite slowly in SQL Server Management Studio, but when I transfer the data with a SQL Server Integration Services data flow, it takes even more time! The source and destination are both fast and have no bottlenecks, and there
Advanced analytics, big data, data science; they're all hyped topics nowadays. They're not new however, and one specific subject area has always had some presence in Microsoft's ETL tool: text mining. Integration Services offers two transformations that c
When you want to load data into your data warehouse, for example into one of your dimensions, you need to determine if an incoming row is either an update - a historical change - or an insert, which is a new occurrence of a record.
SQL Server 2012 introduced the new project deployment model for Integration Services. On the server side, this manifests in the SSIS catalog, the central point for managing SSIS projects, environments and parameters. This tip will guide you through the pr
Recently we were upgrading some SQL Server 2012 service pack 1 instances to the latest cumulative update available at the time. We encountered some issues during the upgrade process, such as the set-up wizard failing or not even starting at all. This tip
In my previous tips about BIML I introduced the concepts of BIML for SSIS. In this tip, I’ll convert the BIML script for generating the import package of the previous tip to a dynamic package generating machine.
In my previous tip, Introduction to Business Intelligence Markup Language (BIML) for SSIS, we looked at a simple example of how to use BIML. In this tip, we look at a real world example of importing data into a database using BIML.
Every ETL project has some repetitive tasks, but with SSIS out-of-the-box, you are forced to create multiple individual packages which is very time consuming due to the lack of easy code reuse. In this tip we show how you can get reuse using Business Inte
You want to incorporate transactions in your SQL Server Integration Services Packages. For example, you insert data into a table with a data flow and after the data flow has finished an update statement is issued against the same table. If this update sta