Joel Greijer

Yes, you can create circular precedence constraint chains in SSIS packages. At least if you automate! Using BIML (which has no check for stupidity). It does what it's told. So investigate your generated packages carefully. Probably your mysteriously failing master package arrived at this if you have the same symptoms as I had When running from Sql Server Agent : Unexpected Termination (No messages) When running in Visual Studio : Starting / Cancelled in output window. Hanging of Visual Studio Windows log  Faulting application name: devenv.exe, version: 16.2.29306.81, time stamp: 0x5d72c031 Faulting module name: DTS.dll, version: 2019.150.1301.433, time stamp: 0x5d1ee90e Exception code: 0xc00000fd 0xc00000fd  seem to indicate buffer overflow (infinite loop clue!) Update: This is reported by ClemJax in  https://github.com/varigence/BimlPit/issues/98

var metadataConnection = SchemaManager.CreateConnectionNode( "db_metadata" , RootNode.Connections[ "db" ].RenderedConnectionString); List< string > includedSchemas = new List< string >{ "schema1" }; List< string > includedTables = null ; var metadataSchema = metadataConnection.GetDatabaseSchema( includedSchemas, includedTables, ImportOptions.None); foreach ( var t in metadataSchema.TableNodes) { //perform biml magic }

IgnoreFailure RedirectRowsToError OutputFailComponent RedirectRowsToNoMatchOutput LookupNoMatchBehavior NoMatchBehavior

When troubleshooting slow biml generation times on a fairly large solution of 100+ ssis packages I found out that one of the culprits are CallBimlScript() function. I created a test showing the overhead this function generates. By examining the packages CreationDates i summarize the total time. SpeedTest.biml < #@ template language = "C#" tier = "1" #> < Biml xmlns = "http://schemas.varigence.com/biml.xsd" > < Annotations > < Annotation AnnotationType = "CodeComment" > < #=DateTime.Now.ToString("s")# > Biml start time </ Annotation > </ Annotations > < Packages > < # for ( int i = 0;i < 1000 ; i ++) { #> < Package Name = "Package <#=i#>" CreationDate = "<#=DateTime.Now.ToString(" s ")#> "> < Annotations > < #=CallBimlScript("TestFunction.biml") #...

Large biml solutions might be slow. These steps are good to take to be able to pinpoint slow spots. Create Debug.biml  Add debug.biml last to your project. As there is no way to get the name of the current file in BIML we use a hack that requires at least one other biml-file containing at least one connection tag to be generated.  < #@ template language = "C#" tier = "900" #> < #@ import namespace = "System.Diagnostics" #> < #@ import namespace = "System.Xml" #> < Biml xmlns = "http://schemas.varigence.com/biml.xsd" > < Annotations > < Annotation AnnotationType = "CodeComment" > < #=DateTime.Now.ToString("s")# > Debug start time </ Annotation > </ Annotations > < # string xBiml = RootNode.GetBiml(); XmlDocument xmlDoc = new XmlDocument (); xmlDoc.LoadXml ( xBiml ); if ( RootNode.Connect...

When trying to use parameterized biml file using CallBimlScript like this <#=CallBimlScript("VariableTest.biml", "variableValue") #> And VariableTest.Biml looks like this <#@ property name="variableValue" type="String" required="true" #> <#= variableValue #> Biml generation will fail with the following error Expanding BIML Error 0 Template host did not supply the required property: variableValue . C:\path\VariableTest.biml 0 0 Parse. There were errors during compilation. See compiler output for more information. The error is because of bugs inside biml engine. To work around this problem, Set required="false" like so <#@ property name="variableValue" type="String" required="false" #> <#= variableValue #> The parameter passing will now work. If you need parameter validation, you have to implement it yourself.

Possible cause for the cryptic error The package failed to load due to error  0x80070057 "The p arameter is incorrect.". This occurs when CPackage::LoadFrommXML fails.  ...when opening or executing and SSIS package In my case i created the ssis package using biml. And accidentally created a duplicate precedence constraint to another task twice. Removing the duplicate constraint and re-generating the package solved it.

Why is does not my live Mist Biml script generate packages, even though i can see the expanded packages in the Preview Expanded Biml script? In my case, the loop creating the packages was based on tables generated from another live script. This "double-hop" live scripting is supported. The key to success is using tiers <#@ template language="C#" tier="1" #> <#@ template language="C#" tier="2" #> <#@ template language="C#" tier="3" #>

Error:: BimlScript expected X properties, but Y were supplied Provide required properties The required attribute on parameter object does not seem to work when using CallBimlScript example:  <#@ property name="parameterValue" type="System.String" required="False" #> Workaround: Specify empty/null parameter in CallBimlScript then use string.IsNullOrEmpty(parameterValue) in your callee script. Error When building biml script i get " Error:Node: Varigence.Biml.SsisEmitter.Model.Dataflow.Input:   Expression  contains syntax errors"  No error lines or task names are shown. Cause  / Solution This message actually means that one of your tasks have an empty ssis expression (Note the double space between "Expression  contains". Examples could be columns inside a . Check recent added tasks for missing expressions. Error Cannot build biml file in mist that has an ssis expression containing double ampersands "...