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

There are some hoops you need to jump through to get a multi-statement transaction committed via powershell. Default time-out is set to only one minute, so using the code below you can increase it. $timeout = New-Object System.TimeSpan -ArgumentList @ (5,0,0) # five hours $options = [System.Transactions.TransactionScopeOption] :: Required $scope = New-Object -TypeName System.Transactions.TransactionScope -ArgumentList @ ( $options , $timeout ) try { #Multiple queries, loops or whatever fun here, inside transaction Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName1 ; Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName2 ; Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName3 ; $scope .Complete() #we tell transactionscope to commit when using e...

Problem 1 I tried adding a powershell script to a sql server agent job step, but there are some severe limitations where write-host or write-verbose outputs are not allowed instead of ignored, which should make sens. Then the job crashes with the following error:  A job step received an error at line [xx] in a PowerShell script. The corresponding line is 'write-host "test";  '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows.  '.  Process Exit Code -1.  The step failed. Problem 2 So instead I created a Operating System (CmdExec) step with calling pow...

When trying to access Microsoft Power BI you get redirected to https://app.powerbi.com/SignupRedirect and from there you get a message that you already have a license and can click ok. This loop goes on forever My solution: Ask your Office.com portal admin to remove the Power Bi license, then try again. The license was free in my case, so it was automatically assigned on next logon attempt.

Consider the following script: array as parameter.ps1 #requires -version 2.0 [CmdletBinding()] param ( [parameter(Mandatory=$false)] [string[]] $data ) write-host "items in data array:" $data.count write-host "first item in data array:" $data[0] When calling from command line cmd.exe using the following command powershell -file ".\array as parameter.ps1" -data "data1","data2" outputs items in data array: 1 first item in data array: data1,data2 But when calling using the folloing command powershell -command "&{.\'array as parameter.ps1' -data 'data1','data2'}" outputs items in data array: 2 first item in data array: data1

create   table   dbo . PlanGuideTest (   Id   int   identity ( 1 , 1 )   primary   key ,    Data   varchar ( 100 )   not   null ) insert   dbo . PlanGuideTest   ( data )   values   ( 'a' ) , ( 'b' ) , ( 'c' ) , ( 'd' ) /* run this a couple of times and verify in execution plan that  QueryPlan DegreeOfParallelism="" is greater than 1 (this will not be the case if your server is not set up for paralellism)*/ exec   sp_executesql   N'select * from PlanGuideTest where Id between @p0 and @p1' , N'@p0 int,@p1 int' , 2 , 4 /* check which plan is used, remember that your original query might be altered before stored in cache, so check usecounts column. */ select   st . te...

Will probably not work on heap (non clustered) tables select   object_name ( s . object_id )   as   TableName ,           i . name                     as   IndexName ,           last_user_update   --,s.*   from     sys . dm_db_index_usage_stats   s           join   sys . indexes   i             on   i . index_id   =   s . index_id                and   i . object_id   =   s . object_id   where    object_name ( s . object_id )   =   'myTable'  

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