OWB - table upsert mapping

Error:

VLD-2750: Missing update matching criteria in YOUR_DIM Match by Constraint is set to all constraints or a specific constraint for YOUR_DIM, then all mapped attributes of this constraint will be included in the WHERE clause in the UPDATE statement. If there is no constraint on the target, or none of the constraint attributes are mapped, set Match by Constraint to no constraints for YOUR_DIM, and define the match condition on the attributes of the target

Solution excerpted from Oracle forums:

When you use a surrogate key (eg. generated with a sequence, presumably for
a slowly changing dimension) you also have to define a unique natural key on
your target table, and use the natural key for matching/updating.

If you change your target table columns or constraints after the operator
was put on the mapping canvas, there is a possibility that OWB doesn't change
the "Loading Properties" correspondingly. To fix this;

1) first finish up your constraints work, then
2) set operator property "Match by constraint" to "No constraints", then
3) check column attribute properties "Loading Properties" for all
columns;
a) columns being part of the unique natural key should have "Load column when
Updating Row" set to "No", other columns should normally have this property set
to "Yes"

b) the "Match Column ...." properties should be set to "Yes" if part of the
natural key, "No" otherwise

c) "Load column when Inserting Row" should normally be "Yes".

4) Finally, you can change the operator property "Match by constraint" from
"No constraints" to your natural key

SSIS Oracle RDB to 10G source

Lessons for me from this exercise:

• I had to throw away the custom ODBC script tasks I had built for RDB and create a new OLE DB based extraction package
• Data that previously loaded into SQL Server Int columns now caused a datatype mismatch error. I had to change the target columns to Numeric
• This had the flow-on effect of causing many data flow data sources to break with an invalid index error. These data sources had to be recreated
• Some text data that had previously loaded in a trimmed state now had trailing spaces

So that was the dev environment. To get the packages to work via IIS required:

• Had to allow inprocess in SQL Management Studio for the provider OraOLEDB.Oracle http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=766133&SiteID=1
• Working my way through the steps in http://support.microsoft.com/kb/255084. The reboot actually made a difference

SSIS Oracle OLE DB connection

Set AlwaysUseDefaultCodePage=TRUE on the OLE DB Source component

http://blogs.conchango.com/jamiethomson/archive/2005/10/25/SSIS_3A00_-Connect-to-Oracle.aspx

So how do you do set this attribute for an Oracle ODBC source?

Everybody positioned in Leaders Quadrant for Business Intelligence

Is the recent BI M&A activity rendering this Gartner report meaningless?

Microsoft's rating upgrade is unfortunately overshadowed by their being too cheap to license a copy of the report. You have to go back to the old guard for this (thank you Cognos):

http://mediaproducts.gartner.com/reprints/cognos/vol4/article3/article3.html

Mea culpa, MS did actually dip into its wallet but didn't refer to the report location in their press release.