1

I have a problem with changing the properties of the SCD transformation in SSIS 2005.

I can define all the properies and mappings the first time i edit the transformation and its working.

But when i open the property editor of the component again, it has forgotten all the entered configuration like the mappings of the table columns and the settings of the columns.

I even have searched in the generated XML for the settings but have not found anything meaningful.

SSIS has definitly stored the components configuration somewhere because its working fine until i open the editor.

I have searched the web but with no luck yet. Does anybody know where the settings are stored respectively how to force the components editor/assistant to not forget all my settings?

Jan
  • 15,802
  • 5
  • 35
  • 59

2 Answers2

3

The SCD WIZARD in SSIS is not an editor, it is a wizard. That is one of its biggest drawbacks. It doesn't remember any configuration. Every time you use the wizard, it starts again from the beginning and recreates all of the object it uses to do the slowly changing dimension. If you want an SCD component that will remember the setting between uses, you should probably use the excellent tool by Todd McDermid: DimensionMerge SCD Component

William Salzman
  • 6,396
  • 2
  • 33
  • 49
  • 1
    The SSIS SCD has a wizard and an (partial) editor (in the context menu). Ok, the wizard recreates all the following objects but it remembers the column mapping and column settings. I have found this issue on connect where microsoft says its a known bug that when meta data on input changes, SCD will loose **all** its configuration. Ok, i will take a look at teh DImensionMerge component. – Jan Sep 29 '11 at 21:10
1

I have had this problem too for almost 2 weeks crawling through the net to find the solution.I finally found a way out. With the xml definition of the SCD component, specifically :

<dwd:DtsComponentDesignTimeProperty> <dwd:key xsi:type="xsd:string">50 SCDMetadata</dwd:key>
<dwd:value xsi:type="xsd:string">&lt;?xml version="1.0" encoding="utf-16"?&gt; &lt;SCDWizardMetadata xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt; &lt;ConnectionID&gt;{8F2DCF5E-0FC4-4CF4-AF30-87548F5C74A3}&lt;/ConnectionID&gt;

It is IMPORTANT that the the content of the tag dwd:value is on same line as the tag itself. If there is a line break, the SCD wizard will not load your settings. I know, its wired. Also the values with the dqd:key should be seperated by a tab. The most important is that the content of the dwd:value start on same line. Hope this helps someone out.

techdreams
  • 5,371
  • 7
  • 42
  • 63
Valentine Obodoechi
  • 11
  • 4
  • My DTSX did not have exactly the format you posted, but it was encoded XML all the same. I was able to decode the XML payload and understand the configuration so that I could recreate it in the wizard. Thanks for the pointer. – CBono Sep 25 '18 at 14:47