Joe the Business Intelligence Guy

A journey through the design and delivery of business intelligence solutions.

SSIS Merge Join Data Flow Component – Discoveries

In an effort to help avoid many long wasted hours researching interesting join “features” found within the SQL Server Integration Services (SSIS) Merge Join Data Flow Component, I offer the following discoveries (more embellishments and links to follow).

  • Using SQL Server Uniqueidentifer (GUID) data type columns as join columns will yield very strange results with the only solid work around being to cast (or convert as your TSQL preference dictates) the Uniqueidentifer to NVARCHAR(36) followed by performing LTRIM, RTRIM, and UPPER to be safe and ensuring sucessful character by character matching. Example: [CaseBusnessKey] = LTRIM(RTRIM(UPPER(CAST([CaseID] AS NVARCHAR(36)))))
  • When more than one sorting/matching column is used you have to be careful which you want to include in the results. Specifically, you may select all of the sorting/matching columns, none of the sorting/matching columns, or the first several (in sequence starting from the first) sorting/matching columns. So based on these requirements, you may not select the 2nd sorting/matching column without selecting the first sorting/matching column. Should you inadvertently try to select the 2nd sorting/matching column without selecting the first sorting/matching column, the component will produce an error message “The component is not in a valid state. The validation errors are: The column with the SortKeyPostion value of 0 is not valid. It should be 2.”

Written by Joe Salvatore

June 10, 2010 at 12:11 am

One Response

Subscribe to comments with RSS.

  1. […] This post was mentioned on Twitter by Richard HR, CXI. CXI said: SSIS Merge Join Data Flow Component – Discoveries « Joe the …: Joe the Business Intelligence Guy. Just another W… […]

Comments are closed.

%d bloggers like this: