The Merge Join Transformation and a SSIS Sorting Tip

My previous blog post was about the SSIS Lookup task and how it really works. Now that I have shown that the Lookup task shouldn’t be used for one-to-many or many-to-many joins, let’s take a look at the Merge Join transformation task. If you follow along with this blog, you will learn a little tip that will eliminate the requirement for you to add a SORT transformation task within your data flow task.

Continue reading The Merge Join Transformation and a SSIS Sorting Tip

Part 1: MDX Code Development for Beginners

As business intelligence continues to make a big splash into businesses, SSAS and cubes are becoming a requirement. One of the limitations that DBAs face every day is converting from reading and writing T-SQL statements to being able to read and write MDX with ease. In order help others who may have been thrown into the role, I am going to start a series on MDX code writing.  This blog post will be geared towards translating a simple T-SQL SELECT statement into an MDX SELECT statement.

Continue reading Part 1: MDX Code Development for Beginners

Creating SSAS Hierarchies with Multiple Dimension Tables

Recently, I was tasked with building a hierarchy using multiple dimensions. I was advised to use a named query or materialized view to accomplish this because SSAS doesn’t have the capabilities to read the columns/attributes from multiple dimensions. Since I have done this before, I decided to take the AdventureWorks database and give a step-by-step tutorial of how to accomplish this request. Let’s get started!

Continue reading Creating SSAS Hierarchies with Multiple Dimension Tables

Avoid Visible Attribute Hierarchies for Attributes Used as Levels in User-Defined Hierarchies

Have you ever built a dimension in SSAS and received a blue informational warning advising you to:

Continue reading Avoid Visible Attribute Hierarchies for Attributes Used as Levels in User-Defined Hierarchies

How to Use Project-Level Parameters

SQL Server 2012 introduced a feature called project parameters. Of course, like many other developers, I was stuck on not fixing something that was not broken. Over time, I learned that project parameters are very beneficial features to use, especially when moving a package from DEV to QA and finally to PROD. Let’s take a quick look at how to set up package parameters and how to use them to manipulate connection strings at runtime.

Continue reading How to Use Project-Level Parameters

How to Load Informix DB2 Using SSIS

Can Microsoft SQL Server and Informix DB2 environments integrate together? The answer is YES!! I have received an increasing amount of questions concerning wanting to cross platform ETL development work between the two. Driven from these questions, I want to dig deeper into regards to manipulating data between Microsoft SQL Server and Informix DB2.

Continue reading How to Load Informix DB2 Using SSIS

When is it the Right Time to Use the SSIS Lookup Task?

I have been working as a DBA for RDX (Remote DBA Experts) for a few years now. At this point in my career, I believe it’s time to start giving back to the SQL community. I have commonly seen many misunderstand how the Lookup task in SSIS actually works. The Lookup task does work as a join method. However, the Lookup task actually is going to pull back only the top matching record from the defined Lookup task in your SSIS package.

Continue reading When is it the Right Time to Use the SSIS Lookup Task?