Copy Newly created files from one SharePoint tenant to another using Flow

We have an on-premise Sharepoint 2013 tenant and we had a need that when a document was created in a library that it needed to be copied to a library in O365.

In Summary here is what you need to do:

1. Create connections to your O365 tenant and your on-premise tenant (Data Gateway typically required).
2. Create a new flow from blank
3. Add the following SharePoint flow steps in order
1. "When a file is created in a folder"
2. "Get file content"
3. "Create file"
4. Configure steps with your values similar to screenshot below

Inspiration:
https://www.chakkaradeep.com/2018/04/20/copy-or-move-files-from-to-sharepoint-using-microsoft-flow/

T-SQL Function to remove HTML tags

I needed to clean out some html tags from a column.  I could not find a function already out there so I crafted one from my google-fu findings.

So here it is:

IF OBJECT_ID (N'dbo.StripHTML', N'FN') IS NOT NULL 
    DROP FUNCTION StripHTML; 
GO 
CREATE FUNCTION dbo.StripHTML(@TextToClean nvarchar(max)) 
RETURNS NVARCHAR(max)
AS
BEGIN
--This function cleans out html tags and some special html characters
--20190129
--author: lyle.spencer@gmail.com
--original code: https://www.sqlservercentral.com/Forums/932589/Extracting-text-from-html-stored-in-SQL-table

-- To get the list of Tags removed....
Declare @TextToCleanTagNameTable Table(Tag nvarchar(max))
Declare @TextToCleanCharI1 int  -- Used to locate '<'
Declare @TextToCleanCharI2 int  -- Used to locate '>' to get Tag Name
Declare @TextToCleanCharICheck int  -- Used to Check for Tag Name
Declare @TextToCleanEndTagName nvarchar(1000)

Declare @TextToCleanStartPos int
Set @TextToCleanStartPos = 0

While 1=1
begin
   Set @TextToCleanCharI1 = CharIndex('<',@TextToClean,@TextToCleanStartPos) 
   if @TextToCleanCharI1 > 0
   begin
  -- Get the Tag Name, if it is a HTML Tag
  Set @TextToCleanCharI2 = CharIndex('>',@TextToClean,@TextToCleanCharI1 + 1)
  if @TextToCleanCharI2 > 0
  begin
-- Check <>
if @TextToCleanCharI2 = @TextToCleanCharI1 + 1
begin
Set @TextToCleanStartPos = @TextToCleanCharI2 + 1
end
else
begin
Set @TextToCleanCharICheck = CharIndex('<',@TextToClean,@TextToCleanCharI1 + 1)
if (@TextToCleanCharICheck < @TextToCleanCharI2)
begin           
   -- Last < found was not start of tag. Just < character / sign
   if @TextToCleanCharICheck > 0
  Set @TextToCleanStartPos = @TextToCleanCharICheck
   else
   begin
  -- @TextToCleanCharICheck = 0
  -- Its a Last TAG...
  Set @TextToCleanEndTagName = SubString(@TextToClean,@TextToCleanCharI1,(@TextToCleanCharI2-@TextToCleanCharI1) + 1)
  if Not Exists(Select 1 from @TextToCleanTagNameTable where Tag = @TextToCleanEndTagName)
Set @TextToClean = Replace(@TextToClean,@TextToCleanEndTagName,'')
Set @TextToCleanStartPos = @TextToCleanCharI2
   end

   if @TextToCleanStartPos = Len(@TextToClean)
   begin           
  --Select @TextToCleanStartPos , Len(@TextToClean),@TextToCleanCharICheck,@TextToCleanCharI1
  --print 'Over Here....'
  BREAK
   end
end
else
begin
   -- ITS A TAG....
   Set @TextToCleanEndTagName = SubString(@TextToClean,@TextToCleanCharI1,(@TextToCleanCharI2-@TextToCleanCharI1) + 1)             
   if Not Exists(Select 1 from @TextToCleanTagNameTable where Tag = @TextToCleanEndTagName)
   begin
  Set @TextToClean = Replace(@TextToClean,@TextToCleanEndTagName,'')
   end

   --Set @TextToCleanStartPos = @TextToCleanCharI2 + 1           
   Set @TextToCleanStartPos = @TextToCleanCharI1
   if @TextToCleanStartPos > Len(@TextToClean)
   begin
  --print 'Here....'
  BREAK
   end
end
end
  end
  else
BREAK  -- No Tag
   end 
   else
  BREAK
end
RETURN @TextToClean
END;
GO

--test me
Select dbo.stripHTML('<test1> test2')

Sharepoint: Copy Unique User Permissions of a user with Powershell

This script will copy on-premise unique permissions on an object of a user to another user or group.  It still needs some work but in general works.  If someone wanted to improve on this work it would be nice if it could copy group permissions.

Much of this was aggregated from around the web, in particular:
http://eth0real.net/articles/grant-permissions-to-sharepoint-list-items-using-powershell

Here it is:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Function to retrieve Permission data
Function Copy-ObjectPermissionsOfUser([String]$FromUserID, [String]$ToUserID, [Microsoft.SharePoint.SPSecurableObject]$Object, [String]$RunMode)
{
    #Set $RunMode to "Commit" for actual permission copy
    #Object Array to hold Permission data
    $PermissionDataCollection = @()

    #Determine the given Object type and Get URL of it
    switch($Object.GetType().FullName)
    {
        "Microsoft.SharePoint.SPWeb"  { $ObjectType = "Site" ; $ObjectURL = $Object.URL }
        "Microsoft.SharePoint.SPListItem"
        {
            if($Object.Folder -ne $null)
            {
                 $ObjectType = "Folder" ; $ObjectURL = "$($Object.Web.Url)/$($Object.Url)"
            }
            else
            {
                $ObjectType = "List Item"; $ObjectURL = "$($Object.Web.Url)/$($Object.Url)"
            }
        }
        #Microsoft.SharePoint.SPList, Microsoft.SharePoint.SPDocumentLibrary, "Microsoft.SharePoint.SPPictureLibrary",etc
        default { $ObjectType = "List/Library"; $ObjectURL = "$($Object.ParentWeb.Url)/$($Object.RootFolder.URL)" }
    }
 
    #Get Permissions of the user on given object - Such as: Web, List, Folder, ListItem
    $UserPermissionInfo = $Object.GetUserEffectivePermissionInfo($FromUserID)
    #Iterate through each permission and get the details
    foreach($UserRoleAssignment in $UserPermissionInfo.RoleAssignments)
    {
        #Get all permission levels assigned to User account directly or via SharePOint Group
        $UserPermissions=@()
        foreach ($UserRoleDefinition in $UserRoleAssignment.RoleDefinitionBindings)
        {
            #Exclude "Limited Accesses"
            if( $UserRoleDefinition.Name -ne "Limited Access")
            {
                $UserPermissions += $UserRoleDefinition.Name
                #now lets copy permission if not group assigned
Write-host "Group: " $UserRoleAssignment.Member.Name
                if($UserRoleAssignment.Member -is [Microsoft.SharePoint.SPGroup] )#SP Groups
                {
                     $PermissionType = "Member of SharePoint Group: " + $UserRoleAssignment.Member.Name
                     write-host -ForegroundColor Green "Permission " $UserPermissions " On " $ObjectURL " from SP group: " $UserRoleAssignment.Member.Name
                }
                elseif($UserRoleAssignment.IsDomainGroup -or $UserRoleAssignment.Member.Name -like "GONZAGA*" ) #check if domain group
                {
                    $PermissionType = "Member of AD Group: " + $UserRoleAssignment.Member.Name
                    write-host -ForegroundColor Yellow "Permission " $UserPermissions " On " $ObjectURL " from AD group: " $UserRoleAssignment.Member.Name
                }
                else #copy the permission if not group assigned
                {
                    $PermissionType = "Direct Permission"
                    $GroupOrUserName = $web.EnsureUser($ToUserID)
                    #$role = $web.RoleDefinitions[$permissionLevel]
                    $assignment = New-Object Microsoft.SharePoint.SPRoleAssignment($GroupOrUserName)
                if($RunMode -EQ "Commit")#Only copy if commit mode
                    {
                        $assignment.RoleDefinitionBindings.Add($UserRoleDefinition)
                        $item.RoleAssignments.Add($assignment)
                    $item.Update()
                    }
                    write-host -ForegroundColor Black -BackgroundColor Yellow "Copied Permission " $UserPermissions " to Object " $ObjectURL
                }
                $UserPermissions = $UserPermissions -join ";"   
 
                #Create an object to hold storage data
                $PermissionData = New-Object PSObject
                $PermissionData | Add-Member -type NoteProperty -name "Object" -value $ObjectType
                $PermissionData | Add-Member -type NoteProperty -name "Title" -value $Object.ID
                $PermissionData | Add-Member -type NoteProperty -name "URL" -value $ObjectURL     
                $PermissionData | Add-Member -type NoteProperty -name "Permission Type" -value $PermissionType
                $PermissionData | Add-Member -type NoteProperty -name "Permissions" -value $UserPermissions
                $PermissionData | Add-Member -type NoteProperty -name "FromGroupOrUser" -value $UserRoleAssignment.Member.Name
                $PermissionData | Add-Member -type NoteProperty -name "ToGroupOrUser" -value $ToUserID
                $PermissionDataCollection += $PermissionData
Write-host "Is Domain Group: " $UserRoleAssignment.IsDomainGroup
Write-host "SP Group:        " $UserRoleAssignment.Member.Name
                Write-host "-----------------------------------"
            }
        }

    } 
    Return $PermissionDataCollection
}

