SSIS Package Errors

Here are some we’ve encountered with resolutions.

Failed to decrypt protected XML node ... "Key not valid for use in specified state."

The creator of the package is someone else than the process owner of the account running it.
If you create the package and run from within SQL the executing account would be the service account.


https://www.mssqltips.com/sqlservertip/2091/securing-your-ssis-packages-using-package-protection-level/

PowerShell: Function Get-LastUpdates shows most recent Windows updates

Function Get-LastUpdates {

    Param(
        [parameter(Position=0, Mandatory = $false)] [Int32] $DaysAgo = 0,
        [parameter(Position=1, Mandatory = $false)] [Int32] $LastNum
    )
    Process {
    
        $Session = New-Object -ComObject "Microsoft.Update.Session"
        $Searcher = $Session.CreateUpdateSearcher()
 
        if ($LastNum) {
            $historyCount = $LastNum
        } else {
            $historyCount = $Searcher.GetTotalHistoryCount()
        }
    
        # Limit the returned rows by $historyCount
        $HistItems = $Searcher.QueryHistory(0, $historyCount) 
 
     #$HistItems | ?{$_.Title -ne $null} |sort Date | Select-Object Date, Title, @{n="desc"; e={$_.Description.substring(0,10)}},  @{name="Operation"; expression={switch($_.operation){ 1 {"Installation"}; 2 {"Uninstallation"}; 3 {"Other"}}}}
     #$HistItems |select -First 1
        if ($DaysAgo -eq 0) {
            $HistItems | ?{$_.Title -ne $null} |sort Date | Select-Object Date, Title
        } else {
            $HistItems | ?{$_.Title -ne $null} | ?{$_.Date -gt (Get-Date).AddDays(-$DaysAgo) } |sort Date | Select-Object Date, Title
    
        }
        # https://itbeco.wordpress.com/microsoft/powershell/
        #Select-Object Date, @{expression={$COMPUTERNAME};Label="Host"}, @{name="Operation"; expression={switch($_.operation){1 {"Installation"}; 2 {"Uninstallation"}; 3 {"Other"}}}}, @{name="Status"; expression={switch($_.resultcode){1 {"In Progress"}; 2 {"Succeeded"}; 3 {"Succeeded With Errors"};4 {"Failed"}; 5 {"Aborted"}}}},@{name="Title";expression={[regex]::Match($_.Title,'(KB[0-9]{6,7})').Value}}
    }
}

And to run from DOS

@ECHO OFF
 
if {%1}=={} (
@ECHO USAGE
@ECHO   GetLastUpdates DaysAgo
@ECHO.
GOTO :FINISHED
)


Powershell -noprofile -command "&{ . E:\MSSQL\PS\adhoc\Get-LastUpdates.ps1; Get-LastUpdates -DaysAgo %1 }

:FINISHED

PowerShell Disk Space report permissions

After getting the following error:

The error information returned by PowerShell is: ‘Access is denied.

  • The account you are running as needs to be added to:
    • Distributed COM Users
    • Performance Monitor Users
    • WMI Control Properties\Security\
      • Click Security button
        • Add MYSRVNAM\Performance Monitor Users
          • Enable Account;
          • Remote Enable

           

This allows me to run the following as the user.

