Need Assistance?

Coordinator
Sep 20, 2008 at 1:13 AM
Please post here if you need assistance installing or using the component.
Oct 15, 2008 at 10:04 PM

I need guidance on installation.
I have gacutil on my computer but am having problems installing the dll files.

C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322>gacutil /i "D:\Program Files\Micros
oft SQL Server 2005\90\DTS\PipelineComponents\SSISComponentUtilities.dll"

Microsoft (R) .NET Global Assembly Cache Utility.  Version 1.1.4322.573
Copyright (C) Microsoft Corporation 1998-2002. All rights reserved.

Failure adding assembly to the cache: Unknown Error

C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322>

Coordinator
Oct 16, 2008 at 4:05 AM
Two things come to mind: Administrator and .Net 2.0

1. Can you try running the command line "as administrator"?  GACUtil won't work if you're not an admin, and on Vista, even if you are an admin, you still have to run the command-line "as Administrator" for GACUtil to work.
2. The component requires the .Net runtime v2.0.  (I see you're running the v1.1 GACUtil which made me think you might not have v2.0, even though I think SSIS requires v2.0.)  ANY version of GACUtil should work fine.

If neither of those works for you (the "unknown" error is weird) please let me know!
Dec 17, 2008 at 10:43 AM
Edited Dec 17, 2008 at 10:45 AM
Hi,

I installed this component.
I also included it in Toolbox -> Data Flow Transformations.
But when I try to put it in a data flow area, the error comes up:

TITLE: Microsoft Visual Studio

------------------------------

The component could not be added to the Data Flow task.

Could not initialize the component. There is a potential problem in the ProvideComponentProperties method.

------------------------------

ADDITIONAL INFORMATION:

Error at Data Flow Task - insert and update Dim_Expenses data [Kimball Method Slowly Changing Dimension [7709]]: System.IO.FileNotFoundException: Could not load file or assembly 'SSISComponentUtilities, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8b0551303405e96c' or one of its dependencies. The system cannot find the file specified.

File name: 'SSISComponentUtilities, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8b0551303405e96c'

   at MouldingAndMillwork.SSIS.KimballMethodSCDConfiguration..ctor(IDTSComponentMetaData90 componentMetaData)

   at MouldingAndMillwork.SSIS.KimballMethodSCD.ProvideComponentProperties()

   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProvideComponentProperties(IDTSManagedComponentWrapper90 wrapper)

WRN: Assembly binding logging is turned OFF.

To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.

Note: There is some performance penalty associated with assembly bind failure logging.

To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

------------------------------

Could not load file or assembly 'SSISComponentUtilities, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8b0551303405e96c' or one of its dependencies. The system cannot find the file specified. (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------

BUTTONS:

OK

------------------------------

thanks
Coordinator
Dec 18, 2008 at 10:23 PM
Edited Dec 18, 2008 at 10:24 PM
Since you were able to add the component to the toolbox, I know that you've copied the DLL(s) to the "PipelineComponents" folder in the SQL Server Program Files directory tree.  But since it can't load the assembly, I think it's not installed in the GAC.

Please follow the instructions here: http://www.codeplex.com/kimballscd/Wiki/View.aspx?title=Binary%20Installation%20Instructions&referringTitle=Home

The first section "Install both DLLs into the GAC" has the steps that I think you missed.  Make sure you run the commands "as administrator" on Vista, if you have it.  If it worked, you should be able to see "KimballMethodSCD" in your Windows\Assembly folder.

If that's not it, please let me know...

(Another reason for me to finish workitem #210 - an installer... :) )
Dec 30, 2008 at 2:05 AM
I have installed the component per the Wiki Installation Instructions; the component is showing in my toolbox and both files are in my %SystemRoot%\Assembly folder.  When the component is dragged onto my Data Flow design surface a dialog box is displayed with the following:

TITLE: Microsoft Visual Studio
------------------------------
The component could not be added to the Data Flow task.  
Please verify that this component is properly installed.
------------------------------
ADDITIONAL INFORMATION:
------------------------------
The data flow object "MouldingAndMillwork.SSIS.KimballMethodSCD, KimballMethodSCD, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8b0551303405e96c" is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)

Apparently, it's not installed properly but I'm not sure where things went wrong (this is on an XP machine).  My first thought was that the 2005 DLLs were inadvertently installed, but I double checked and that wasn't the case.  As a side note, it may be worth considering naming the 2005 and 2008 DLLs differently for install clarity as well as for people using SSIS 2005 and 2008 on the same box.

Thanks for all your efforts with this component, there is definitely a need for it.


Coordinator
Dec 31, 2008 at 3:36 AM
Edited Dec 31, 2008 at 3:54 AM
You're sure the DLLs are visible in the Assembly subfolder of your Windows folder?  The fact that the component is on your toolbox verifies that it's in the PipelineComponents folder...  If you have the KimballMethodSCD.dll and the SSISComponentUtilities.dll there - then I'm not sure what to suggest.  My only thought is to start from scratch, removing absolutely everything of the component, downloading it again, and installing it again (per instructions).  If that doesn't work, please let me know.  I'll post something up on MSDN forums for SSIS - I know one of the experts there (like Matt Masson) will be able to steer us in the right direction...

Your suggestion for naming the versions differently is an excellent idea, as side by side installations of SQL are supported, so this component ought to support them as well.  I'll do a little research to see what's required in that area...
Dec 31, 2008 at 5:28 AM
Yes, the DLLs are definitely visible in the C:\WINDOWS\assembly folder (albeit without the .DLL extension).  I did a reboot and no difference.  Next, the component was removed from my Toolbox, gacutil /u was run on the two assemblies, the DLLs were deleted from PipelineComponents, re-downloaded the 2008 files, copied to the PipelineComponents folder and gacutil /i was run against the DLLs.  Following is the output from the command session:

C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents>"C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\"gacutil.exe /u KimballMethodSCD
Microsoft (R) .NET Global Assembly Cache Utility.  Version 3.5.30729.1
Copyright (c) Microsoft Corporation.  All rights reserved.

Assembly: KimballMethodSCD, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8b0551303405e96c, processorArchitecture=MSIL
Uninstalled: KimballMethodSCD, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8b0551303405e96c, processorArchitecture=MSIL
Number of assemblies uninstalled = 1
Number of failures = 0

C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents>"C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\"gacutil.exe /u SSISComponentUtilities
Microsoft (R) .NET Global Assembly Cache Utility.  Version 3.5.30729.1
Copyright (c) Microsoft Corporation.  All rights reserved.

Assembly: SSISComponentUtilities, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8b0551303405e96c, processorArchitecture=MSIL
Uninstalled: SSISComponentUtilities, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8b0551303405e96c, processorArchitecture=MSIL
Number of assemblies uninstalled = 1
Number of failures = 0

C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents>"C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\"gacutil.exe /i SSISComponentUtilities.DLL
Microsoft (R) .NET Global Assembly Cache Utility.  Version 3.5.30729.1
Copyright (c) Microsoft Corporation.  All rights reserved.

Assembly successfully added to the cache

C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents>"C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\"gacutil.exe /i KimballMethodSCD.DLL
Microsoft (R) .NET Global Assembly Cache Utility.  Version 3.5.30729.1
Copyright (c) Microsoft Corporation.  All rights reserved.

Assembly successfully added to the cache

C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents>

Everything seemed to go well, but the same issue is persisting.  On this go round I decided to swap the order of loading the assemblies (i.e. SSIS...DLL first and Kimball...DLL second), I'm sure it makes no difference but stranger things have happened.  For what it's worth, following is the stack trace from the error:

   at Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.GetPipelineInfo(String creationName, IServiceProvider serviceProvider)
   at Microsoft.DataTransformationServices.Design.DesignUtils.GetNewPipelineComponentObjectName(IDTSComponentMetaDataCollection100 parentCollection, String clsid, IDTSComponentMetaData100 componentMetadata, PipelineComponentInfo& pipelineComponentInfo)
   at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError)

Thanks for the help.

Coordinator
Jan 1, 2009 at 7:11 PM
Fred,

I've posted a thread over at the MSDN forums.  I'll post up any interesting responses here, but you can subscribe to that MSDN thread directly if you wish, to have it alert you to responses.
Coordinator
Jan 2, 2009 at 5:52 PM
Hi Fred,

From reading above, I gather you're running SQL 2005 on XP.  I have a few questions on details:

1. That's 32-bit XP, correct? 
2. And is that SQL 2005 Developer, or another edition? 
3. Do you have any other Visual Studio products installed?  C#? VB.Net?
Jan 3, 2009 at 3:16 AM
Two items to note:
  1. I'm running SSIS 2008 - from my last post, the "Microsoft SQL Server\100" would be "Microsoft SQL Server\90" if running SQL 2005.
  2. I have installed and am able to use CozyRoc SSIS tasks.
In response to your questions:
  1. That's 32-bit XP, correct?  Yes.
  2. And is that SQL 2005 Developer, or another edition?  Installed from the SQL Server 2008 Enterprise DVD.
  3. Do you have any other Visual Studio products installed?  C#? VB.Net?  See list below from VS 2008 Help-About; one interesting item from the list is the SQL_PreRelease tag next to SSIS.  It's strange since it is showing the RTM version number and it's the only item with that tag.
Microsoft Visual Studio 2008 Version 9.0.30729.1 SP
Microsoft .NET Framework Version 3.5 SP1

Installed Edition: Professional

Microsoft Visual Basic 2008
Microsoft Visual C# 2008
Microsoft Visual Studio 2008 Tools for Office
Microsoft Visual Studio Tools for Applications 2.0
Microsoft Visual Web Developer 2008

Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB944899)   KB944899
Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB945282)   KB945282
Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB946040)   KB946040
Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB946308)   KB946308
Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB946344)   KB946344
Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB946581)   KB946581
Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB947171)   KB947171
Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB947173)   KB947173
Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB947180)   KB947180
Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB947540)   KB947540
Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB947789)   KB947789
Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB948127)   KB948127

