Troubleshooting slow BIML solutions

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.Connections.Count == 0 ) throw new Exception("Debug.biml cannot be executed on its own. Shift+mark all biml-files and choose Generate Ssis packages. At least one of these biml files need at least one connection tag"); string bimlFilePath = System.IO.Directory.GetParent(RootNode.Connections[0].BimlFile.FilePath).FullName; string sFile = String.Format(@"{0}\{1}",bimlFilePath, @"debug.CompiledBiml.xml"); //throw new Exception(sFile); xmlDoc.Save(sFile); #> </Biml>

When generating packages from your biml-files, include debug.biml and a file called debug.CompiledBiml.xml is created. Add it to your miscellaneous files in your project for easy access.

Create log timer entries

<Annotations> <Annotation AnnotationType="CodeComment"><#=DateTime.Now.ToString("s")#> MyFile start time</Annotation> </Annotations>

Examine debug.CompiledBiml.xml

This will output timer rows as comments in your debug.CompiledBiml.xml in like fashion:

<Biml> <Annotations> <Annotation AnnotationType="CodeComment">2016-12-14T13:20:52 FirstFile start time</Annotation> <Annotation AnnotationType="CodeComment">2016-12-14T13:20:52 SecondFile start time</Annotation> <Annotation AnnotationType="CodeComment">2016-12-14T13:25:54 AnotherFile start time</Annotation> <Annotation AnnotationType="CodeComment">2016-12-14T13:27:22 Debug start time</Annotation> </Annotations> </Biml>

 Thanks to Cathrine Wilhelmsen for leading me into this
https://www.cathrinewilhelmsen.net/biml/

Findings

CallBimlScript() function is insanely slow in VS2013+Bids Helper

Comments

Post a Comment

Popular posts from this blog

SSIS: Set parent package variable from child package

SSIS: Set parent package variable from child package For this sample we communicate using bool value named ReturnValue First create a variable in parent package: ReturnValue as Boolean In child package, you do not need to set package configuration for the parent variable, nor should the ReturnValue variable be created. Create script task in child package. Leave the fields ReadOnlyVariables, ReadWriteVariables empty. public void Main() { Variables vars = null ; Dts.VariableDispenser.GetVariables( ref vars); var dummy = false ; if (Dts.VariableDispenser.Contains( "User::ReturnValue" )) { Dts.Events.FireInformation(0, "" , "Setting (parent package variable) User::ReturnValue" , null , 0, ref dummy); Dts.VariableDispenser.LockForWrite( "User::ReturnValue" ); Dts.VariableDispenser.GetVariables( ref vars); vars[ "User::ReturnValue" ].Value = true ; ...
Read more

How to decrypt stored password from SSMS registered servers

Image
Right click your server, choose tasks/export Uncheck "Do not include user names and passwords in the export file" Save the export to a file Open the file in an editor and locate the tag RegisteredServers:ConnectionStringWithEncryptedPassword Copy the contents of password attribute Open powershell and paste the code below, after you amend it with your encrypted string. $base64pass = "PasteEncryptedPasswordHere" [System.Reflection.Assembly]::LoadWithPartialName("System.Security") | Out-Null [System.Text.Encoding]::Unicode.GetString([System.Security.Cryptography.ProtectedData]::Unprotect([System.Convert]::FromBase64String($base64pass), $null, [System.Security.Cryptography.DataProtectionScope]::CurrentUser))
Read more

Insert bulk statement does not support recompile (SQL 2017)

This happened to me once, source and target databases was located on the same server running 14.0.1000.169 Microsoft SQL Server Enterprise (64-bit) Consecutive executions never fail. I could not reproduce the error until next day. My workaround has been to simply add "re-try execution" a number of times in SQL Agent job. SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005  Description: "Insert bulk statement does not support recompile.".  End Error
Read more