#Function to Generate Permission Report
Function do-SearchAndCopyUniquePermissions($FromUserid, $ToUserid, $WebAppURL, $ReportPath)
{
    #Output Report location, delete the file, If already exist!
    if (Test-Path $ReportPath)
     {
        Remove-Item $ReportPath
     }
   
      #Write Output Report CSV File Headers
     "Object, Title, URL, Permission Type, Permissions" | out-file $ReportPath

    ###Check Whether the Search Users is a Farm Administrator ###
    Write-host "Scanning Farm Administrators..."
      #Get the SharePoint Central Administration site
      $AdminWebApp = Get-SPwebapplication -includecentraladministration | where {$_.IsAdministrationWebApplication}
    $AdminSite = Get-SPWeb $AdminWebApp.Url
    $AdminGroupName = $AdminSite.AssociatedOwnerGroup
    $FarmAdminGroup = $AdminSite.SiteGroups[$AdminGroupName]

    #enumerate in farm adminidtrators groups
    foreach ($user in $FarmAdminGroup.users)
    {
        if($user.LoginName.Endswith($FromUserid,1)) #1 to Ignore Case
        {
                "Farm, $($AdminSite.Title), $($AdminWebApp.URL), Farm Administrators Group, Farm Administrator" | Out-File $ReportPath -Append
        }   
    }

    ### Check Web Application User Policies ###
    Write-host "Scanning Web Application Policies..."
     $WebApp = Get-SPWebApplication $WebAppURL

        foreach ($Policy in $WebApp.Policies)
        {
            #Check if the search users is member of the group
              if($Policy.UserName.EndsWith($FromUserid,1))
             {
                #Write-Host $Policy.UserName
                 $PolicyRoles=@()
                foreach($Role in $Policy.PolicyRoleBindings)
                {
                    $PolicyRoles+= $Role.Name +";"
                }
            #Send Data to CSV File
            "Web Application, $($WebApp.Name), $($WebApp.URL), Web Application Policy, $($PolicyRoles)" | Out-File $ReportPath -Append
            }
        }

    #Convert UserID Into Claims format - If WebApp is claims based! Domain\User to i:0#.w|Domain\User
    if($WebApp.UseClaimsAuthentication)
    {
        $ClaimsUserID = (New-SPClaimsPrincipal -identity $FromUserid -identitytype 1).ToEncodedString()
    }
 
    #Get all Site collections of given web app
    $SiteCollections = Get-SPSite -WebApplication $WebAppURL -Limit All

    #Loop through all site collections
    foreach($Site in $SiteCollections)
    {
        Write-host "Scanning Site Collection:" $site.Url
        ###Check Whether the User is a Site Collection Administrator
           foreach($SiteCollAdmin in $Site.RootWeb.SiteAdministrators)
        {
            if($SiteCollAdmin.LoginName.EndsWith($ClaimsUserID,1))
               {
                "Site Collection, $($Site.RootWeb.Title), $($Site.RootWeb.Url), Site Collection Administrators Group, Site Collection Administrator" | Out-File $ReportPath -Append
               }   
          }
     
        #Get all webs
        $WebsCollection = $Site.AllWebs
        #Loop throuh each Site (web)
        foreach($Web in $WebsCollection)
        {
             IF($web.url -like 'http://bi-sp2013/bi*')
             {
                    if($Web.HasUniqueRoleAssignments -eq $True)
                    {
                        Write-host "Scanning Site:" $Web.Url
             
                        #Get Permissions of the user on Web
                        $WebPermissions = Copy-ObjectPermissionsOfUser $ClaimsUserID $ToUserID $Web
                 
                        #Export Web permission data to CSV file - Append
                        $WebPermissions |  Export-csv $ReportPath  -notypeinformation -Append
                    } 
             
                    #Check Lists with Unique Permissions
                    Write-host "Scanning Lists on $($web.url)..."
                    foreach($List in $web.Lists)
                    {
                        if($List.HasUniqueRoleAssignments -eq $True -and ($List.Hidden -eq $false))
                        {
                            #Get Permissions of the user on list
                            $ListPermissions = Copy-ObjectPermissionsOfUser $ClaimsUserID $ToUserID $List $RunMode
                     
                            #Export Web permission data to CSV file - Append
                            $ListPermissions |  Export-csv $ReportPath -notypeinformation -Append                       
                        }
             
                        #Check Folders with Unique Permissions
                        $UniqueFolders = $List.Folders | where { $_.HasUniqueRoleAssignments -eq $True }                 
                        #Get Folder permissions
                        foreach($folder in $UniqueFolders)
                        {
                            $FolderPermissions = Copy-ObjectPermissionsOfUser $ClaimsUserID $ToUserID $folder $RunMode
             
                            #Export Folder permission data to CSV file - Append
                            $FolderPermissions |  Export-csv $ReportPath -notypeinformation -Append             
                        }
             
                        #Check List Items with Unique Permissions
                        $UniqueItems = $List.Items | where { $_.HasUniqueRoleAssignments -eq $True }
                        #Get Item level permissions
                        foreach($item in $UniqueItems)
                        {
                            $ItemPermissions = Copy-ObjectPermissionsOfUser $ClaimsUserID $ToUserID $Item $RunMode
                            #Export List Items permission data to CSV file - Append
                            $ItemPermissions |  Export-csv $ReportPath -notypeinformation -Append           
                        }
                    }
            }
        }
    }
    if($RunMode -EQ "Commit")#Only copy if commit mode
    {
        Write-Host "Permissions Copied, see log file for details"
    }ELSE
    {
        Write-Host -foregroundcolor Red "Audit Report Generated, to copy permissions set runemode to Commit."
    }
}