Microsoft Visual Studio 2008 Professional Edition - ENU Service Pack 1 (KB945140)   KB945140

SQL Server Analysis Services Version 10.0.1600.22
SQL Server Integration Services Version 10.0.1600.22 ((SQL_PreRelease).080709-1414)
SQL Server Reporting Services Version 10.0.1600.22

CozyRoc SSIS+

Thanks - Fred.

Jan 3, 2009 at 4:27 AM
For additional troubleshooting I downloaded the source code and compiled it on my machine; a .snk file was created locally to strongly name the assemblies.  The component is still failing but the interesting thing is that it now shows on my Data Flow design surface (previously it did not show on the design surface).  The error message is also different:

===================================
Could not load type: MouldingAndMillwork.SSIS.KimballMethodSCDInterface,KimballMethodSCD,Version=1.0.0.0,Culture=neutral,PublicKeyToken=8b0551303405e96c. Verify that the component editor is installed properly.  (Microsoft Visual Studio)
===================================

Could not load file or assembly 'KimballMethodSCD, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8b0551303405e96c' or one of its dependencies. The system cannot find the file specified. (mscorlib)
------------------------------
Program Location:

   at System.RuntimeTypeHandle._GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark& stackMark, Boolean loadTypeFromPartialName)
   at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark& stackMark)
   at System.RuntimeType.PrivateGetType(String typeName, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark& stackMark)
   at System.Type.GetType(String typeName, Boolean throwOnError)
   at Microsoft.DataTransformationServices.Design.DtsComponentDesigner.GetComponentUI()

