Thursday, May 7, 2015

APS / PDW AU 2 – SSIS PDW DESTINATION COMPONENT BUG AND WORK AROUND

This is something I discovered during a migration to an APS/PDW Appliance. I have seen little documentation on it and figured I would write a small post about it.

PDW Destination Component:

Loading Mode: Upsert

Issue: When specifying a key column(s) for the Upsert.
In situations where the key column name has a space at the APS destination table. I.E. [Column Name]. The Upsert will throw an error that the destination columns: Column, Name do not exist.
Upon further investigation the component is reading the selected key column names as delimited by a space as well as comma. It will then treat each word separated by a space in a column name as a separate column specified for the key. Note that this includes columns wrapped in brackets []

Work Around:
Rename the column(s) on the APS destination by removing the spaces from the key column names.
For Example: [Column Name] Renamed to [ColumnName] or [Column_Name]

Steps to Recreate the Issue:

Step 1
Create a Destination Table in the APS/PDW Appliance with a column name that has spaces. Such as [Sample Column]. This table can either be Distributed or Replicated.

Step 2
Create an SSIS Package with a sample data set to load into this table.

Step 3
Use a PDW Destination with the Loading Mode set to Upsert.

Step 4
Select the Key Column for the Upsert to the column that has the name [Sample Column]

Step 5
Execute the load and record the error.

No comments:

Post a Comment