#Input Variables
$RunMode = "aCommit" #Set to "Commit" for actual permission copy, "Audit" for report only
$WebAppURL = "https://bi-sp2013.gonzaga.edu/"
$FromUserid ="GONZAGA\spencerl" #must be a user as these are unique permissions only
$ToUserid ="GONZAGA\SBA-Grad-School-Role"  #Can be user or group
$ReportPath = "C:\Temp\SBA-Ws-GA-Role-PermissionRpt.csv"

#Call the function to generate user access report
do-SearchAndCopyUniquePermissions $FromUserid $ToUserid $WebAppURL $ReportPath $RunMode

SSAS Error: The 'xx' dimension was not found in the 'xx' database on the '' server.

I ran into this this error seemingly randomly.  I was able to find a solution in a cached google page  MSDN page.

If you encounter this the solution is simple:

1.  Build->Build Solution
2.  Build->Deploy Solution (or just process what you want)
3.  Profit!

Original solution (not reachable for me):
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/268d5c92-5e35-446c-84a6-a1bbe67ea23c/the-xxx-dimension-was-not-found-in-the-xxx-database-on-the-server?forum=sqlanalysisservices

Adding Custom SSRS Export Formats in Sharepoint Integrated Mode

What I needed: To add tab delimited and no header csv output to Reporting Services integrated mode in Sharepoint 2013.

What I did: Obligatory google-fu, synthesized what others had done.

Solution:
Powershell script as follows:

#enable sharepoint powershell

$ver = $host | select version

if($Ver.version.major -gt 1) {$Host.Runspace.ThreadOptions = "ReuseThread"}

if(!(Get-PSSnapin Microsoft.SharePoint.PowerShell -ea 0))

{

Add-PSSnapin Microsoft.SharePoint.PowerShell

}

##

#Set Script Variables

##

#Remeber: Must run shell as administrator, I used my farm account.

#Get App GUID

$AppGUID = Get-SPRSServiceApplication

#delete tab extension

Remove-SPRSExtension -identity $AppGUID -name "TAB" -ExtensionType "Render"

#Add Tab delimited output to SSRS

New-SPRSExtension -identity $AppGUID -ExtensionType "Render" -name "TAB" -TypeName "Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering" -ServerDirectives "<OverrideNames><Name Language='en-US'>TAB Delimited</Name></OverrideNames>" -ExtensionConfiguration "<DeviceInfo><Encoding>ASCII</Encoding><FieldDelimiter xml:space='preserve'>`t</FieldDelimiter><UseFormattedValues>False</UseFormattedValues><NoHeader>True</NoHeader><FileExtension>txt</FileExtension></DeviceInfo>"

#delete extension

#Remove-SPRSExtension -identity $AppGUID -name "CSV (No Header)" -ExtensionType "Render"

#Add CSV no header

New-SPRSExtension -identity $AppGUID -ExtensionType "Render" -name "CSV (No Header)" -TypeName "Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering" -ServerDirectives "<OverrideNames><Name Language='en-us'> CSV No Header</Name></OverrideNames>" -ExtensionConfiguration "<DeviceInfo><Encoding>ASCII</Encoding><UseFormattedValues>False</UseFormattedValues><NoHeader>True</NoHeader><FileExtension>csv</FileExtension></DeviceInfo>"

#Lets see our new report extension

Get-SPRSExtension -Identity $AppGUID -ExtensionType Render

Sources:
https://blog.westmonroepartners.com/configuring-custom-render-formats-sql-server-reporting-services-sharepoint-integrated-mode/

http://charanmandya.blogspot.com/2014/06/export-csv-without-header-in-ssrs.html