Coordinator
Jan 3, 2009 at 11:30 PM
That SQL "Prerelease" is interesting, but after checking mine, it shows that too.  (I had to "Copy Info" and paste it to see that.)

You're missing some hotfixes that I have, and I'm missing some you have (but that may be due to you having VB and WD installed).

I think we can rule out the VS environment, and concentrate on the "installation" of the component (in the GAC).  Particularly now that I see the new problem when you compile from source.  The last part of the stack trace indicates it's trying to get the component UI - the editor part.  The higher parts show that it can't load that type because it can't find it.  That's a little unusual, since I compiled the UI in the same DLL as the non-UI part of the component (which the SSIS guys say you should try not to do, in order to provide better runtime performance).

Anyway, since you have the source, this is what I'd suggest:

On the KimballMethodSCD class itself, there's the DtsPipelineComponent attribute.  Delete the "UITypeName" part of that attribute, try and recompile and use the component.  Removing that line will disconnect the UI from the component, so you won't be able to edit it properly, but perhaps that will give us some other information to work with.
Jan 5, 2009 at 3:26 AM
Interesting... I did as you suggested (by removing the UITypeName) and then adding the component was error free.  Of course the UI wouldn't work.  I then added that line back in, recompiled and the same "Could not load type" error was back.

In looking at that line I realized my PublicKeyToken would be different than yours since I had to create my own .snk file.  So I ran the sn (strong name) utility and pasted in the returned public key token, recompiled and the component is now working without error (and I can open the UI).  I haven't actually tested the component with a data flow but I suspect it will work.

I need to circle back and uninstall the assemblies I created from the GAC and reinstall yours to see if anything has changed but I don't have time right now and wanted to get back to you with this update.
Coordinator
Jan 5, 2009 at 4:54 PM
Good news Fred.

I completely understand if you end up not finding time to "go back" and try from the binaries again - I wouldn't have time for that!  But if you do find out what caused the original problem, please let me know...

Thanks!
Jan 29, 2009 at 3:31 PM
Hi,
I get the following error when I try to execute a package which contains only the source and dimension inputs with one surrogate and a composite business key, only SCD1 changes  and the Kimball SCD component :

[Kimball Method Slowly Changing Dimension] Error: Internal error (Error HRESULT E_FAIL has been returned from a call to a COM component.) in PreExecute caching processing information.
[Kimball Method Slowly Changing Dimension [14534]] Error: System.NullReferenceException: Object reference not set to an instance of an object.    at MouldingAndMillwork.SSIS.BusinessKeyManager..ctor(RuntimeInputCache[] caches)    at MouldingAndMillwork.SSIS.KimballMethodSCD.PreExecute()    at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)
[DTS.Pipeline] Error: component "Kimball Method Slowly Changing Dimension" (14534) failed the pre-execute phase and returned error code 0x80004003. 

I use SQL Server 2005 64 bit

