Using Biml as an SSIS Design Patterns Engine – Level 4

Question

Using Biml as an SSIS Design Patterns Engine – Level 4

Andy Leonard

SSChampion

Points: 10144
More actions
August 13, 2013 at 10:00 pm

#281165

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

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

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

ehansen SSC Enthusiast Points: 143 More actions · Answer 1

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: 5407 More actions · Answer 2

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: 9675 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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

Peter Schott SSCrazy Eights Points: 9675 More actions · Answer 7

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: 13863 More actions · Answer 8

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, Pluralsight courses

TheComedian SSCrazy Points: 2932 More actions · Answer 9

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

prathmanu Old Hand Points: 371 More actions · Answer 10

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

prathmanu Old Hand Points: 371 More actions · Answer 11

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"

UsesDispositions="true"

Version="2"

ValidateExternalMetadata="false">

<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>

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

<# } #>

</InputColumns>

</InputPath>

</InputPaths>

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

</Annotations>

<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>

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

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

<# } #>

</OutputColumns>

</OutputPath>

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

</Annotations>

<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>

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

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

<# } #>

</OutputColumns>

</OutputPath>

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

</Annotations>

<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>

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

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

<# } #>

</OutputColumns>

</OutputPath>

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

</Annotations>

<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>

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

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

<# } #>

</OutputColumns>

</OutputPath>

</OutputPaths>

</CustomComponent>

prathmanu Old Hand Points: 371 More actions · Answer 12

More precisely the error is thrown by

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

<# } #>

</InputColumns>

</InputPath>

and the error will go off if I remove the line

Cant figure out which Input or Output column is different.

Please help

phil.guth Newbie Points: 3 More actions · Answer 13

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