Get-WmiObject Win32_logicaldisk -ComputerName MYSRVNAM |? {$_.DeviceID -eq "E:" -or $_.DeviceID -eq "F:" -or $_.DeviceID -eq "G:" -or $_.DeviceID -eq "H:" }  `
| Format-Table SystemName, DeviceID, MediaType, `
@{Name="Size(GB)";Expression={[decimal]("{0:N0}" -f($_.size/1gb))}}, `
@{Name="Free Space(GB)";Expression={[decimal]("{0:N0}" -f($_.freespace/1gb))}}, `
@{Name="Free (%)";Expression={"{0,6:P0}" -f(($_.freespace/1gb) / ($_.size/1gb))}} `
-AutoSize | Out-String

Thanks to the original source!

EXCEL: Compare Columns Tips

Here are several ways to search for a string in one column in another. The last one worked the best for me, looking for column E in column C.

Just past in the first row of column D1 and copy down.

=NOT(ISNA(MATCH($K1,$G$1:$G$99504,0)))

=COUNTIF($K$1:$K$99504,$G1)>0

=COUNTIF($C1,”*” & $E1 & “*”)>0

Remoting needs to be set up on [ServerName]

PowerShell error:

Remoting needs to be set up on [ServerName]
Try running winrm quickconfig on remote server.

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

DOS;winrm quickconfig
WinRM already is set up to receive requests on this machine.
WinRM is not set up to allow remote access to this machine for management.
The following changes must be made:

Create a WinRM listener on HTTP://* to accept WS-Man requests to any IP on this
machine.
Enable the WinRM firewall exception.

Make these changes [y/n]? y

WinRM has been updated for remote management.

Created a WinRM listener on HTTP://* to accept WS-Man requests to any IP on this
machine.
WinRM firewall exception enabled.

PowerShell: Definitions and Tips

What does it mean? Start here, and for my own reference:

$PSScriptRoot
  # <a href="http://stackoverflow.com/questions/3667238/how-can-i-get-the-file-system-location-of-a-powershell-script">This</a> will tell me the root path of the currently running module or file.
Set-StrictMode -Version "Latest"
  # Use the latest version of PowerShell.

I have a TIPs.ps1 file which has a Break at the top preventing mistakes.

Break
  # Put at the top of a work script to prevent ourselves from running this entire script.

Another Break tip when used in a TRY CATCH.

Break # to just exit the process without throwing an error back to the error stream.

Generally I like to Throw my errors so processing stops.

try 
{
  Get-Content "c:\nonexistingfile.txt" -ErrorAction stop
} 
catch 
{
  throw 'Custom error message' # or $_
}

Search for a number of occurrences of a string within a string.

[regex]$regexDB = '-' 
if ($regexDB.matches($File1).Count -lt 2) { ... }

SQL Metadata load all databases all column data into a table

We had the idea to put all column data into a table for code review comparisons etc. Chris Trump came up with this. Thanks, Chris!

EXECUTE master.sys.sp_MSforeachdb @command1 = N'
USE [?];
 
INSERT INTO dbo.Database_DataDictionary (DATABASE_NAME, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION)
SELECT  
        "?" as DATABASE_NAME,
        t.TABLE_NAME,
        c.COLUMN_NAME,
        c.ORDINAL_POSITION,
        UPPER(c.DATA_TYPE) as DATA_TYPE,
        ISNULL(c.CHARACTER_MAXIMUM_LENGTH,'''') AS CHARACTER_MAXIMUM_LENGTH,
        ISNULL(c.NUMERIC_PRECISION,'''') AS NUMERIC_PRECISION,
        ISNULL(c.NUMERIC_SCALE,'''') AS NUMERIC_SCALE,
        ISNULL(c.DATETIME_PRECISION,'''') AS DATETIME_PRECISION
 
FROM         
        INFORMATION_SCHEMA.COLUMNS AS c 
        INNER JOIN INFORMATION_SCHEMA.TABLES AS t ON 
                t.TABLE_NAME = c.TABLE_NAME
WHERE         
        t.TABLE_TYPE = ''Base Table'''
 

SELECT * FROM dbo.Database_DataDictionary

POWERSHELL: A wrapper for Get-ADGroupMember

I just wanted a wrapper for Get-ADGroup and Get-ADGroupMember to return all members of a group.

function Get-ADGroupMembers {
	<#
		.SYNOPSIS
			For any Get-ADGroup Get-ADGroupMember's Get-ADUser properties and output our PSObject

		.DESCRIPTION
			For any AD group return a list of members including members of subgroups.

		.PARAMETER  Group
			The AD Group name to find members for (without the DOMAIN).
 
		.EXAMPLE
			Get-ADGroupMembers -Group "SQL DBA"

		.EXAMPLE
			$Group = "SQL DBA"
			$Output = Get-ADGroupMembers -Group $Group
			$Output |Sort-Object LastName |Select-Object ObjectClass, GroupName, LastName, GivenName, SamAccountName, Name |ft -AutoSize
			Write-Host ("{0} Members found in AD group: {1}" -f $Output.Count, $Group)
			
		.INPUTS
			System.String 

		.OUTPUTS
			PSObject

		.NOTES
			20170113 Created

 

	#>
	[CmdletBinding()] 
	param(
		[Parameter(Position=0, Mandatory=$true)]
		[ValidateNotNullOrEmpty()]
		[System.String]
		$Group 
	)
	try {
				
		#
		## Get all 
		#  
		
		$Groups = Get-ADGroup -Identity $Group 	 
		$output = ForEach ($g in $groups)  {
			$results = Get-ADGroupMember -Identity $g.name -Recursive | Get-ADUser -Properties displayname, objectclass, name 

			ForEach ($r in $results){
				New-Object PSObject -Property @{
				    GroupName = $g.Name
				    Username = $r.Name
				    ObjectClass = $r.ObjectClass
				    Name = $r.Name
					GivenName = $r.GivenName
					LastName = $r.Surname
					Enabled = $r.Enabled
					SamAccountName = $r.SamAccountName 
				 }
			}
		} 
	  Write-Output $output
	}
	catch {
		throw
	}
}