Thanks
Coordinator
Jan 29, 2009 at 9:26 PM
Edited Jan 29, 2009 at 9:28 PM
I'll venture a guess and wager that you have some columns marked "Not Used" in the properties, and you're using v1.3 of the component.  If that's the case, you're probably experiencing an existing bug in v1.3 (http://www.codeplex.com/kimballscd/WorkItem/View.aspx?WorkItemId=1017).  The short version of that is that the component is improperly handling "Not Used" input column.  The quick fix is to "use" those columns, or remove them from the flow in upstream components.  The longer fix is to install v1.4.

If that's not it, can you post up some screen shots of the "Existing Dimension" tab and the "Source System" tab of the component editor?  Either that, or a copy of the DTSX if it's not too sensitive...

And may I also suggest that you try v1.4 of the component (http://www.codeplex.com/kimballscd/Release/ProjectReleases.aspx?ReleaseName=v1.4%20for%20SSIS%202005%20and%20SSIS%202008).  If that doesn't fix it outright, at least I'll be in a better position to troubleshoot with you using the latest version.

The error indicates that it's having a problem matching up the lineage IDs (columns) you've specified in the editor with the columns that its being supplied by the runtime in the buffer.  It's probably expecting a column to be there in the buffer, but the SSIS engine has removed that column from the buffer because it's not a "required" column (according to what SSIS knows of the data flow).  The reason v1.3 was failing with the "Not Used" columns was that at runtime, I was attempting to find the location of those columns in the buffer.  The SSIS engine was (under some circumstances, not all) removing those columns from the runtime buffer for performance reasons.  (It's perfectly OK for it to do that!)  I was just a moron for expecting them to be there (even though I didn't use the information later on) and not handling that problem better.

Thanks for your patience!
Feb 12, 2009 at 7:00 PM
I decided to give v1.4 a try.  The install went great this time and everything seems to be going well when running until the records have completed flowing into the component.  Then a DOS window pops up which has SQLDUMPER as part of the title.  One time a “Do you want to send this error to Microsoft?” dialog popped up.  The errors from the Progress window in order of occurrence are:

[KM-SCD Users] Error: Internal error (Object reference not set to an instance of an object.) in ProcessInput adding rows to the cache.
[KM-SCD Users] Error: Internal error (Object reference not set to an instance of an object.) in ProcessKey processing thread dequeueing a key.
[KM-SCD Users] Error: Internal error (Object reference not set to an instance of an object.) in ProcessKey processing thread dequeueing a key.
[KM-SCD Users] Error: Internal error (Object reference not set to an instance of an object.) in ProcessInput adding rows to the cache.
[KM-SCD Users [82]] Error: System.Exception: 2 exceptions thrown!  First: Object reference not set to an instance of an object.
   at MouldingAndMillwork.SSIS.KimballMethodSCD.PostExecute()
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPostExecute(IDTSManagedComponentWrapper100 wrapper)
[SSIS.Pipeline] Error: component "KM-SCD Users" (82) failed the post-execute phase and returned error code 0x80131500.

I also have some random implementation questions: 

1) Most of my dimension tables are using an identity column for new surrogates.  Given that I setup the component to "Not Use" the surrogate key column.  In this situation there is always a Warning icon on the component and the data flow.  Is there a different way to setup things when using an identity column?
2) My package grabs an ETL Log ID and puts it into an SSIS variable to use for logging purposes.  Each of my dimension tables also have an etl_log_id column which gets updated on Type 1 changes and inserted with new adds.  I'm not clear on the best way to handle this using the component; input a derived column with the log id as a separate source, add the log id to the main source input table or directly within the component through some sort of variable assignment.
3) There are a ton of unused column warnings in the Execution Results, what is the best way to clean these up?
4) Is there a way to use variables for Type 2 date handling?

Let me know if you need more detail and thanks for any insights you can provide.
Coordinator
Feb 12, 2009 at 8:16 PM
Fantastic, Fred - thanks for giving it a go, and a very big thanks for the feedback.

Your error is most likely the result of some race conditions in my threading, where I'm obviously not locking resources properly.  I have a few questions that should help me track down what mistake I made.  (Good thing it's still labeled a beta.)

