Using Biml as an SSIS Design Patterns Engine – Level 4

  • Andy Leonard

    SSCrazy Eights

    Points: 9895

    Comments posted to this topic are about the item Using Biml as an SSIS Design Patterns Engine – Level 4

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

  • ehansen

    SSC Enthusiast

    Points: 143

    The first foreach loop does not have the corresponding <# } #> closure. This results in biml validation errors when generating the SSIS packages

    Severity Description Recommendation Line Column

    Error } expected 524 2

    This is resolved by adding the <# } #> line just above the </Packages> endtag.

  • Misha_SQL

    SSCertifiable

    Points: 5385

    Thank you, Andy, for the article and thank you, ehancen, for the pointing out the bug in the BIML.

    My local instance of SQL Server is a named instance, so I had to change from (local) server name to SERVER\INSTANCE, but it didn't work. Then I realized that I needed to double up on the slashes: SERVER\\INSTANCE, however it still didn't work (this time with a different error). I eventually connected to a server with a default instance and everything worked, but it would be interesting to know how to connect to named instances.

    Thank you!

  • Peter Schott

    SSCrazy Eights

    Points: 9599

    Really hoping that a future article can give us a bit more detail on what you're using to build these scripts or where you learn to use the various C# functionality. My BIML worked, but I never did get rid of the errors and warnings in the file. I can see the logic behind what you did, but seems like I'm missing something in how we would do this without a more fundamental understanding or a better tool. (Saw the Varigence tool, but that's a harder sell per license. 🙂 )

    Anyway, I get the power and am really interested in learning more. I look forward to learning how we get from Article #3 to Article #4. There was a huge jump in the use of BIML between those two articles. Thanks for writing and bringing us along.

  • stumap

    SSC Enthusiast

    Points: 130

    This article helped me get rid of the xml validation errors http://stackoverflow.com/questions/12259217/visual-studio-2012-t4-templates-generating-xml-gives-error

    i.e. Solution Explorer > Right Click on .biml file > Open With > Source Code Editor [Set as Default], but the formatting is not as intuitive

  • sergey_yani

    SSC Enthusiast

    Points: 172

    Hello.

    There is a mistake in post.

    In "Listing 6 – Adding the Packages Node and Starting a Loop" loop defined, but never get closing tag.

    There must be tag " <# } #> " before "</Packages>" right behind "</Package>" .

  • juerg.maier

    SSC Enthusiast

    Points: 132

    With a named instance use a double slash only in the "CreateConnectionNode()" function and not in the <Connections> section.

  • Peter Schott

    SSCrazy Eights

    Points: 9599

    Conceptually, how would you handle NULL DB values going through this process when you hit the Conditional Split transform? Would you ensure there were no NULL values by tweaking the source queries? Would you throw additional NULL checks in the expression? We have several columns that allow NULL that would go through something like this and I'm curious what others are doing to get around that? It runs fine until we try to compare a NULL to something else in that expression.

  • g.britton

    SSChampion

    Points: 13685

    Dear Andy,

    I'm working through the BIML stairway series as SqlServerCentral and I think I've hit a wall.

    I have several instances of SQL Server on my laptop, so I need to specify a specific instance. The first few lines of the code for step 4 read:

    <#@ import namespace="System.Data" #>

    <#@ import namespace="Varigence.Hadron.CoreLowerer.SchemaManagement" #>

    <# var connection = SchemaManager.CreateConnectionNode("SchemaProvider", "Data Source=(local);Initial Catalog=SSISIncrementalLoad_Source;Provider=SQLNCLI11.1;Integrated Security=SSPI;"); #>

    <# var tables = connection.GenerateTableNodes(); #>

    However, I have to change the Data Source to

    Data Source=.\sql2012

    Note that this is usually valid in a connection string and I use it all the time. However, BIML complains:

    c:\Users\Redirection\brittg2\Documents\Visual Studio 2012\Projects\BIML\BIML\GenerateStagingPackages.biml(3,87) : Error 0 : Unrecognized escape sequence.

    I think that BIML sees the back-slash and things it's an escape sequence, or is that the XML parser?

    Anyway, I replaced the single back - slash with a double. That got me further, but I still have an error:

    Validating BIML

    1/4 Emitting Project IncrementalLoad_Source1.dtproj.

    1/1 Emitting Package IncrementalLoad_Source1.

    (-1,-1) : Error 0 : Node Source1 Source: Could not execute Query on Connection SSISIncrementalLoad_Source:

    SELECT [ColID], [ColA], [ColB], [ColC] FROM [dbo].[Source1]

    Instance failure.

    FWIW, Building a package in the designer worked no problem. Made an OLEDB connector to SSISIncrementalLoad_Source. Added an OLDEDB source adapter to read the table [dbo].[Source1]. No problem!

    Oh also, I ran Profiler to try see what was happening. As far as I can see, the query mentioned in the error:

    SELECT [ColID], [ColA], [ColB], [ColC] FROM [dbo].[Source1]

    Was never sent to the server. Is this a problem in the error reporting?

    How do I get past the "Instance Failure" error?

    Edit: I solved the failure, the problem is that the backslash must be escaped in C# but NOT in XML

    Gerald Britton, MCSE-DP, MVPToronto PASS Chapter[/url]

  • TheComedian

    SSCrazy

    Points: 2928

    Why wasn't the mistake corrected in the code once pointed out?

  • prathmanu

    Old Hand

    Points: 371

    This packages will track Inserts and Updates. What changes we need to apply to implement the deletes?

  • prathmanu

    Old Hand

    Points: 371

    Have Implemented this with CDC but I am getting the error :

    Error: 0xC0047062 at DFT Incremental load_Source1, CDCSplitter [92]: System.ArgumentException: Value does not fall within the expected range.

    at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer100.DirectRow(Int32 hRow, Int32 lOutputID)

    at Attunity.SqlServer.CDCSplit.CdcSplitterComponent.ProcessInput(Int32 inputId, PipelineBuffer buffer)

    at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)

    Error: 0xC0047022 at DFT Incremental load_Source1, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "CDCSplitter" (92) failed with error code 0x80070057 while processing input "CDCSource_Output_CDCSplitter" (94). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

    and my custom CDC splitter component is

    <CustomComponent Name="CDCSplitter"

    ComponentClassId="{874F7595-FB5F-40FF-96AF-FBFF8250E3EF}"

    ComponentTypeName="Attunity.SqlServer.CDCSplit.CdcSplitterComponent, Attunity.SqlServer.CDCSplit, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c"

    ContactInfo="Attunity Ltd.; All Rights Reserved; http://www.attunity.com;"

    UsesDispositions="true"

    Version="2"

    ValidateExternalMetadata="false">

    <Annotations>

    <Annotation AnnotationType="Description">Directs a stream of net change records into different outputs based on the type of the change (Insert, Delete and Update). This allows specific handling for different types of change records.</Annotation>

    </Annotations>

    <InputPaths>

    <InputPath Identifier="Input" OutputPathName="CDCSource.Output">

    <InputColumns>

    <InputColumn SourceColumn="__$start_lsn" />

    <InputColumn SourceColumn="__$operation" />

    <InputColumn SourceColumn="__$update_mask" />

    <# foreach (var column in table.Columns) { #>

    <InputColumn SourceColumn="<#=column.Name#>" />

    <# } #>

    </InputColumns>

    </InputPath>

    </InputPaths>

    <OutputPaths>

    <OutputPath Name="InsertOutput">

    <Annotations>

    <Annotation AnnotationType="Description">Output type - Insert.</Annotation>

    </Annotations>

    <CustomProperties>

    <CustomProperty Name="OutputType" DataType="Int32"

    TypeConverter="Attunity.SqlServer.CDCSplit.OutputType, Attunity.SqlServer.CDCSplit, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c"

    >0</CustomProperty>

    </CustomProperties>

    <OutputColumns>

    <OutputColumn Name="__$start_lsn" DataType="Binary" Length="10" />

    <OutputColumn Name="__$operation" DataType="Int32" />

    <OutputColumn Name="__$update_mask" DataType="Binary" Length="128" />

    <# foreach (var column in table.Columns) { #>

    <OutputColumn Name="<#=column.Name#>" DataType="<#=column.DataType#>" Length="<#=column.Length#>" />

    <# } #>

    </OutputColumns>

    <ExternalColumns />

    </OutputPath>

    <OutputPath Name="UpdateOutput">

    <Annotations>

    <Annotation AnnotationType="Description">Output type - Update.</Annotation>

    </Annotations>

    <CustomProperties>

    <CustomProperty Name="OutputType" DataType="Int32"

    TypeConverter="Attunity.SqlServer.CDCSplit.OutputType, Attunity.SqlServer.CDCSplit, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c"

    >1</CustomProperty>

    </CustomProperties>

    <OutputColumns>

    <OutputColumn Name="__$start_lsn" DataType="Binary" Length="10" />

    <OutputColumn Name="__$operation" DataType="Int32" />

    <OutputColumn Name="__$update_mask" DataType="Binary" Length="128" />

    <# foreach (var column in table.Columns) { #>

    <OutputColumn Name="<#=column.Name#>" DataType="<#=column.DataType#>" Length="<#=column.Length#>" />

    <# } #>

    </OutputColumns>

    </OutputPath>

    <OutputPath Name="DeleteOutput">

    <Annotations>

    <Annotation AnnotationType="Description">Output type - Delete.</Annotation>

    </Annotations>

    <CustomProperties>

    <CustomProperty Name="OutputType" DataType="Int32"

    TypeConverter="Attunity.SqlServer.CDCSplit.OutputType, Attunity.SqlServer.CDCSplit, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c"

    >2</CustomProperty>

    </CustomProperties>

    <OutputColumns>

    <OutputColumn Name="__$start_lsn" DataType="Binary" Length="10" />

    <OutputColumn Name="__$operation" DataType="Int32" />

    <OutputColumn Name="__$update_mask" DataType="Binary" Length="128" />

    <# foreach (var column in table.Columns) { #>

    <OutputColumn Name="<#=column.Name#>" DataType="<#=column.DataType#>" Length="<#=column.Length#>"/>

    <# } #>

    </OutputColumns>

    </OutputPath>

    <OutputPath Name="ErrorOutput" IsErrorOutput="true">

    <Annotations>

    <Annotation AnnotationType="Description">Output type - Error.</Annotation>

    </Annotations>

    <CustomProperties>

    <CustomProperty Name="OutputType" DataType="Int32"

    TypeConverter="Attunity.SqlServer.CDCSplit.OutputType, Attunity.SqlServer.CDCSplit, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c"

    >3</CustomProperty>

    </CustomProperties>

    <OutputColumns>

    <OutputColumn Name="__$start_lsn" DataType="Binary" Length="10" />

    <OutputColumn Name="__$operation" DataType="Int32" />

    <OutputColumn Name="__$update_mask" DataType="Binary" Length="128" />

    <# foreach (var column in table.Columns) { #>

    <OutputColumn Name="<#=column.Name#>" DataType="<#=column.DataType#>" Length="<#=column.Length#>" />

    <# } #>

    </OutputColumns>

    <ExternalColumns />

    </OutputPath>

    </OutputPaths>

    </CustomComponent>

  • prathmanu

    Old Hand

    Points: 371

    More precisely the error is thrown by

    <InputPaths>

    <InputPath Identifier="Input" OutputPathName="CDCSource.Output" >

    <InputColumns>

    <InputColumn SourceColumn="__$start_lsn" />

    <InputColumn SourceColumn="__$operation" />

    <InputColumn SourceColumn="__$update_mask" />

    <# foreach (var column in table.Columns) { #>

    <InputColumn SourceColumn="<#=column.Name#>" />

    <# } #>

    </InputColumns>

    </InputPath>

    and the error will go off if I remove the line

    <InputColumn SourceColumn="__$operation" />

    Cant figure out which Input or Output column is different.

    Please help

  • phil.guth

    Newbie

    Points: 3

    It seems that this article was re-posted in 2018, but none of the aforementioned issues have been resolved (For example, clarification for using a named instance and the missing <# } #>  )

    Is there a way that community members can update this post, so that it is accurate?

    Thanks,

    Phil

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply