Joel Greijer

This is how to grant user from system assigned identity (e.g. automation account) to allow to connect and query azure sql database without any use of keys, secrets, vaults or hard coded passwords. The app does not have to be contributor in azure sql instance for this to work Enable System Assigned Identity Navigate to Automation Account / Identity Set System Assigned: On Copy Object (Principal) Id guid Navigate to Azure Active Directory Search for the guid in "Search your tenant" box Find the "enterprise application" popping up Copy the Name this is what you use below [App_Name] Grant access to Azure SQL Database CREATE USER [App_Name] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo ALTER ROLE db_datareader ADD MEMBER [App_Name] ALTER ROLE db_datawriter ADD MEMBER [App_Name] How to use from runbook (powershell) $server  =  "your-sql-server-name.database.windows.net" $database  =  "your-database-name" $query  =  "sel...

It's very tricky to find the schema of the crucial .configsettings file that is used when automating deployments of ssas projects.  It has come to my understanding that msbuild does not produce this file for Tabular Model projects. Though in some cases Microsoft forget to mention this in their guides.  I know clarification is pending on this page https://docs.microsoft.com/en-us/analysis-services/deployment/deployment-script-files-solution-deployment-config-settings?view=asallproducts-allversions Meanwhile. Here is a sample of the file in case you need it <ConfigurationSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http:...

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

Setting up SSRS security is a two step process. Site security, then Folder security. This can be handled using the following scripts. Sources from  https://randypaulo.wordpress.com/2012/02/22/powershell-set-user-permission-in-ssrs-item-sql-reporting-server-using-powershell/ https://www.powershellgallery.com/packages/ReportingServicesTools/0.0.0.2/Content/Functions%5CSecurity%5CGrant-AccessToRS.ps1 Add-SsrsItemSecurity.ps1 <# .SYNOPSIS Set user permissions in SQL Reporting Services using Web Service .DESCRIPTION Set user permissions in SQL Reporting Services using Web Service .EXAMPLE Add-SsrsItemSecurity -url "http://[ServerName]/ReportServer/ReportService2010.asmx?WSDL" -itemPath "MyReportFolder" -u domain\user -r "Content Manager" -inherit $true #> param ( [ Parameter ( Mandatory = $true )][ Alias ( "url" )] [string] $webServiceUrl , [ Parameter ( Mandatory = $true )][ Alias ( "path" )] [stri...

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

/*   Awaits job to finish, then returns job_status   0 = Failed   1 = Succeeded   2 = Retry   3 = Canceled   null = Job never executed / no history   If job was executed and already finished successfully before this proc starts, it will return 1 (success) */   create   procedure   tools . AwaitSqlServerAgentJob      @jobName   sysname   as      declare   @jobid   uniqueidentifier   =   ( select   job_id   from   msdb . dbo . sysjobs   where   name   =   @jobname   )    declare   @sessionId   int   =   ( select   max ( session_id )   from   msdb . dbo . sysjobactivity ...

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