Getting No Output - Invalid Input?

Topics: Issues 3: Executing the Component (Run-Time)
Aug 31, 2010 at 3:26 PM

I tested this the other day with a simple example, and everything worked perfectly. So now I am trying it on a more complex data flow, but I am getting no output. I can see the correct number of rows loading from both the source and the existing dimension, but when they hit the Kimball Method SCD, it does not output any records.

It is giving metwo warning messages in the SCD component. One says the input is not sorted by the business key, and "Sorting the input according to the Business Key can increase runtime performance" The other is "The Invalid Input output is not attached. You can dismiss this warning by acknowledging it in the Output Column Selection tab of the editor."

Once it gets past the SCD component, it does not appear to send any data past it into the Derived Column components or the Union All, even though I know I have around 3,000 new input records, and probably hundreds of update records.

Any ideas what I may be doing wrong? Are the warnings pointing to a fatal flaw. I am not able to see anything obvious in the Execution Results, other than the fact that it is writing 0 rows.

Thanks!

Coordinator
Aug 31, 2010 at 7:52 PM

First - I hope your use of the Derived Columns and Union Alls is for development purposes only - you'll have to replace all those with appropriate OLE DB Command or OLE DB Destination components to properly persist your data in your dimension table.

You should be able to see an Information message from the KSCD after it's finished processing that will tell you a summary of how it processed all the rows.  It should read something like this:

Information: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Input rows processed: 3 Existing Dimension, 0 Special Members, 1 Source System
Information: 0x0 at Data Flow Task, Kimball Method Slowly Changing Dimension: Rows output: 3 Unchanged, 0 New, 0 Deleted, 0 SCD1, 0 SCD2 Expired, 0 SCD2 New, 0 Invalid Input

From that, you should be able to see where your rows are ending up.  As a best practice, I recommend always hooking up the Invalid Input to some sort of error reporting mechanism.

Aug 31, 2010 at 9:46 PM

Thanks Todd,

Yes, this is just in test mode right now. It is due to invalid input. I added a derived column for invalid input and sure enough all of my rows are taking that route! I am still trying to figure out what is invalid. It did not give me any specific error messages that tell me what is invalid from what I can tell. Here are the messages referring to rows perocessed and invalid input: 

[Kimball Method Slowly Changing Dimension] Information: Input rows processed: 217438 Existing Dimension, 0 Special Members, 220258 Source System

[Kimball Method Slowly Changing Dimension] Information: Rows output: 0 Unchanged, 0 New, 0 Deleted, 0 SCD1, 0 SCD2 Expired, 0 SCD2 New, 437696 Invalid Input

It is loading all of the source records as well as all of the existing dimension records as invalid input, so I am getting all records from both. I am thinking something is wrong with my keys. I have a surrogate key that I want SQL Server to update. I will keep looking at it.

Thanks again.

Coordinator
Aug 31, 2010 at 10:18 PM

The message in the output window won't tell you why it's sent all those rows to the Invalid Input.  What you should do is put a Data Viewer on that flow, or send them to a text file or some other place you can look at them.  There will be an error message with each row.  Since all your rows are going out, I'm going to assume you have something configured incorrectly, which is probably resulting in NULL values being part of your business key.  The error message should explain what's wrong.

Sep 23, 2010 at 9:08 PM

I had the same issue, i found out, i didnt tick mark the "Allow nulls check boxes in KSCD"

 

Sep 4, 2012 at 11:04 PM

I am having a similar issue. Using version 1.6.

Let's take for example one business key.

It loads into the DW (Existing Dimension) fine first 3 times, with valid effective from and effective to dates.

As soon as that business key shows up for the fourth time (is updated fourth time) in the source input, the rows are routed to the "InValid Input" stream.

The message is

Overlapping SCD 2 version detected (1/2)

The data in the DW looks correct and the incoming rows has updated data as well. I am expecting a row on the "Expired SCD2" stream and one on the "New SCD2" stream.

Any ideas what this error would mean?