Need code to generate and SSIS Package with BIML containing a COZYROC Table Difference component

Question

Can anyone please help with a code sample showing how to generate an SSIS package using BIML that contains a COZYROC Table Difference Component?

I found a piece of sample code on BimlScript.com that looks like the code you would expect to see up to the COZYROC Table Difference component but does not actually contain the Table Difference part. https://www.bimlscript.com/Snippet/Details/63

Here is the code I used for the table diff component:

<CustomComponent Name="TableDiff" ComponentTypeName="CozyRoc.SqlServer.SSIS.TableDifference, CozyRoc.SSISPlus.2019, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34ea" Version="3">
  <CustomProperties>
      <CustomProperty Name="NewInputLineageIDs" DataType="Int32" IsArray="true" TypeConverter="" UITypeEditor="" ContainsId="true"></CustomProperty> 
      <CustomProperty Name="OldInputLineageIDs" DataType="Int32" IsArray="true" TypeConverter="" UITypeEditor="" ContainsId="true"></CustomProperty> 
      <CustomProperty Name="KeyOrders" DataType="Int32" IsArray="true" TypeConverter="" UITypeEditor=""></CustomProperty>
      <CustomProperty Name="UpdateIDs" DataType="Int32" IsArray="true" TypeConverter="" UITypeEditor=""></CustomProperty>
      <CustomProperty Name="CheckOptions" DataType="Int32" IsArray="true" TypeConverter="" UITypeEditor=""></CustomProperty>
      <CustomProperty Name="Names" DataType="String" IsArray="true" TypeConverter="" UITypeEditor=""></CustomProperty>

      <CustomProperty Name="StringCompareCultureId" DataType="Int32" TypeConverter="" UITypeEditor="">0</CustomProperty>
      <CustomProperty Name="StringCompareIgnoreCase" DataType="Boolean" TypeConverter="" UITypeEditor="">false</CustomProperty>
      <CustomProperty Name="StringCompareIgnoreKana" DataType="Boolean" TypeConverter="" UITypeEditor="">false</CustomProperty>
      <CustomProperty Name="StringCompareIgnoreWidth" DataType="Boolean" TypeConverter="" UITypeEditor="">false</CustomProperty>
      <CustomProperty Name="StringCompareIgnoreNonSpace" DataType="Boolean" TypeConverter="" UITypeEditor="">false</CustomProperty>
      <CustomProperty Name="StringCompareIgnoreSymbols" DataType="Boolean" TypeConverter="" UITypeEditor="">false</CustomProperty>
      <CustomProperty Name="StringCompareSort" DataType="Boolean" TypeConverter="" UITypeEditor="">false</CustomProperty>
      <CustomProperty Name="EnableLogOutput" DataType="Boolean" TypeConverter="NOTBROWSABLE" UITypeEditor="">false</CustomProperty>
      <CustomProperty Name="IncludeInputColumnsInLogOutput" DataType="Boolean" TypeConverter="" UITypeEditor="">true</CustomProperty>
  </CustomProperties>

  <Annotations>
      <Annotation AnnotationType="Description">Find the difference between two data flows."</Annotation>
  </Annotations>

  <InputPaths>
      <InputPath OutputPathName="MuilticastLeft.Output1" SsisName="New Data Flow" Identifier="NEW"></InputPath>
      <InputPath OutputPathName="MuilticastRight.Output1" SsisName="Old Data Flow" Identifier="OLD"></InputPath>
  </InputPaths>

Given the supplied biml the only thing that looks "weird" in the markup is `TypeConverter="NOTBROWSABLE"` I haven't done much with CustomComponents nor do I have CozyRoc stuff installed. Might also look at posting a support request over in the Varigence support channel https://support.varigence.com The error you're reporting, you'd have to open the dtsx in a text editor and you'll find a `` or `` sitting there which looks like it's an issue when columns aren't mapped, maybe? — billinkc, Sep 29 '22 at 21:13

score 0 · Answer 1 · answered Sep 29 '22 at 14:12

BimlExpress allows you to reverse engineer an existing SSIS package to Biml. Since I don't have the cozyroc componentry installed on my machine, I can take a guess based on the linked component but in general, this will be a CustomComponent with the CozyRoc specific bits filled in.

First will be the name of the assembly (guessed) along with the public key token. You can right click on the associated dll, select Properties and get this information from the Details tab.

<CustomComponent Name="Table Difference" ComponentTypeName="CozyRoc.SqlServer.SSIS.TableDifference, CozyRoc.SSISPlus.XXXX, Version=1.0.0.0, Culture=neutral, PublicKeyToken=XXXXXXXXXX" ContactInfo="">
    <CustomProperties>
        <CustomProperty Name="IgnoreCase" DataType="Boolean" TypeConverter="" UITypeEditor="">false</CustomProperty>
        <!-- etc -->
    </CustomProperties>
    <InputPaths>
        <InputPath OutputPathName="OLE DB Source.Output" SsisName="Input" ErrorOrTruncationOperation="" Identifier="Input" />
    </InputPaths>
    <OutputPaths>
        <OutputPath Name="Output" ErrorOrTruncationOperation="" SynchronousInput="Input" />
    </OutputPaths>
</CustomComponent>

Generally, I'd expect you'll be adding a CustomProperty for all the settings in the component. As you can see, the table difference is much more complex http://www.cozyroc.com/ssis/table-difference than the Flow Synchronization https://www.cozyroc.com/ssis/flow-synchronization with the many parameters vs 3.

Thanks billinkc. I did manage to add biml script code for the Table Diff component but the resultant dtsx package has errors when I try to open it. Specifically, I get An expected element "arrayElements" was not found. I posted the code I used for TablDiff in the original question above. — DrLechter, Sep 29 '22 at 17:00

Need code to generate and SSIS Package with BIML containing a COZYROC Table Difference component

1 Answers1