SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Biml as an SSIS Design Patterns Engine – Level 4


Using Biml as an SSIS Design Patterns Engine – Level 4

Author
Message
Andy Leonard
Andy Leonard
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1150 Visits: 1095
Comments posted to this topic are about the item Using Biml as an SSIS Design Patterns Engine – Level 4

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
ehansen
ehansen
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 32
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
Misha_SQL
Ten Centuries
Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)

Group: General Forum Members
Points: 1000 Visits: 1005
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
Peter Schott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1919
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. Smile )

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
stumap
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 100
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
sergey_yani
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 43
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
juerg.maier
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 41
With a named instance use a double slash only in the "CreateConnectionNode()" function and not in the <Connections> section.
Peter Schott
Peter Schott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1919
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
g.britton
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1539 Visits: 1763
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, MVP
Toronto PASS Chapter
TheComedian
TheComedian
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 510
Why wasn't the mistake corrected in the code once pointed out?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search