Joel Greijer

This is a sample of how you would create and assign members to three roles for your tabular cube using TSML json script. To execute, open new query connection to your ssas tabular server in management studio, then execute The example in msdn has an extra, invalid bracket set, after "sequence": which fails the script. It has been removed in my working sample below   {   "sequence": {     "operations": [       {         "createOrReplace": {           "object": {             "database": "YourTabularCubeName",             "role": "Admin"           },           "role": {             "name...

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

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

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.

When importing sqlps module implicitly you sometimes get the prompt changed to sqlserver:> in your powershell session. This can be hacked by explicitly loading sqlps in your script. Since this hogs your verbosity log you have the option to disable it with the output stream redirection hack used below. This script uses a defined path which is neccessary if running on teamteamcity where sqlps is not installed. # begin hack because in some cases invoke-sql is bugged, leaving prompt hanging in sqlserver:> try { write-verbose "Importing sqlps" $psmodulepath = join-path $PsScriptRoot MsBuild\Powershell\Modules\SQLPS Push-Location Import-Module -name $psmodulepath -DisableNameChecking 4>$null; #use line below to load where sqlps exists on executing machine: #Import-Module sqlps -DisableNameChecking 4>$null; Pop-Location #reset invalid prompt location } catch { write-error ( "There was an error importing sqlps: {0}" -f $_ .Except...