simple SCD1 task

Sep 14, 2009 at 7:42 PM

I would like to create very simple tast, for refreshing dimension. For begining I would like to use just SCD1 type. I have both sources (existing dimension and source) from MSSQL. I've defined one field as buisineskey and one as surrogate key (I create surrogate key in dimension table with IDENTITY coulum). I've defined destination buth nothing comes to it...

When I look at output, I get:

  • Information: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Input rows processed: 19822 Existing Dimension, 0 Special Members, 19823 Source System
  • Information: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Rows output: 0 Unchanged, 39645 Invalid Input, 0 New, 0 SCD1, 0 SCD2

Any idea why I have just Invalid Input columns (I dont' see any error in output)?

Sep 14, 2009 at 7:51 PM

RTFM. It was "allow null" problem.

Coordinator
Sep 15, 2009 at 4:48 PM

Glad you found your problem.  For future reference for others - always hook up the Invalid Output to something.  Anything.  Even just a Row Count transform.  Why?  So you can put a Data Viewer on the output.  Even if you just put a Row Count there to write to a dummy variable and only have a Data Viewer (which only works in "debug mode" when you're executing from BIDS) - DO IT.  At the very worst, your "production" jobs will (you are using the Auditing features, right?) "fail" (detect Invalid Input).  Your next step will likely be to run that in debug mode - and voila, even though you may not be actively logging that output (although you should), the Data Viewer will pop up with all the info you need.

Sep 16, 2009 at 7:29 PM

Todd this is excellent advice - I've found that the Trash Destination is invaluable for doing just this sort of thing - use it for debugging during testing and development of a package. You can find it at

http://ssisctc.codeplex.com/Wiki/View.aspx?title=Other%20Tasks%20and%20Components%20available%20from%20Konesans