Unexpected Data Type on output error when moving packages to other machines

Feb 20, 2009 at 10:39 PM
Hi Todd,

This is a very useful component (I'm using the 2005 v1.4 version). However, the problem that I'm having is that once I move the packages to another machine (via SVN) and run DTExec the metadata validation seems to break. I looked at the source and can't really find the root of the issue. If I update the SSIS package on the other machine by rebuilding the metadata links then everything works, but when I move the package back to my dev machine, I get the same error.

Am I doing something wrong here?
Feb 23, 2009 at 5:08 PM
Can you give me some more information on what seems to break?  What are the exact error messages you get, and how are you "interpreting" what they mean... as in, what do you think it's trying to tell you?

In order to fix it, how do you "rebuild the metadata links"?
Feb 23, 2009 at 5:49 PM
It hits line 1581 in KimballMethodSCDConfiguration (2005 version). The validation errors occur for all outputs.

Something like:

Unexpected data types on the 'New' output.
Unexpected data types on the 'Unchanged' output.

It does this for all available outputs.

The way I update the package on the other machine is by simply deleting the KSCD task, dropping in a new one, and reconnecting the inputs and outputs.

I should also mention that I'm not using all of the outputs, but the error occurs for all of the possible outputs.
Feb 23, 2009 at 7:26 PM
Those messages (and line number) indicate that the metadata that the component is reading from SSIS on the inputs is different than the metadata that it's configured the outputs to.  (Sounds exactly like what you're thinking it's saying.)

What is interesting to me (or odd) is that this "validation error" that's detected by the component returns "VS_NEEDSNEWMETADATA" to SSIS.  In non-programmer-speak, this means that although the component found a problem with the configuration, it's also telling SSIS that "I think I can fix it myself", and it's requesting that SSIS instruct it to fix itself.  (Sounds weird, I know - it should just "fix itself"!  That's not how the plumbing works, though.)

A few questions for you...
1. You say that to solve the problem, you remove and re-add the component.  Have you tried just opening the component editor?  It ought to pop up a message box with the same set of errors you saw before, but it should also say "do you want the component to repair these errors?"  (To which you should say "yes".)  Does that fix the problem?

2. Is your "second" machine configured differently than the "first"?  This is a stab in the dark, but are the regional settings the same?

Based on the difficulty of "fixing" your problem - I'll be adding more information to those error messages - specifically the column that's causing the component to report the error - as that may point you in a good direction...

PS - thanks for using the component!
Feb 24, 2009 at 6:16 PM
Hi Todd,

When I open the editor dialog on the component I see the same error messages in the dialog. From memory I believe it did prompt to repair, but it does not fix the problem. The error messages remain. Dropping in a new instance of the component and reconnecting the inputs and outputs was the only thing that worked for me.

The differences between the environments:

One machine is XP and the other is Vista Home Premium. The other difference is that the XP machine originally had the 1.3 release of the component and then was upgraded to the 1.4 beta release while the Vista machine only had the 1.4 release. Those are the only differences that I can think of. I guess one other thing to note is that in both cases I am recreating the dbs (drop and run all create and initialization scripts) prior to executing the packages. It seems to me that recreating the dbs doesn't have anything to do with it.

The other thing I can do is debug the component myself. What are the prerequisites for setting up my environment to create my own local dev releases of the component?

Thanks again
Feb 24, 2009 at 9:23 PM
You can definitely download the source and diagnose the problem for yourself.  You'd need VS 2005 and SSIS installed on your "dev" machine.  I believe all you'd have to do is download the source, make your own snk file (by using sn.exe), identify that key file in the project properties of the two projects, AND put the public key part into the attribute at the top of the KimballMethodSCD class (so it can find the UI)...

Right now, I've made the error messages a little more verbose (in my code).  I have a little left to do to troubleshoot another issue, then I'll be updating the v1.4 release.  I'll post here again when I've done that so that you can download it, try it, and maybe get a better message...
Mar 12, 2009 at 4:32 AM
A new v1.4 has been uploaded to the Releases - please download that.  Hopefully that will fix the problem outright - but if not, it contains some more troubleshooting tools to assist in diagnosing the issue.
Mar 17, 2009 at 1:45 AM
Hi Todd,

I finally got a chance to try out the updated component. The error message complains about the Row Change Reason columns are mismatched between WSTR(90) and WSTR(100). The same goes for Invalid Input Error Column. WSTR(900) instead of WSTR(1000).

A quick find and replace on the XML files fixes the issue. I'm thinking this somehow has to be related to my dev machine having first had the 1.3 version installed and these newer machines only having had the 1.4 version installed.

Does that make sense?

What we be a good way to ensure that my dev machine has the 1.3 version completely uninstalled?

Thanks again
Mar 18, 2009 at 11:29 PM
(Use the uninstaller to get rid of v1.4 "nicely" first.)

To uninstall v1.3 (or v1.4 for that matter), you want to make sure you can't see "KimballMethodSCD" or "SSISComponentUtilities" in the "C:\Windows\Assembly" folder and the "Program Files\Microsoft SQL Server\90\DTS\PipelineComponents" folder (or 100\DTS for 2008), and you'll want to delete those everywhere else on your computer.  Do a search and make sure to include non-indexed locations...
Mar 19, 2009 at 12:43 AM
On my dev machine I have 2005 and 2008. First, I nicely uninstalled v1.4. There was still a dll in my 2008 DTS dir. I deleted that, reinstalled v1.4.

This reproduced the problem on my dev box that i was seeing on the other machines. Since I've fixed the issue on the other machine, I should be able to check it in and re-synch locally and all machines will be happy.

I think I'm all set.

Thanks for your help and patience!
Mar 22, 2009 at 12:10 AM
That's great!  I hope your fix sticks - cause I'm a little confused about what worked for you.  Happy SCDing....
Jul 23, 2009 at 10:52 AM

I am having a similar problem to this.  It is complaining about "Unexpected data types on the 'New' output.  (Special column 'Row Change Reason'  is DT_WSTR(100) ......  while the output is DT_STR(100).

I can sort of understand what is wrong - the Kimball SCD component is trying to set the output to WSTR whilst the actual output MetaViewer is saying it is STR.

The problem is I dont know how to fix this.


Any help much appreciated - this component is otherwise great.

Jul 24, 2009 at 9:13 PM

That is very odd.  The component should have complete control over that column.  You should be able to fix that by simply opening the editor, and when SSIS says "this and this and this are wrong - would you like the component to automatically fix them?", then say yes.

Do you have a column in your data called "Row Change Reason"?

Jul 25, 2009 at 5:39 AM

Sorry, upon reading that again, I wasn't clear when I said "Do you have a column in your data called "Row Change Reason"?"

What I meant was: Do you have a column in your Existing Dimension table called "Row Change Reason"?

Jul 28, 2009 at 9:01 AM
Edited Jul 28, 2009 at 1:02 PM

I think I have worked out how my problem is occurring.  When you attach the "Existing Dimension" Input to the SCD component, and if this source has a column called Row Change Reason, then it seems that the data type for this column is retained from the source data in my case this sets the Output Column to DT-STR - which the SCD component doesnt like - presumable something in the internals of the SCD component.

To get round this make sure that your Existing Dimension input does not contain the Row Change Reason column.



Jul 28, 2009 at 6:58 PM

Ah - thanks.  That's what I was thinking it could be.  Did the component not report a problem to you?  I'm sure I had that somewhere in the validation logic.

Perhaps that's not working as I intended - I'll double check that validation for v1.5.  The component should complain if you have an input column named the same as one of its' "automatically generated" columns, such as "Row Change Reason".