First - if it's at all possible, it would benefit me greatly to get a hold of your package and data.  I'm sure you can understand that - but I also completely understand if it's too sensitive.
Second - have you "optimized" the component by sorting the inputs?  (I think you have from one of your next questions.)
Third - can you exhaustively describe, and/or provide screen shots of the component editor to assist, the component config?  It would help for me to know all what the BKs are (how many, data types), how many columns are involved, approximate row size (bytes), how many rows are on your two or three inputs, what's set on the "options" page, etc...
Fourth - does the error seem to occur consistently at a particular row count of one of the inputs?  Is it inconsistent?
Fifth - can you characterize how data usually flows into the component?  What I mean is - does the "Source" input always (due to how you have the upstream flow set up) arrive in its entirety at the component before the first row of "Existing Dimension" input gets there?  Or does the ED get there "first, completely", or is it "simultaneous"?  (I'm trying to get a feel for the "environment" the component is working in.)

I've answered your other questions in some other new discussion threads:
1. http://www.codeplex.com/kimballscd/Thread/View.aspx?ThreadId=46984
2. http://www.codeplex.com/kimballscd/Thread/View.aspx?ThreadId=46987
3. http://www.codeplex.com/kimballscd/Thread/View.aspx?ThreadId=46989
4. http://www.codeplex.com/kimballscd/Thread/View.aspx?ThreadId=46993

Thanks for your time.
Mar 23, 2009 at 7:15 PM
I recently installed the current v1.4 release (from March 18th) and am running into an error:
[KM-SCD Users] Error: Internal error (Input string was not in a correct format.) in ProcessKey processing thread SCD Handling - SCD1 Update.

Before I started troubleshooting the issue I wanted to ensure the proper files/versions were installed so went to the "C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents" folder and noticed that  there was not an "SSISComponentUtilities.DLL" file; the "KimballMethodSCD.dll" was there with a date of 3/18/09.

My assumption is that the "SSISComponentUtilities.DLL" file is no longer a dependency but wanted to check first before digging deeper on the error.

Thanks for your help.
Coordinator
Mar 23, 2009 at 9:42 PM
Edited Mar 23, 2009 at 9:48 PM

It is a dependency, Fred – but it doesn’t have to be in the Pipeline Components folder.  It just has to be GAC’d (in your Windows\assembly folder).

I’m just writing a note about 1.4 beta – and how it doesn’t want to upgrade itself.  You may want to read that.

I'll look into your (new) error message to see what could be causing the problem!

Coordinator
Mar 24, 2009 at 1:57 AM
Not much to go on there, Fred.  The best I can guess is that it can't convert a string value to a number... but I can't really see where it would want to do that.  It's in the process of copying the values out of the input columns into the current and historical versions of that row.  That's a column-by-column copy, so the metadata should line up for each column (a string input should be a string output, etc...).  Can you narrow it down to a particular row?

One way to do that would be to turn on the "show detailed SCD determination information in output window" option in the Debugging settings.  Then copy the last ten lines or so of the output window after the component dies and paste them back here.
Jun 24, 2009 at 9:13 PM

Hi Todd

i really want to use your component but i cant quite get it working. I've sucessfully installed (using the latest 1.4 Kimball SCD Setup 2005.msi) and it is in my toolbox but when i drag the component onto the design surface i get the same error Fred was reporting earlier in this thread. I dont have access to the source so im not quite sure how he got past it. Here is the exact error message from VS (SQL 2005 BIDs)

TITLE: Microsoft Visual Studio
------------------------------

The component could not be added to the Data Flow task.
Could not initialize the component. There is a potential problem in the ProvideComponentProperties method.

------------------------------
ADDITIONAL INFORMATION:

Error at Data Flow Task [Kimball Method Slowly Changing Dimension [73]]: System.MissingMethodException: Method not found: 'Void MouldingAndMillwork.SSIS.Utility.PostInformation(Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90, System.String)'.
   at MouldingAndMillwork.SSIS.KimballMethodSCDConfiguration.PostDebugInformation(String message, DebugMessageType messageType)
   at MouldingAndMillwork.SSIS.KimballMethodSCD.ProvideComponentProperties()
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProvideComponentProperties(IDTSManagedComponentWrapper90 wrapper)


------------------------------

Method not found: 'Void MouldingAndMillwork.SSIS.Utility.PostInformation(Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90, System.String)'. (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------

I went back and attempted to re register the component in the GAC and appears to have been successful - here is the output

C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin>gacutil.exe /i "C:\Program Fil
es\Microsoft SQL Server\90\DTS\PipelineComponents\SSISComponentUtilities.dll"
Microsoft (R) .NET Global Assembly Cache Utility.  Version 3.5.30729.1
Copyright (c) Microsoft Corporation.  All rights reserved.

Assembly successfully added to the cache

C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin>gacutil.exe /i "C:\Program Fil
es\Microsoft SQL Server\90\DTS\PipelineComponents\KimballMethodSCD.dll"
Microsoft (R) .NET Global Assembly Cache Utility.  Version 3.5.30729.1
Copyright (c) Microsoft Corporation.  All rights reserved.

Assembly successfully added to the cache

C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin>

 

Thanks - any help would be greatly appreciated

 

Coordinator
Jun 24, 2009 at 11:15 PM

The very first error you showed there where it's complaining that a method wasn't found indicates that the KimballSCD DLL installed, but the SSISComponentUtilities did not.

Fred was having problems with v1.3 and v1.4 beta - before I had an installer made up.  Did you use the installer?  You may want to completely remove the component and try installing again.  Make sure the GAC and the PipelineComponents folders are free of both DLLs.  Then run the installer and make sure they get put there.  Post back with results...

Jun 25, 2009 at 12:20 AM

Thanks for the quick response

Yes i did use the msi for sql 2005. Any way to test your hypothosis? in otherwords what constitiutes "installed"? i verified that both KimballMethodSCD.dll and SSISComponentUtilites.dll are in C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents and both successfully registered with GAC - i also see both in C:\WINDOWS\assembly.

Also some more info on my setup. I didnt think i fell into the camp of trying to install two side by side instances http://kimballscd.codeplex.com/WorkItem/View.aspx?WorkItemId=2389 because the machine i am installing on does not have both SQL 05 and 08 on it. It does however have both Visual Studio Team System 2008 and BIDs 2005 (plus SQL 2005 Client tools) on it. I dont really think this has anything to do with my issue but wanted to point it out as i do have both a /90/ and /100/ folders under my SQL in program files - but again i verified that the dll's were in the correct folder.

I'm going to unregister GAC, uninstall (using add remove programs), reboot (why not try the ole 3 finger salute?) and try the reinstall

Jun 25, 2009 at 12:48 AM

I'm happy to report that it worked the second time although i am at a total loss as to why becuase honestly i didnt do anything different this time around. To correct my previous post, i did not have to unregister by command line the GAC - in fact i couldnt because of the reference to the MSI - so i simply uninstalled from add remove programs and verified that the installer did the right thing and cleaned up after itself. I then launched Bids and removed Kimball SCD from the toolbox and rebooted. I then simply re installed using the MSI and this time it worked.

Nov 4, 2009 at 6:03 PM

I installed KimballMethodSCD on a 64 bit server with no install errors. When I go to add the component to SSIS on the SSIS Data Flow Items tab it is not there to select.

How do I get it to show up on the tab?

Thanks

Nov 4, 2009 at 6:11 PM

ok so this is how you fix my problem.

go to add remove programs and remove it. Then remove it with the msi file then install it again and it shows up in the SSIS Data Flow Items tab!!!

not sure why it did not work the first time - although trying to fix this I did a bunch of MS updates to the server.

Jan 6, 2010 at 10:48 PM

For what it's worth, SQL Server 2005, Win XP professional here.  Nothing 2008 (VS or SS) on the box and I was running into the above issues (The data flow object ... is not installed correctly on this computer).

Everything appeared to be in order but didn't work.  Did the uninstall, reboot and reinstall and things seemed to be working fine now.  If it helps any, below are the listings of the dlls on disk before and after the re-install.  Sizes all seem to mesh so I'm actually at a loss to explain why it didn't like it the first round.  My gut says the reboot did something magical

C:\>dir /o /s \WINDOWS\assembly\Kim* \WINDOWS\assembly\SS*
Volume in drive C has no label.
Volume Serial Number is 20CF-14EF

Directory of C:\WINDOWS\assembly\GAC_MSIL

01/04/2010 10:26 AM <DIR> KimballMethodSCD
01/04/2010 10:26 AM <DIR> SSISComponentUtilities
0 File(s) 0 bytes

Directory of C:\WINDOWS\assembly\GAC_MSIL\KimballMethodSCD\1.0.0.0__8b0551303405e96c

01/04/2010 10:26 AM 274,432 KimballMethodSCD.dll
1 File(s) 274,432 bytes

Directory of C:\WINDOWS\assembly\GAC_MSIL\SSISComponentUtilities\1.0.0.0__8b0551303405e96c

01/04/2010 10:26 AM 49,152 SSISComponentUtilities.dll
1 File(s) 49,152 bytes

C:\>dir /o /s "C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents\Kim*" "C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents\SS*"
04/06/2009 02:40 PM 274,432 KimballMethodSCD.dll
04/06/2009 02:25 PM 38,400 SSISComponentUtilities.dll


C:\>dir /o /s \WINDOWS\assembly\Kim* \WINDOWS\assembly\SS*
Volume in drive C has no label.
Volume Serial Number is 20CF-14EF

Directory of C:\WINDOWS\assembly\GAC_MSIL

01/06/2010 03:24 PM <DIR> KimballMethodSCD
01/06/2010 03:24 PM <DIR> SSISComponentUtilities
0 File(s) 0 bytes

Directory of C:\WINDOWS\assembly\GAC_MSIL\KimballMethodSCD\1.0.0.0__8b0551303405e96c

01/06/2010 03:24 PM 274,432 KimballMethodSCD.dll
1 File(s) 274,432 bytes

Directory of C:\WINDOWS\assembly\GAC_MSIL\SSISComponentUtilities\1.0.0.0__8b0551303405e96c

01/06/2010 03:24 PM 49,152 SSISComponentUtilities.dll
1 File(s) 49,152 bytes

Total Files Listed:
2 File(s) 323,584 bytes
2 Dir(s) 22,988,206,080 bytes free

C:\>dir /o /s "C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents\Kim*" "C:\Program Files\Microsoft SQL Server\90\DTS\Pipeline
Components\SS*"
Volume in drive C has no label.
Volume Serial Number is 20CF-14EF

Directory of C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents

04/06/2009 02:40 PM 274,432 KimballMethodSCD.dll
04/06/2009 02:25 PM 38,400 SSISComponentUtilities.dll
2 File(s) 312,832 bytes

Total Files Listed:
2 File(s) 312,832 bytes
0 Dir(s) 22,988,296,192 bytes free

Jan 13, 2010 at 11:36 PM

What is the process for upgrading ?  I replaced the 1.3 version with the 1.5 beta and got it working, but now I get errors like this for each SCD:

 

 

TITLE: Editing Component
------------------------------
The component has detected potential metadata corruption during validation.
Error at Populate DIM_Account [Kimball Method Slowly Changing Dimension [316]]: System.TypeLoadException: Could not load type 'MouldingAndMillwork.SSIS.Utility' from assembly 'SSISComponentUtilities, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8b0551303405e96c'.
   at MouldingAndMillwork.SSIS.KimballMethodSCD.Validate()
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostValidate(IDTSManagedComponentWrapper100 wrapper)
Due to limitations of the Advanced Editor dialog box, this component cannot be edited using this dialog box.
------------------------------
BUTTONS:
OK
------------------------------

TITLE: Editing Component

------------------------------

The component has detected potential metadata corruption during validation.

Error at Populate DIM_Account [Kimball Method Slowly Changing Dimension [316]]: System.TypeLoadException: Could not load type 'MouldingAndMillwork.SSIS.Utility' from assembly 'SSISComponentUtilities, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8b0551303405e96c'.

   at MouldingAndMillwork.SSIS.KimballMethodSCD.Validate()

   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostValidate(IDTSManagedComponentWrapper100 wrapper)

Due to limitations of the Advanced Editor dialog box, this component cannot be edited using this dialog box.

 

Do I neet to edit the XMl to reflect the new component or something ?

 

Thanks.

 

Coordinator
Jan 15, 2010 at 6:06 PM

It sounds like the install of 1.5 didn't go smoothly.  The error message is basically saying that the second DLL did not get GAC'd.

I recommend that you:

  1. Close VS
  2. Uninstall KSCD 1.5
  3. Install KSCD 1.5
  4. Open VS

I don't know if bfellows installed the component with VS already open or not - but it's possible that having VS open could interfere with a proper installation, requiring a reboot to "complete" it.

Jan 18, 2010 at 5:41 AM

I did that and it still didn't work, so U manually registered with the gacutil.  It then got farther, but failed upgrading my components.

Jan 21, 2010 at 4:29 PM

I have tried multiple times with and without reboots and ensuring that I have VS closed when I install and it's not working.

1. After installing 2008 x86 on XP I have KimballMethodSCD100 in the GAC, but not SSISComponentUtilities. Running gacutil to put SSISComponentUtilities in the GAC doesn't seem to make any difference to the results I get.

2. I can see the 1.5 component in the Toolbox and add it to a new SSIS package

3. Upgrading existing packages fails miserably, errors include:

  • Validation error. ... could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
  • Error loading Member Dimensions.dtsx: The managed pipeline component "MouldingAndMillwork.SSIS.KimballMethodSCD, KimballMethodSCD, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8b0551303405e96c" could not be loaded. The exception was: Could not load file or assembly 'KimballMethodSCD, Version=1.0.0.0, Culture=neutral, PublicKeyToken=8b0551303405e96c' or one of its dependencies. The system cannot find the file specified.
  • Error loading Member Dimensions.dtsx: The "component "Kimball Method Slowly Changing Dimension" (280)" failed to cache the component metadata object and returned code 0x80131600.
  • Error loading Member Dimensions.dtsx: Component "component "Kimball Method Slowly Changing Dimension" (280)" could not be created and returned error code 0xC0047067. Make sure that the component is registered correctly.
  • Error loading Member Dimensions.dtsx: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "".

Is the upgrade supposed to be completely automatic? It seems like the problem loading teh KimballMethodSCD (without the 100 in its name) is really the problem, is there anything I can do manually to get my existing packages to load the new version instead of trying to find the old one?

Todd, I'd also suggest setting the version number in the assembly, it is currently set to 1.0.0.0 for all versions which makes troubleshooting more tricky because when you look at a DLL there's no way to reliably know you have the correct one.

Coordinator
Jan 24, 2010 at 7:31 PM

Thanks for trying so hard.  I do have some answers for you.  In this case, I'm assuming you're talking about 1.5 - cause that's all I'm going to talk about here :)

First, I'm not sure why the installer isn't putting SSISComponentUtilities into your GAC.  From my tests of the installer, it does - but I haven't yet gone through my last pass of making the installer and vetting it. 

And no, looking at what's going on, and remembering what I'd decided earlier - the upgrade is not automatic.  Sigh.  This is what you need to do to upgrade from v1.4 to v1.5 (I will be putting this in the release notes):  First, you can uninstall v1.4 completely.  (This installs the binaries for v1.4, not the information in your SSIS packages.)  Second, install v1.5, ensuring you have both DLLs in the GAC, and the KSCD DLL in the PipelineComponents folder.  Next, BACK UP YOUR PACKAGES.  Then open each dtsx in NotePad (or other text editor, and change all occurrences of "SSIS.KimballMethodSCD, KimballMethodSCD, Version=1.0.0.0" to "SSIS.KimballMethodSCD, KimballMethodSCD100, Version=1.0.0.0".  You can now open your packages and the automated part of the upgrade will take place.

As for your last question - I can't do that.  If I changed the version number of the assembly, the same thing would happen as has already happened between v1.4 and v1.5.  However, it makes no sense to change the version number now - because I'd have to change it next version again.  At least with this release, I gain the "side by side" feature I was looking for - and don't ever risk losing that, or smooth upgrading as long as I don't touch the strong name of the assembly again.

What I do intend to do is to put the version number on the installer title (as you'd suggested) AND the internal metadata version number on the About screen.

Dec 23, 2010 at 10:52 PM

We have a QA server and a Prod server, which have different versions of 1.5 x64 -- QA uses 1.5.0, Prod uses 1.5.3.  Is there a way to get the install files for version 1.5.0 to deploy to our production box instead of 1.5.3?  There is an error related to the following message which we would like to ignore on production until we can make the appropriate changes to the package that worked properly on QA:  "Description: Some SCD 2 Housekeeping columns are specified, but no columns are identified as tracking SCD2 changes."

Coordinator
Dec 30, 2010 at 11:54 PM

All you'd have to do is uninstall v1.5.3 and install v1.5.0, assuming you still have the installer for that.  If you don't, then all you need to do is manually install the DLLs according to the manual instructions at the bottom of the "Installation" page.

Feb 1, 2011 at 6:25 AM

Hi,

I am a newbie on this forum. 

I am using SSIS 2008 R2 in a new data warehouse project and am very excited to try out Kimball SCD component. In my previouse warehousing projects I used stored procedures to maintain SCD tables. That worked fine but lacked row level auditing and the code could sometimes get too complex.

So, I added the Kimball SCD to the data flow, ran the package in BIDS, and now need some help interpreting the otcome.

My data source is a wide SQL Server table with nearly 200 columns. It represents a denormalised data extract from the operational system. I am importing it to the data warehouse, which is normalised, so a single source row is sent to multiple destination tables, which use only some of the source columns. For example, UserId and UserName are sent to the Users dimension table, wheras AccountNumber and AccountType are sent to the Accounts dimension table.

For each dimension table I would like to use the Kimball Method SCD Component. I set it up to try on one dimension table which was originally empty.

Outcome of the run:

1. No rows added to the table - it remains empty.

2. No errors - package completed successfully, all boxes are green.

3. Execution Results tab shows the following lines:
[Kimball Method SCD] Information: Input rows processed: 0 Existing Dimension, 0 Special Members, 18026 Source System
[Kimball Method SCD] Information: Rows output: 0 Unchanged, 1177 New, 1177 Deleted, 0 SCD1, 0 SCD2 Expired, 0 SCD2 New, 16849 Invalid Input

Looking at "Invalid Input" output I found that all these 16849 rows have "Duplicate business key". Why is this a problem? The source has multiple rows with the same business key and I expected the component to sort them out. Does the component expect no more than 1 row per business key in the source system?

The "New rows" output shows 1177 rows. Why the dimention table remains empty?

The "Deleted" output contains 0 rows, but the message above shows "1177 deleted". Where is the truth?

Sorry for asking too many questions in one post.

Feb 2, 2011 at 4:57 AM
Edited Feb 2, 2011 at 7:11 AM

Ok, I figured out my problem. I was expecting too much from the component foolishly thinking it would insert and update dimension table rows, whereas it just creates the outputs. This raises a question: why this functionality has not been built in? In my usage scenario it would be a deciding factor for using the component. As described in my first posting, I am using a 200-column table to load normalised data warehouse schema. There are 15 warehouse tables to load, 6 of them are SCD tables.

So, I was hoping my data flow could multicast the source table to 15 paths, 6 of them using SCD component downstream. If the component incorporated dimension table maintenance I would need to add 12 boxes for SCDs - 6 source dimensions and 6 SCD components. The data flow in this case would look heavy but manageable.

Using the current component design I would need 6 x 5 = 30 boxes (including destinations and OLE DB updates), which feels a bit too busy given I'll have another 9 non-SDC tables to load within the same data wlow. I do like the component pretty much, so an alternative would be to create separate data flow tasks for each SCD table. Of course it would result in 6 passes of the same source table, but the package design would be more modular.

Todd, have you though of incorporating SDC table maintenance within the component? What are the pros and cons in your opinion?

Feb 4, 2011 at 12:33 AM

With respect, building the table maintenance into the component is not desireable as it would require building a component capable of handing all the various database, file and other connectivity and mapping into this component. My suggestion for this situation is develop a pattern for getting this loaded in one package and copy that package in it's entirety to jump start development of the subsequent packages. I have used this very sucessfully as a consultant and know also that their are tools to refine and assist with this very process. Best of luck.

Feb 7, 2011 at 10:21 PM

I have a newbie question.  I have a data flow with a Kimball Slowly Changing Dimension (KSCD).  The KSCD has two sources:  an OLE DB Source and a SharePoint List Source.  Should the KSCD combine the outputs of both sources?  For example, if one source has 20,000 rows and the other source has 25,000 should the KSCD process a total of 45,000 rows?  If so, that's not what is happening for me.

 

Please help!

Coordinator
Apr 8, 2011 at 7:38 PM

I don't understand how your sources are related.  Are they?