The post you're referencing is extremely long.  Can you just rephrase the question?

For the benefit of anyone joining in.

Problem:

Set-ADUser uses a different set of parameters from New-ADUser. The attribute updates need to be handled a little differently.

Summary:

The existing script performs the following actions:
    - Executes a query against SQL to generate a list of updates which should be published into Active Directory
    - Executes the New-ADUser command if a user should be created with a set of dynamically filled parameters
    - Clears the flag from the entry in the SQL database.

Code breakdown:

SQL reader

Import-Module ActiveDirectory


# Path to create users in
$path = "OU=TestUser,OU=Test,DC=Test,DC=com"

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Select statement to return new user accounts
# Needs to return "sAMAccountName" & "Password" columns
# Note: Other columns names should match AD attribute name
$sql = "Select FirstName as GivenName,
				 LastName as sn,
				 DisplayName as DisplayName,
				 samAccountName as sAMAccountName,
				 EmailAddress as mail,
				 City as l,
				 Department as Department,
				 StreetAddress as StreetAddress,
				 State as st,
				 samAccountName+'@wuestundpartner.com' as userPrincipalName,
				 PostalCode as postalcode,
				 MobilePhone as mobile,
				 OfficePhone as telephoneNumber,
				 Department as department,
				 Title as Title,
				 Office as physicalDeliveryOfficeName,
				 c as c,
				 CountryCode as co,
				 Action as Action,					
				 'Abc-123456' as Password
from GetActiveDirectoryUsers where Action = 'update' OR Action = 'create' "

###########################

$cn = new-object system.data.sqlclient.sqlconnection
$cn.ConnectionString = $connectionString
$cn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = $sql
$cmd.connection = $cn
$dr = $cmd.ExecuteReader()

Select allOpen in new window

Parameter population:

$colCount = $dr.FieldCount
$sAMAccountNameOrdinal = $dr.GetOrdinal("sAMAccountName")
$PasswordOrdinal = $dr.GetOrdinal("Password")
$DisplayNameOrdinal = $dr.GetOrdinal("DisplayName")
$ActionOrdinal = $dr.GetOrdinal("Action")

while ($dr.Read())
{
	# Get value of sAMAccountName column
	$sAMAccountName = $dr.GetValue($sAMAccountNameOrdinal)
	# Get value password column (converted to secure string for New-ADUser Cmdlet)
	$password = ConvertTo-SecureString -AsPlainText $dr.GetValue($PasswordOrdinal) -Force
	# Get value of DisplayName column
	$DisplayName = $dr.GetValue($DisplayNameOrdinal)
	$Action = $dr.GetValue($ActionOrdinal)
	
	#write-host "Creating user account..." $sAMAccountName
	
	$otherAttributes = New-Object System.Collections.HashTable
	
	# Create a hash table of attribute names and attribute values
	# Used to populate other attributes. 
	for ($i = 0; $i -le $colCount - 1; $i++) {
        if ($attribute -notin 'Password', 'SAMAccountName', 'Action') {
            $value = $dr.GetValue($i)
            if ($value -is [String]) {
                $value = $value.Trim()
            }
            if ([String]::IsNullOrEmpty("$value") -eq $false) {
                Write-Host "Name: $attribute; Value: $value $($value.GetType()) isNull? $($null -eq $value)"
                $otherAttributes.Add($attribute, $value)
            }
        }
    }
}

Select allOpen in new window

Actions:

if ($Action -eq 'create')
	{
		try
		{
			Write-Host "Create branch executed for $sAMAccountName"
			New-ADUser -sAMAccountName $sAMAccountName -Name $DisplayName -Path $path -otherAttributes $otherAttributes -Enable $true -AccountPassword $password -PasswordNeverExpires $true 
			#Write-Host "UserID $($DisplayName) created!"
		}
		catch
		{
			Write-Host "There was a problem creating UserID $($DisplayName). The account was not created!"
			Write-Host "Error message: $($_.Exception.Message)"
		}
	}
	elseif ($Action -eq 'update')
	{
		Write-Host "Update is triggered for $sAMAccountName"
	}
}

Select allOpen in new window

And what I believe might work...

elseif ($Action -eq 'update') {
    Write-Host "Update is triggered for $sAMAccountName"

    Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes
}

Select allOpen in new window

Chris,
It does the job, but a slight change in the scenario (sorry)
When the update is made, there is a possibility of Account being Disabled,

SQL has a column: AccountIsEnabled
If I add it in the Array & put it in OtherAttributes, it doesn't accept it.

I would prefer that the value is taken from the SQL, but it's only True or False in SQL.

Exclude it from otherAttributes; either you using your original switch, or the the modified "-in" approach above.

Then you just need a trigger for it. I suspect the two commands used below will just skip it if the account is already in the requested state. If not, it needs a bit of tweaking to only run when a state change is required.

if ($AccountIsEnabled -eq $true) {
    Get-ADUser -Identity $SamAccountName | Enable-ADAccount
} elseif ($AccountIsEnabled -eq $false) {
    Get-ADUser -Identity $SamAccountName | Disable-ADAccount
}

Select allOpen in new window

Can you please see if these lines are correct :
In hash table:  AccountisEnabled as Enabled,

Variable:
$AccountisEnabled = $dr.GetOrdinal("Enabled")

In While:
$AccountisEnabled = $dr.GetValue($AccountisEnabledOrdinal)

In Switch ($attribute)
"Enabled" { } #Ignore

Yes, I think that's right.

Is it not possible to do it with Get-Aduser | set-Aduser?

Like this:
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes   | Enable-ADAccount

It looks like Set-ADUser doesn't return by default.

Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru | Enable-ADAccount

Select allOpen in new window

Like this:

elseif ($Action -eq 'update')
      {
            if ($AccountIsEnabled -eq $true)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes | Enable-ADAccount
            }
            elseif ($AccountIsEnabled -eq $false)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes - | Disable-ADAccount
                  
            }
            Write-Host "Update is triggered for $sAMAccountName"
            #Get-ADUser -Identity $SamAccountName  | Set-ADUser  -Replace $otherAttributes
            
      }
}

Scratch that, we're over-complicating it.

Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -Enabled $AccountEnabled -Server serverName

Select allOpen in new window

can you please remove server name?

Done.

I get this  ERROR: Set-ADUser : Index was out of range. Must be non-negative and less than the size of the collection.

That's an awesome error... chocolate teapot error.

I imagine we're back to one or more of the attributes we're attempting to modify is not-quite-right. It'll be another one at a time job I think.

ha ha ha!!!
how about earlier approach of if else?
I tried that, the script ran without errors but didn't do anything....

It'll have the same issue if it triggers properly I imagine.

Alright, then how to proceed?

We'll have to reduce the content of $otherAttributes again until the one causing the problem is isolated.  Same as was done with New-ADUser. Super-fun, huh?

We can, perhaps, speed the process up, like this. At least then you don't have to hack things about quite so much.

foreach ($name in $attributes.Keys) {
    Write-Host "Updating $name with $($attribute[$name])"
    $replace = @{$name = $attributes[$name]}
    Get-ADUser -Identity $SamaccountName | Set-ADUser -Replace $replace
}

Select allOpen in new window

Wait,
I tried the command from console: Get-ADUser -Identity tfi | Set-ADUser -City Zürich | Disable-ADAccount

It changed the city, but didn't disable the account
So, I guess something else is to be done.

No, if you're using that approach you need the -PassThru parameter for Set-ADUser. Set-ADUser does not return anything by default, so no input for Disable-ADAccount.

Get-ADUser -Identity tfi | Set-ADUser -City Zürich -PassThru | Disable-ADAccount

Select allOpen in new window

What if we just hard code -Enabled $true in the nested if else statement?

Like this:

elseif ($Action -eq 'update')
      {
            if ($AccountIsEnabled -eq $true)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes | Enable-ADAccount
            }
            elseif ($AccountIsEnabled -eq $false)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -Enabled $false
                 
            }
            Write-Host "Update is triggered for $sAMAccountName"
            #Get-ADUser -Identity $SamAccountName  | Set-ADUser  -Replace $otherAttributes -Enabled $true
           
      }
}

Now you're confusing me :-D

Since I noticed the Enabled parameter we don't need to use the split for Enable/Disable-ADAccount.

elseif ($Action -eq 'update')
      {
        Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -Enabled $AccountIsEnabled
      }
}

Select allOpen in new window

If Enabled is set to $false (or $AccountIsEnabled is set to false) the account will be disabled.

If you want to stick with Enable/Disable-ADAccount you must include the PassThru parameter for Set-ADUser. Without that there's nothing to pass to the enable / disable commands.

elseif ($Action -eq 'update')
      {
            if ($AccountIsEnabled -eq $true)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru | Enable-ADAccount
            }
            elseif ($AccountIsEnabled -eq $false)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru | Disable-ADAccount
                  
            }
            Write-Host "Update is triggered for $sAMAccountName"
            #Get-ADUser -Identity $SamAccountName  | Set-ADUser  -Replace $otherAttributes -Enabled $true
            
      }
}

Select allOpen in new window

Sorry about that,
So I used the Enable-ADAccount, Disable-ADAccount like this:

elseif ($Action -eq 'update')
      {
            if ($AccountIsEnabled -eq $true)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru| Enable-ADAccount
            }
            elseif ($AccountIsEnabled -eq $false)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru | Disable-ADAccount
                  
            }
            Write-Host "Update is triggered for $sAMAccountName"            
      }

No error thrown, command runs fine, but nothing happens...


Running: Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -Enabled $AccountIsEnabled

Throws ERROR: Set-ADUser : Cannot convert 'System.String' to the type 'System.Nullable`1[System.Boolean]' required by parameter 'Enabled'.

Ahh I did wonder about that, your AccountIsEnabled field doesn't actually appear to be boolean (true or false). It's likely a string containing true or false?

If so, we can make it right :)

$AccountisEnabled = $dr.GetValue($AccountisEnabledOrdinal)
if ($AccountIsEnabled -is [String]) {
    $AccountIsEnabled = (Get-Variable $AccountIsEnabled).Value
}

Select allOpen in new window

Now:
Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -Enabled $AccountIsEnabled

ERROR: Set-ADUser : Index was out of range. Must be non-negative and less than the size of the collection.

Okay, back to that wonderful error. Which is where this bit hopefully comes in:

foreach ($name in $attributes.Keys) {
    Write-Host "Updating $name with $($attribute[$name])"
    $replace = @{$name = $attributes[$name]}
    Get-ADUser -Identity $SamaccountName | Set-ADUser -Replace $replace
}

Select allOpen in new window

That is, we write off the "Enabled" parameter part as "that works" and shelve it for a few minutes while we explore setting the attributes individually.

Where do I insert this? Inside the else if?

Yep, it replaces the content currently used to call Set-ADUser. By stepping through each in turn I hope we can quickly identify which one of the attributes is causing grief.

ERROR: Get-Variable : Cannot find a variable with name 'Test'.
ERROR: +         $AccountIsEnabled = (Get-Variable $AccountIsEnabled).Value

Don't use a dollar sign after Get-Variable:

Get-Variable AccountIsEnabled

Select allOpen in new window

No, you must.

That variable is expected to hold "True" or "False". This lets us convert the string version to the value of the variables $true and $false.

Code example:

$HonestThisIsABool = "True"
(Get-Variable $HonestThisIsABool).Value

Select allOpen in new window

A simple cast wouldn't work in this case because...

[Boolean]"False"

Select allOpen in new window

When I remove the $ sign, the part of the script doesn't execute....

Now, with the $ sign in place I get the error as above:

ERROR: Get-Variable : Cannot find a variable with name 'Test'.
SQLADUsers.ps1 (69, 24): ERROR: At Line: 69 char: 24
ERROR: +         $AccountIsEnabled = (Get-Variable $AccountIsEnabled).Value
ERROR: +                              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:     + CategoryInfo          : ObjectNotFound: (Test:String) [Get-Variable], ItemNotFoundException
ERROR:     + FullyQualifiedErrorId : VariableNotFound,Microsoft.PowerShell.Commands.GetVariableCommand
ERROR:

Hmm your Enabled field contains Test? Thats... unexpected. What other values does it hold?

Now, I'm Lost.... :(
there is no value as Test, AccountIsEnabled has value False
The only place where the Test is contained is in the field FirstName....

If this is leading the question off topic feel free to ignore me but, I don't follow:

Too many concurrent threads. Put this aside for the moment, let's stick with the chocolate teapot error and get that fixed first.

Once that's done, we'll fix up the enable / disable handling.

Just for the argument, I changed the FirstName to False & the script ran without errors....
However, no changes were made to the account

Stick with debugging Set-ADUser, I'll chat with Jason by message, too many threads otherwise :)

No no, I'm not involving in that, don't worry, that is something off the topic for me....
So the only place where Test was mentioned, was in the field FirstName.
I changed it to False, then the script ran without error.
But the impact is null

Oh wait I see.  I don't think the (Get-Variable whatever).value is resulting in 'False':

Gentlemen,
Can you please bear in mind that too many things only confuse...
@Jason,
Thanks for the inputs, but will be highly appreciated if you could translate them to current issue or how to use them in the issue at hand?

Thanks!

Chris,
How about we split the command in 2 parts, like:

elseif ($Action -eq 'update')
      {
             if ($AccountIsEnabled -eq $true)
            {
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes
                  Get-ADUser -Identity $SamAccountName | Enable-ADAccount
            }
            elseif ($AccountIsEnabled -eq $false)
            {
                  
                  Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes
                  Get-ADUser -Identity $SamAccountName | Disable-ADAccount
                  
            }
}

That works, it is, after all your script so it should be written so it makes sense to you.

I guess...
When I run the script now, it doesn't even reach the place of Else If.... seems there's a lot of code which is now conflicting...

Let me try to make sense & come back ..

When you're running Set-ADUser and you use the -Replace parameter it should be a hash table:

Set-ADUser whoever -Replace @{Somettribute="$otherAttributes"}

I don't know what attribute you're working with or what the value of $otherAttributes is, but Technet is pretty clear on the syntax:

https://technet.microsoft.com/en-us/library/ee617215.aspx

The variable $AccountIsEnabled has value First, which I fail to understand from where is it getting......

Well that's why God created breakpoints

& Jason you think I was so favourite of God I would be talking to Experts ;)

All right,
Here's the code Gentlemen, I have gone BLIND trying to figure out ...
Please help....

Import-Module ActiveDirectory

# Path to create users in
$path = "OU=TestUser,OU=test,DC=test,DC=com"

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Select statement to return new user accounts
# Needs to return "sAMAccountName" & "Password" columns
# Note: Other columns names should match AD attribute name
$sql = "Select FirstName as GivenName,
				LastName as sn,
				 DisplayName as DisplayName,
				 samAccountName as sAMAccountName,
				 EmailAddress as mail,
				 City as l,
				 State as st,
				 c as c,
				 CountryCode as co,
				 Department as Department,
				 StreetAddress as StreetAddress,
				 samAccountName+'@test.com' as userPrincipalName,
				 PostalCode as postalcode,
				 Title as Title,
				 Department as department,
				 Office as physicalDeliveryOfficeName,
				 OfficePhone as telephoneNumber,
				 MobilePhone as mobile,
				 Action as Action,			
				 AccountisEnabled as Enabled,
				 '11Passw0rd' as Password
from GetActiveDirectoryUsers where Action = 'update' OR Action = 'create' "

###########################

$cn = new-object system.data.sqlclient.sqlconnection
$cn.ConnectionString = $connectionString
$cn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = $sql
$cmd.connection = $cn
$dr = $cmd.ExecuteReader()

$colCount = $dr.FieldCount
$sAMAccountNameOrdinal = $dr.GetOrdinal("sAMAccountName")
$PasswordOrdinal = $dr.GetOrdinal("Password")
$DisplayNameOrdinal = $dr.GetOrdinal("DisplayName")
$ActionOrdinal = $dr.GetOrdinal("Action")
$AccountisEnabled = $dr.GetOrdinal("Enabled")

while ($dr.Read())
{
	# Get value of sAMAccountName column
	$sAMAccountName = $dr.GetValue($sAMAccountNameOrdinal)
	# Get value password column (converted to secure string for New-ADUser Cmdlet)
	$password = ConvertTo-SecureString -AsPlainText $dr.GetValue($PasswordOrdinal) -Force
	# Get value of DisplayName column
	$DisplayName = $dr.GetValue($DisplayNameOrdinal)
	# Get value of Action column
	$Action = $dr.GetValue($ActionOrdinal)
	# Get value of AccountisEnabled column
	$AccountisEnabled = $dr.GetValue($AccountisEnabledOrdinal)
	<#if ($AccountIsEnabled -is [String])
	{
		$AccountIsEnabled = (Get-Variable $AccountIsEnabled).Value
	}
	#>
	write-host "Creating user account..." $sAMAccountName
	
	$otherAttributes = New-Object System.Collections.HashTable
	
	# Create a hash table of attribute names and attribute values
	# Used to populate other attributes. 
	
	for ($i = 0; $i -le $colCount - 1; $i++)
	{
		$attribute = $dr.GetName($i)
		
		switch ($attribute)
		{
			"Password"{ } #Ignore
			"SAMAccountName" { } #Ignore
			"Action" { } #Ignore
			"Enabled" { } #Ignore
			default
			{
				#$otherAttributes.Add($attribute, $dr.GetValue($i))
				$value = $dr.GetValue($i)
				if ($value -is [String])
				{
					$value = $value.Trim()
				}
				if ([String]::IsNullOrEmpty("$value") -eq $false)
				{
					#Write-Host "Name: $attribute; Value: $value $($value.GetType()) isNull? $($null -eq $value)"
					$otherAttributes.Add($attribute, $value)
				}
			}
		}
		
	}
	# Capture Error for not resetting SQL Values in Action
	$Error.clear()
	# Create Active Directory User Account
	if ($Action -eq 'create')
	{
		try
		{
			Write-Host "Create branch executed for $sAMAccountName"
			New-ADUser -sAMAccountName $sAMAccountName -Name $DisplayName -Path $path -Enable $true -otherAttributes $otherAttributes -AccountPassword $password -PasswordNeverExpires $true 
			
			#Write-Host "UserID $($DisplayName) created!"
		}
		catch
		{
			Write-Host "There was a problem creating UserID $($DisplayName). The account was not created!"
			
			Write-Host "Error message: $($_.Exception.Message)"
		}
	}
	elseif ($Action -eq 'update')
	{
		Write-Host "update elseif"
		Write-Host "this is value $AccountisEnabled"
		 if ($AccountIsEnabled -eq 'true')
		{
			Write-Host "Update true"
			#Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru| Enable-ADAccount 
			#Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes 
			#Get-ADUser -Identity $SamAccountName | Enable-ADAccount
		}
		elseif ($AccountIsEnabled -eq 'false')
		{
			Write-Host "Update false"
			#Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru | Disable-ADAccount 
			Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes 
			Get-ADUser -Identity $SamAccountName | Disable-ADAccount
			
		}
		<#
		foreach ($name in $attributes.Keys)
		{
			Write-Host "Updating $name with $($attribute[$name])"
			$replace = @{ $name = $attributes[$name] }
			Get-ADUser -Identity $SamaccountName | Set-ADUser -Replace $replace
		}#>
		#Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -Enabled $AccountIsEnabled
		#Write-Host "Update is triggered for $sAMAccountName"		
	}
	##############
		#Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -Enabled $AccountEnabled 
		#Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -Enabled 
		#Write-Host "Name: $attribute; Value: $value $($value.GetType()) isNull? $($null -eq $value)"
	}

$dr.Close()
# If the code runs successfully, then Clear the value from SQL in Action
<#	if (!$Error)
{
	$updateqry = "update dbo.GetActiveDirectoryUsers set Action = ' ' where Action = 'create' OR Action ='update' ;"
	$cmd.CommandText = $updateqry
	$cmd.ExecuteNonQuery()
}#>
$cn.Close()

Select allOpen in new window

First error found: $AccountisEnabledOrdinal = $dr.GetOrdinal("Enabled")

Does that fix retrieval of the enabled / disabled state?

Chris,
The value is showing False!
Let me go bit further...

I would put a breakpoint before damn near every variable and track their values.  Personally I use ISE Steroids and the Variable Monitor Addon, but there is a standalone variable module for ISE available on the tools website:

https://social.technet.microsoft.com/wiki/contents/articles/2969.windows-powershell-ise-add-on-tools.aspx

Developing good debugging techniques and writing in an abundance of logging goes a long way in these situations.

I can't disagree. I use VS Code, probably much the same (it also has a watch interface). It's free, I like not paying for ISE Steriods.

I didn't.  I just blogged about it and got a free license under Tobias' Helping Hands Initiative :)

Thanks Jason,
I have put it on my to-do list !

@Chris,
Seems to Disable the account!!!

However, now the create doesn't execute at all :(
The code is as posted last...

Ok, this is strange, but the script doesn't execute if ($Action -eq 'create')
or if ($Action -eq 'update')

The value of $Action is captured correctly as create & update?

Your code is getting too complex, we need to split it up a bit. Reduce the scope, make debugging a bit easier. One sec.

This is the latest code:

Import-Module ActiveDirectory

# Path to create users in
$path = "OU=TestUser,OU=test,DC=test,DC=com"

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Select statement to return new user accounts
# Needs to return "sAMAccountName" & "Password" columns
# Note: Other columns names should match AD attribute name
$sql = "Select FirstName as GivenName,
				LastName as sn,
				 DisplayName as DisplayName,
				 samAccountName as sAMAccountName,
				 EmailAddress as mail,
				 City as l,
				 State as st,
				 c as c,
				 CountryCode as co,
				 Department as Department,
				 StreetAddress as StreetAddress,
				 samAccountName+'@test.com' as userPrincipalName,
				 PostalCode as postalcode,
				 Title as Title,
				 Department as department,
				 Office as physicalDeliveryOfficeName,
				 OfficePhone as telephoneNumber,
				 MobilePhone as mobile,
				 Action as Action,			
				 AccountisEnabled as Enabled,
				 '11Passw0rd' as Password
from GetActiveDirectoryUsers where Action = 'update' OR Action = 'create' "

###########################

$cn = new-object system.data.sqlclient.sqlconnection
$cn.ConnectionString = $connectionString
$cn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = $sql
$cmd.connection = $cn
$dr = $cmd.ExecuteReader()

$colCount = $dr.FieldCount
$sAMAccountNameOrdinal = $dr.GetOrdinal("sAMAccountName")
$PasswordOrdinal = $dr.GetOrdinal("Password")
$DisplayNameOrdinal = $dr.GetOrdinal("DisplayName")
$ActionOrdinal = $dr.GetOrdinal("Action")
$AccountisEnabledOrdinal = $dr.GetOrdinal("Enabled")

while ($dr.Read())
{
	# Get value of sAMAccountName column
	$sAMAccountName = $dr.GetValue($sAMAccountNameOrdinal)
	# Get value password column (converted to secure string for New-ADUser Cmdlet)
	$password = ConvertTo-SecureString -AsPlainText $dr.GetValue($PasswordOrdinal) -Force
	# Get value of DisplayName column
	$DisplayName = $dr.GetValue($DisplayNameOrdinal)
	# Get value of Action column
	$Action = $dr.GetValue($ActionOrdinal)
	# Get value of AccountisEnabled column
	$AccountisEnabled = $dr.GetValue($AccountisEnabledOrdinal)
	if ($AccountIsEnabled -is [String])
	{
		$AccountIsEnabled = (Get-Variable $AccountIsEnabled).Value
	}
	write-host "Creating user account..." $sAMAccountName
	
	$otherAttributes = New-Object System.Collections.HashTable
	
	# Create a hash table of attribute names and attribute values
	# Used to populate other attributes. 
	
	for ($i = 0; $i -le $colCount - 1; $i++)
	{
		$attribute = $dr.GetName($i)
		
		switch ($attribute)
		{
			"Password"{ } #Ignore
			"SAMAccountName" { } #Ignore
			"Action" { } #Ignore
			"Enabled" { } #Ignore
			default
			{
				#$otherAttributes.Add($attribute, $dr.GetValue($i))
				$value = $dr.GetValue($i)
				if ($value -is [String])
				{
					$value = $value.Trim()
				}
				if ([String]::IsNullOrEmpty("$value") -eq $false)
				{
					$otherAttributes.Add($attribute, $value)
				}
			}
		}
		
	}
	Write-Host "Action = $Action"
	# Capture Error for not resetting SQL Values in Action
	$Error.clear()
	
	# Create Active Directory User Account
	if ($Action -eq 'create')
	{
		Write-Host "Create executed"
		try
		{
			Write-Host "Create branch executed for $sAMAccountName"
			New-ADUser -sAMAccountName $sAMAccountName -Name $DisplayName -Path $path -Enable $true -otherAttributes $otherAttributes -AccountPassword $password -PasswordNeverExpires $true 
			
			Write-Host "UserID $($DisplayName) created!"
		}
		catch
		{
			Write-Host "There was a problem creating UserID $($DisplayName). The account was not created!"
			
			Write-Host "Error message: $($_.Exception.Message)"
		}
	}
	elseif ($Action -eq 'update')
	{
		if ($AccountIsEnabled -eq $true)
		{
			Write-Host "Update true"
			Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru| Enable-ADAccount
		}
		elseif ($AccountIsEnabled -eq $false)
		{
			Write-Host "Update false"
			Get-ADUser -Identity $SamAccountName | Set-ADUser -Replace $otherAttributes -PassThru | Disable-ADAccount 
		}
	}
}

$dr.Close()
# If the code runs successfully, then Clear the value from SQL in Action
<#	if (!$Error)
{
	$updateqry = "update dbo.GetActiveDirectoryUsers set Action = ' ' where Action = 'create' OR Action ='update' ;"
	$cmd.CommandText = $updateqry
	$cmd.ExecuteNonQuery()
}#>
$cn.Close()

Select allOpen in new window

Are you happy to do a bit of component testing for me? it'll be a bit of a repeat of work you've already done. The goal is to split it down into far more manageable chunks.

The smaller chunks make targeted debugging a lot easier.

Sure, I can also learn something for future!

Okay, the goal of the first part is to get clean things out of the SQL database. All this function does is execute the SQL query and convert the output into an object (one object per row).

Once this has run, we should have values in the variable $usersToProcess. Assuming the code works, by writing it in this style we have a reusable function for performing queries against an SQL database.

function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)
                    $value = $reader.GetValue($i)
                    
                    if ($value -is [DBNull]) {
                        $value = $null
                    }
                    if ($value -is [String]) {
                        $value = $value.Trim()
                    }

                    $psObject | Add-Member $name $value
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"
# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Department,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString

Select allOpen in new window

Alright, I put this in new script, to start from Scratch...
So far when I run I only get : ERROR: Add-Member : Cannot add a member with the name "department" because a member with that name already exists. If you want to overwrite the member anyway, use  the Force parameter to overwrite it.

That only happens if there's more than one column with same name in the query. And now I look at the query, there is.

Can we lose the repetition of Department in the query? I had to check to make sure I didn't add that :)

We'll have a handler for the condition as well I think. The example below removes the duplicated value from the query as well.

function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String]) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"
# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString

Select allOpen in new window

No Error, only You cannot call a method on a null-valued expression.

Can you show me the full error message?

Updated function to account for what might be the most likely source of the error.

function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

Select allOpen in new window

This ran without any errors.

Okay, but does $usersToProcess have anything in it? :)

Chris,
I lost you, the second function you gave has no HashTable?
Was I supposed to add it to First function?

My variables are like this:
"Select FirstName as GivenName,
                        LastName as sn,
                         DisplayName as DisplayName,
                         samAccountName as sAMAccountName,
                         EmailAddress as mail,
                         City as l,
                         State as st,
                         c as c,
                         CountryCode as co,
                         Department as Department,
                         StreetAddress as StreetAddress,
                         samAccountName+'@wuestundpartner.com' as userPrincipalName,
                         PostalCode as postalcode,
                         Title as Title,
                         Department as department,
                         Office as physicalDeliveryOfficeName,
                         OfficePhone as telephoneNumber,
                         MobilePhone as mobile,
                         Action as Action,                  
                         AccountisEnabled as Enabled,
                         '11Passw0rd' as Password
from GetActiveDirectoryUsers where Action = 'update' OR Action = 'create' "


I added them under your second function & got the same "You cannot call a method on a null-valued expression"

The goal if this command is to get information out of the database. It does absolutely nothing else, it's entire job is defined by that.

Once we've got a valid representation of the table we'll move onto the next step, defining how to call New-ADUser with as little work as possible.

ok, then if i run the second function, it runs without errors.

Step back a moment, all we're doing with this function is running the database query. The expected output is a set of objects representing what we had in the database.

If it's run exactly as above this command should show us everything we managed to scrape out:

$usersToProcess | Out-GridView

Select allOpen in new window

The idea is to separate the database operation from the rest. Get the database query and it's output right and the work the next part must do is much more straight-forward.

should I add this at bottom?

or inside the open connection?

At the end. The function, if it works, is sacrosanct, it should not be changed at all. We pass the $sql and $connectionString variables in as parameters.

I added after the close of function, but no grid view

Did you remove the code I had to call the function?

It won't do anything on its own.

This is the code I'm hoping you'll run for me (after fixing the connection string towards the bottom):

function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"
# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString

$usersToProcess | Out-GridView

Select allOpen in new window

What do you mean: (after fixing the connection string towards the bottom)?

If i run the code as it is, i still get : You cannot call a method on a null-valued expression.

Test version which should give a line number. I need the exact error (the whole thing), not just the descriptive message. Coding the SQL query parser from memory I'm afraid, no servers I can test against.

function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    # try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    # } catch {
    #    throw
    #} finally {
    #    if ($SqlConnection.State -eq 'Opened') {
    #        $SqlConnection.Close()
    #    }
    #}
}

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"
# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString

$usersToProcess | Out-GridView

Select allOpen in new window

Sorry about that, here is the error:
ERROR: You cannot call a method on a null-valued expression.
SQL-AD-O365-LF.ps1 (29, 9): ERROR: At Line: 29 char: 9
ERROR: +                 if (-not $psObject.Properties.Item($name))
ERROR: +                     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
ERROR:     + FullyQualifiedErrorId : InvokeMethodOnNull
ERROR:
ERROR: You cannot call a method on a null-valued expression.

It did open the grid view for a second

Awesome, thanks. Silly mistake (on my part, of course) fixed below.

function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.PSObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"
# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString

$usersToProcess | Out-GridView

Select allOpen in new window

Alright, no errors, grid-view flashes & then goes away!

Ahh you're running it in a script? I'm more of a paste into the console...

Not to worry, let's drop it to a file instead. We'll definitely be able to view that.

This:

$usersToProcess | Out-GridView

Select allOpen in new window

Becomes:

$usersToProcess | Export-Csv usersToProcess.csv -NoTypeInformation

Select allOpen in new window

This step is just to verify we're getting something useful back from SQL.

No worries, I ran it from console & opens Grid-view.
I see all the 3 users I have staged for Testing!!!

Awesome, just what we needed.

Righto, we've happily separated the database read from the rest, that's great because now we can introduce two more process-specific functions. We'll start with the function to create the new user.

function NewADUser {
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String]$Path = "OU=TestUser,OU=test,DC=test,DC=com"
    )

    process {
        if ($UserInformation.Action -eq 'create') {
            $params = @{
                Path = $Path
                AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
            }
            $otherAttributes = @{}
            $command = Get-Command New-ADUser

            # Use as many named parameters as possible
            foreach ($property in $UserInformation.PSObject.Properties | Where-Object Name -notin 'Action', 'Password') {
                if ($command.Parameters.ContainsKey($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $otherAttributes.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($otherAttributes.Count -gt 0) {
                $params.OtherAttributes = $otherAttributes
            }

            Write-Host "Creating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            New-ADUser @params -WhatIf
        }
    }
}

Select allOpen in new window

We use this function like this:

$usersToProcess | NewADUser

Select allOpen in new window

At the  moment it won't make changes, that's because I added the WhatIf parameter to New-ADUser (at the bottom of the function). It should show you a blob of stuff which shows us how it's managed to fill the parameters for New-ADUser (or not if I made mistakes).

NO Errors so far
Shall I remove whatIf?

It should have dumped a blob of text to the screen. Did that pop up?

By all means remove WhatIf and give it a try. I'd be moderately surprised if I got it right first time :)

Ok I removed the WhatIf & it shows the Grid-View
then I added at the bottom: $usersToProcess | New-ADUser
& got these errors:

RROR: New-ADUser : The input object cannot be bound because it did not contain the information required to bind all mandatory parameters:  Name
SQL-AD-O365-LF.ps1 (146, 19): ERROR: At Line: 146 char: 19
ERROR: + $usersToProcess | New-ADUser
ERROR: +                   ~~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidArgument: (@{GivenName=Fir...ord=11Passw0rd}:PSObject) [New-ADUser], ParameterBindingException
ERROR:     + FullyQualifiedErrorId : InputObjectMissingMandatory,Microsoft.ActiveDirectory.Management.Commands.NewADUser

Ahh I see, I missed that you'd filled that from DisplayName. It's fixed here.

function NewADUser {
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String]$Path = "OU=TestUser,OU=test,DC=test,DC=com"
    )

    process {
        if ($UserInformation.Action -eq 'create') {
            $params = @{
                Path = $Path
                AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
            }
            $otherAttributes = @{}
            $command = Get-Command New-ADUser

            # Use as many named parameters as possible
            foreach ($property in $UserInformation.PSObject.Properties | Where-Object Name -notin 'Action', 'Password') {
                if ($command.Parameters.ContainsKey($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $otherAttributes.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($otherAttributes.Count -gt 0) {
                $params.OtherAttributes = $otherAttributes
            }
            if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                $params.Name = $UserInformation.DisplayName
            }

            Write-Host "Creating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            New-ADUser @params
        }
    }
}

Select allOpen in new window

I ran it as you have posted, it throws no error, however no account is created or updated.
Do I have to add
$usersToProcess | New-ADUser
?

Because when I add, I get the following:

ERROR: New-ADUser : The input object cannot be bound because it did not contain the information required to bind all mandatory parameters:  Name
SQL-AD-O365-LF.ps1 (150, 19): ERROR: At Line: 150 char: 19
ERROR: + $usersToProcess | New-ADUser
ERROR: +                   ~~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidArgument: (@{GivenName=Fir...ord=11Passw0rd}:PSObject) [New-ADUser], ParameterBindingException
ERROR:     + FullyQualifiedErrorId : InputObjectMissingMandatory,Microsoft.ActiveDirectory.Management.Commands.NewADUser
ERROR:

It's a case of update that function in the main script then re-run it. The whole things looks like this now. Do notice that I've intentionally omitted the hyphen from the last line. This is so it uses our function as a wrapper, the wrapper (hopefully) fills all the parameters New-ADUser needs.

#Requires -Module ActiveDirectory

function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.PSObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

function NewADUser {
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String]$Path = "OU=TestUser,OU=test,DC=test,DC=com"
    )

    process {
        if ($UserInformation.Action -eq 'create') {
            $params = @{
                Path = $Path
                AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
            }
            $otherAttributes = @{}
            $command = Get-Command New-ADUser

            # Use as many named parameters as possible
            foreach ($property in $UserInformation.PSObject.Properties | Where-Object Name -notin 'Action', 'Password') {
                if ($command.Parameters.ContainsKey($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $otherAttributes.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($otherAttributes.Count -gt 0) {
                $params.OtherAttributes = $otherAttributes
            }
            if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                $params.Name = $UserInformation.DisplayName
            }

            Write-Host "Creating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            New-ADUser @params
        }
    }
}

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"
# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString

$usersToProcess | NewADUser

Select allOpen in new window

where are you importing module active directory?

Right at the top, the #Requires -Module statement will load it. If you're not comfortable with that you can re-instate Import-Module ActiveDirectory. It can go at the top of the script.

Sorry, but it throws error:

ERROR: New-ADUser : Cannot convert 'System.String' to the type 'System.Nullable`1[System.Boolean]' required by parameter 'Enabled'.
SQL-AD-O365-LF.ps1 (112, 15): ERROR: At Line: 112 char: 15
ERROR: +             New-ADUser @params
ERROR: +                        ~~~~~~~
ERROR:     + CategoryInfo          : InvalidArgument: (:) [New-ADUser], ParameterBindingException
ERROR:     + FullyQualifiedErrorId : CannotConvertArgument,Microsoft.ActiveDirectory.Management.Commands.NewADUser

It does show all the attributes of the user:
Creating user with the following information
{
    "AccountPassword":  {
                            "Length":  10
                        },
    "userPrincipalName":  "tno@test.com",
    "DisplayName":  "Test Now",
    "sAMAccountName":  "tno",
    "Name":  "Test Now",
    "Department":  "Team Z",
    "Title":  "Re",
    "GivenName":  "Test",
    "OtherAttributes":  {
                            "st":  "Zürich",
                            "mobile":  "",
                            "co":  "756",
                            "mail":  "test.now@test.com",
                            "l":  "Zürich",
                            "telephoneNumber":  "+4 45",
                            "c":  "CH",
                            "physicalDeliveryOfficeName":  "",
                            "sn":  "Now"
                        },
    "postalcode":  "8001",
    "StreetAddress":  " 5",
    "Path":  "OU=TestUser,OU=test,DC=test,DC=com",
    "Enabled":  "True"
}

Chris,
It still is stuck at Enabled!

Ah yes, sorry I forgot to inject that update. A minor tweak happily.

I've got to catch a train, I'll pick it up when I get home.

No worries, I am leaving too.
If you get time, have a look, I can only check tomorrow.

Thanks a lot Chris & have a Great Evening!!!

Head-start on tomorrow.

Before we run the NewADUser function we're going to apply what amounts to a cleaning filter to $usersToProcess to fix that Enabled value again. This does not belong in Invoke-SqlQuery because it's a transformation we're applying to the data set (not something the SQL query did wrong).

$usersToProcess | ForEach-Object {
    $_.Enabled = (Get-Variable $_.Enabled).Value
}

Select allOpen in new window

This does not need assigning, it updates values in the array in memory.

And the rest of the updates. The code below contains the following changes:

• Clean Enabled property (the snippet immediately above)

• NewADUser will no longer set attributes with no value

• SetADUser has been added

• RemoveAction has been added

• All functions have error handling

• Every action taken writes to a Status field either with OK or a failure notice an any error message

It's longer than the original, but this gives us the opportunity to debug at each distinct stage. I fully expect there will still be errors, but I hope we have better tools to break down the errors now.

This is not written to die at the first error ($ErrorActionPreference = 'Stop'). It does as much work as it can and will only take an account out of the database "queue" if it succeeds. I include this notice because there's a lot of community pressure behind that style of error handling, I'm not sold on it.

#Requires -Module ActiveDirectory

function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.PSObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

function NewADUser {
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String]$Path = "OU=TestUser,OU=test,DC=test,DC=com"
    )

    process {
        if ($UserInformation.Action -eq 'create') {
            $params = @{
                Path = $Path
                AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
            }
            $otherAttributes = @{}
            $command = Get-Command New-ADUser

            # Create a list of properties (from UserInformation) which will be written to the new user
            $propertiesToSet = $UserInformation.PSObject.Properties | Where-Object { $_.Name -notin 'Action', 'Password', 'Status' -and $null -ne $_.Value }

            # Use as many named parameters as possible
            foreach ($property in $propertiesToSet) {
                if ($command.Parameters.ContainsKey($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $otherAttributes.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($otherAttributes.Count -gt 0) {
                $params.OtherAttributes = $otherAttributes
            }
            if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                $params.Name = $UserInformation.DisplayName
            }

            Write-Host "Creating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            try {
                New-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Create failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function SetADUser {
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    process {
        if ($UserInformation.Action -eq 'update') {
            $params = @{
                Identity = $_.SamAccountName
            }
            $replace = @{}
            $command = Get-Command Set-ADUser

            # Create a list of properties (from UserInformation) which will be set on the existing user
            $propertiesToSet = $UserInformation.PSObject.Properties | Where-Object { $_.Name -notin 'Action', 'Password', 'Status', 'SamAccountName' -and $null -ne $_.Value }

            # Use as many named parameters as possible
            foreach ($property in $propertiesToSet) {
                if ($command.Parameters.ContainsKey($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $replace.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($replace.Count -gt 0) {
                $params.Replace = $replace
            }
            if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                $params.Name = $UserInformation.DisplayName
            }

            Write-Host "Updating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            try {
                Set-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Set failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function RemoveAction {
    param(
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    begin {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()
    }

    process {
        if ($UserInformation.Status -eq 'OK') {
            try {
                $SqlCommand = $SqlConnection.CreateCommand()
                $SqlCommand.CommandText = "UPDATE dbo.GetActiveDirectoryUsers SET Action = ' ' WHERE SamAccountName = '{0}';" -f $UserInformation.SamAccountName
                $SqlCommand.ExecuteNonQuery()
            } catch {
                Write-Error -ErrorRecord $_
            }
        }
    }

    end {
        $SqlConnection.Close()
    }
}

#
# Main
#

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

# A status field is added to each entry. This is used to persistently record the result of our commands.
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
    Select-Object *, Status

# Update any fields which need modification
$usersToProcess | ForEach-Object {
    $_.Enabled = (Get-Variable $_.Enabled).Value
}

# Commit the changes to AD
# NewADUser internally filters usersToProcess on Action = Create
$usersToProcess | NewADUser
# SetADUser internally filters usersToProcess on Action = Update
$usersToProcess | SetADUser

# Update database
# RemoveAction internally filters usersToProcess on Status = OK
$usersToProcess | RemoveAction

Select allOpen in new window

Small suggestion...I notice several try/catch/finally blocks, but I'm not 100% sure the commands in the try block wouldn't generate non-terminating errors so I set the $ErrorActionPreference.  I also started a transcript log and wrapped everything up in a begin/process/end.

#Requires -Module ActiveDirectory
#Requires -Version 3.0

begin {
  $date = Get-Date -Format 'yyyyMMddHHmm'
  $currenteap = $ErrorActionPreference
  $ErrorActionPreference = 'Stop'
  Start-Transcript -Path "$env:USERPROFILE\Desktop\transcript_$date.log"
}

process {
  function Invoke-SqlQuery {
    param(
      [String]$Query,

      [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
      $SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
      $SqlConnection.ConnectionString = $ConnectionString
      $SqlConnection.Open()

      $SqlCommand = $SqlConnection.CreateCommand()
      $SqlCommand.CommandText = $Query
    
      $reader = $SqlCommand.ExecuteReader()

      if ($reader.HasRows) {
        while ($reader.Read()) {
          $psObject = New-Object -TypeName PSObject
          for ($i = 0; $i -lt $reader.FieldCount; $i++) {
            $name = $reader.GetName($i)

            if (-not $psObject.PSObject.Properties.Item($name)) {
              $value = $reader.GetValue($i)
                        
              if ($value -is [DBNull]) {$value = $null}
              if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {$value = $value.Trim()}

              $psObject | Add-Member $name $value
            }
          }
          $psObject
        }
      }

      $reader.Close()
    } catch {throw} finally {
      if ($SqlConnection.State -eq 'Opened') {$SqlConnection.Close()}
    }
  }

  function NewADUser {
    param (
      [Parameter(ValueFromPipeline = $true)]
      [PSObject]$UserInformation,

      [String]$Path = 'OU=TestUser,OU=test,DC=test,DC=com'
    )

    process {
      if ($UserInformation.Action -eq 'create') {
        $params = @{
          Path            = $Path
          AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
        }
        $otherAttributes = @{}
        $command = Get-Command New-ADUser

        # Create a list of properties (from UserInformation) which will be written to the new user
        $propertiesToSet = $UserInformation.PSObject.Properties | Where-Object -FilterScript { $_.Name -notin 'Action', 'Password', 'Status' -and $null -ne $_.Value }

        # Use as many named parameters as possible
        foreach ($property in $propertiesToSet) {
          if ($command.Parameters.ContainsKey($property.Name)) {$params.($property.Name) = $property.Value} else {$otherAttributes.Add($property.Name, $property.Value)}
        }
            
        # Load everything else into OtherAttributes
        if ($otherAttributes.Count -gt 0) {$params.OtherAttributes = $otherAttributes}
        if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {$params.Name = $UserInformation.DisplayName}

        Write-Host -Object 'Creating user with the following information'
        Write-Host -Object ($params | ConvertTo-Json -Depth 3)

        try {
          New-ADUser @params -ErrorAction Stop
          $UserInformation.Status = 'OK'
        } catch {
          $UserInformation.Status = 'Create failed ({0})' -f $_.Exception.Message

          Write-Error -ErrorRecord $_
        }
      }
    }
  }

  function SetADUser {
    param (
      [Parameter(ValueFromPipeline = $true)]
      [PSObject]$UserInformation
    )

    process {
      if ($UserInformation.Action -eq 'update') {
        $params = @{
          Identity = $_.SamAccountName
        }
        $replace = @{}
        $command = Get-Command Set-ADUser

        # Create a list of properties (from UserInformation) which will be set on the existing user
        $propertiesToSet = $UserInformation.PSObject.Properties | Where-Object -FilterScript { $_.Name -notin 'Action', 'Password', 'Status', 'SamAccountName' -and $null -ne $_.Value }

        # Use as many named parameters as possible
        foreach ($property in $propertiesToSet) {
          if ($command.Parameters.ContainsKey($property.Name)) {$params.($property.Name) = $property.Value} else {$replace.Add($property.Name, $property.Value)}
        }
            
        # Load everything else into OtherAttributes
        if ($replace.Count -gt 0) {$params.Replace = $replace}
        if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {$params.Name = $UserInformation.DisplayName}

        Write-Host -Object 'Updating user with the following information'
        Write-Host -Object ($params | ConvertTo-Json -Depth 3)

        try {
          Set-ADUser @params -ErrorAction Stop
          $UserInformation.Status = 'OK'
        } catch {
          $UserInformation.Status = 'Set failed ({0})' -f $_.Exception.Message

          Write-Error -ErrorRecord $_
        }
      }
    }
  }

  function RemoveAction {
    param(
      [Parameter(ValueFromPipeline = $true)]
      [PSObject]$UserInformation
    )

    begin {
      $SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
      $SqlConnection.ConnectionString = $ConnectionString
      $SqlConnection.Open()
    }

    process {
      if ($UserInformation.Status -eq 'OK') {
        try {
          $SqlCommand = $SqlConnection.CreateCommand()
          $SqlCommand.CommandText = "UPDATE dbo.GetActiveDirectoryUsers SET Action = ' ' WHERE SamAccountName = '{0}';" -f $UserInformation.SamAccountName
          $SqlCommand.ExecuteNonQuery()
        } catch {Write-Error -ErrorRecord $_}
      }
    }

    end {
      $SqlConnection.Close()
    }
  }

  #
  # Main
  #

  # Connection string to SQL Server database
  $ConnectionString = 'Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;'

  # Query
  $sql = "Select FirstName as GivenName,
    LastName as sn,
    DisplayName,
    samAccountName,
    EmailAddress as mail,
    City as l,
    State as st,
    c,
    CountryCode as co,
    Department,
    StreetAddress,
    samAccountName+'@test.com' as userPrincipalName,
    PostalCode,
    Title,
    Office as physicalDeliveryOfficeName,
    OfficePhone as telephoneNumber,
    MobilePhone as mobile,
    Action,            
    AccountisEnabled as Enabled,
    '11Passw0rd' as Password
  FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

  # A status field is added to each entry. This is used to persistently record the result of our commands.
  $usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $ConnectionString |
  Select-Object -Property *, Status

  # Update any fields which need modification
  $usersToProcess | ForEach-Object -Process {$_.Enabled = (Get-Variable -Name $_.Enabled).Value}

  # Commit the changes to AD
  # NewADUser internally filters usersToProcess on Action = Create
  $usersToProcess | NewADUser
  # SetADUser internally filters usersToProcess on Action = Update
  $usersToProcess | SetADUser

  # Update database
  # RemoveAction internally filters usersToProcess on Status = OK
  $usersToProcess | RemoveAction
}

end {
  $ErrorActionPreference = $currenteap
  Stop-Transcript
}

Select allOpen in new window

hahaha... see community pressure.

I explicitly do *not* want to die on every single error and therefore recommend that ErrorActionPreference is *not* globally set. Doing so causes Write-Error to throw and kill the entire process when really a single operation needs to be trapped.

Non-terminating errors are created when individual changes fail a command (like New-ADUser) can fail for a myriad of reasons. The error messages are also captured in the status property of each object.

Error are handled in small try-catch blocks and written to a status object. Write-Error is used to communicate the error to anyone watching / transcripting.

With respect to begin/process/end. The main script is not processing a pipeline. Begin/process/end are an unnecessary complication, they're not be used, they're just clutter.

Well that single hypothetical process you're referring to could very easily have a domino effect that causes a myriad of other issues.  Shouldn't your goal be to account for as many conceivable errors as you can?  I notice you did not address the error action at all, so if a non-terminating error occurs in one of the try blocks, it makes the catch block not just clutter, but useless on a level usually reserved for Project Managers.  

Man I'll defer to you everytime, and I readily admit you can run laps around me here so don't take it as pressure.  Take it as just me suggesting alternate ideas and learning from an authority on the subject.  We're already about 100 threads deep here what's a few more?  We're doing this for free so our motivation is the quality of the help we provide both to the OP and each other, right?  

By the way speaking of authorities, I've seen Jeffery Snover and Ed Wilson both use begin/process/end in a similar manner and it was very effective when used in conjunction with a return statement.

This would be a very, very significant detour from the content of the (very long) thread. I'm totally happy to discuss it, but it is an important topic and, if it's worth discussing, it's worth a question :)

The try / catch blocks above isolate the single points I think might die. There are a very small number of PS commands in the script, only PS commands throw non-terminating errors. All are isolated and tagged with ErrorAction Stop.

The point of error control in this script is to avoid throwing a fat terminating error every time a single user operation goes a bit wrong because an error with a single user does not necessarily represent a big problem. Same applies if you were making a change across 1000 servers, you'd be annoyed if the script completely bombed half way through because just one server failed.

Here captures it nicely for me:

https://github.com/PoshCode/PowerShellPracticeAndStyle/blob/master/Best%20Practices/Error%20Handling.md

Ideally, whatever command or code you think might bomb should be dealing with one thing: querying one computer, deleting one file, updating one user. That way, if an error occurs, you can handle it and then get on with the next thing.

We have control around the "one" operation. The controlling script surrounding that is left as a reporting vehicle for when things do go wrong. And they will with this kind of operation, on a semi-regular basis. That they do is not necessarily a show stopper.

Begin / process / end can be used in whatever way is right for a situation. But I'd argue with everyone (including Ed and Jeffery) that they're clutter unless there's something achieved by using them. You can see I use begin / process / end within the script, in the commands processing pipeline input, it's not that I don't see the value when used properly.

Wow!!!
That was lot of Brain Storming ....

Morning Guys,

Chris,
Thanks, I ran the code & got the following:

New-ADUser:
Creating user with the following information
{
    "AccountPassword":  {
                            "Length":  10
                        },
    "userPrincipalName":  "tse@test.com",
    "DisplayName":  "Test Second",
    "sAMAccountName":  "tse",
    "Name":  "Test Second",
    "Department":  "Team Z",
    "Title":  "Andreas",
    "GivenName":  "Test",
    "OtherAttributes":  {
                            "st":  "Zürich",
                            "mobile":  "",
                            "co":  "756",
                            "mail":  "test.second@test.com",
                            "l":  "Zürich",
                            "telephoneNumber":  "",
                            "c":  "CH",
                            "physicalDeliveryOfficeName":  "",
                            "sn":  "Second"
                        },
    "postalcode":  "8001",
    "StreetAddress":  " 5",
    "Path":  "OU=TestUser,OU=test,DC=test,DC=com",
    "Enabled":  false
}

ERROR: NewADUser : The server is unwilling to process the request
SQL-AD-O365-LF.ps1 (270, 19): ERROR: At Line: 270 char: 19
ERROR: + $usersToProcess | NewADUser
ERROR: +                   ~~~~~~~~~
ERROR:     + CategoryInfo          : NotSpecified: (CN=Test Second,......,DC=com:String) [Write-Error], ADException
ERROR:     + FullyQualifiedErrorId : ActiveDirectoryServer:0,NewADUser
ERROR:

Set-ADUser

Updating user with the following information
{
    "postalcode":  "60311",
    "Enabled":  false,
    "Replace":  {
                    "st":  "Frankfurt",
                    "mobile":  "",
                    "co":  "276",
                    "mail":  "test.first@test.com",
                    "l":  "Frankfurt",
                    "telephoneNumber":  "",
                    "c":  "DE",
                    "physicalDeliveryOfficeName":  "",
                    "sn":  "Test"
                },
    "userPrincipalName":  "tfi@test.com",
    "GivenName":  "First",
    "Name":  "Test First",
    "Identity":  "tfi",
    "StreetAddress":  "Am ",
    "Title":  " ",
    "Department":  "Team Z",
    "DisplayName":  "Test First"
}
ERROR: SetADUser : A parameter cannot be found that matches parameter name 'Name'.
SQL-AD-O365-LF.ps1 (272, 19): ERROR: At Line: 272 char: 19
ERROR: + $usersToProcess | SetADUser
ERROR: +                   ~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidArgument: (:) [Write-Error], ParameterBindingException
ERROR:     + FullyQualifiedErrorId : NamedParameterNotFound,SetADUser
ERROR:

Good stuff, I realised on the way here that there's a bug in my attempt to prevent blank values sneaking in.  This attempts to fix that again.

#Requires -Module ActiveDirectory

function Invoke-SqlQuery {
    param(
        [String]$Query,

        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.PSObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

function NewADUser {
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String]$Path = "OU=TestUser,OU=test,DC=test,DC=com"
    )

    process {
        if ($UserInformation.Action -eq 'create') {
            $params = @{
                Path = $Path
                AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
            }
            $otherAttributes = @{}
            $command = Get-Command New-ADUser

            # Create a list of properties (from UserInformation) which will be written to the new user
            $propertiesToSet = $UserInformation.PSObject.Properties | 
                Where-Object { 
                    $_.Name -notin 'Action', 'Password', 'Status' -and 
                    $null -ne $_.Value -and
                    ($Value -isnot [String] -or $Value.Trim() -ne '')
                }


            # Use as many named parameters as possible
            foreach ($property in $propertiesToSet) {
                if ($command.Parameters.ContainsKey($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $otherAttributes.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($otherAttributes.Count -gt 0) {
                $params.OtherAttributes = $otherAttributes
            }
            if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                $params.Name = $UserInformation.DisplayName
            }

            Write-Host "Creating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            try {
                New-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Create failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function SetADUser {
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    process {
        if ($UserInformation.Action -eq 'update') {
            $params = @{
                Identity = $_.SamAccountName
            }
            $replace = @{}
            $command = Get-Command Set-ADUser

            # Create a list of properties (from UserInformation) which will be set on the existing user
            $propertiesToSet = $UserInformation.PSObject.Properties | 
                Where-Object { 
                    $_.Name -notin 'Action', 'Password', 'Status', 'SamAccountName' -and 
                    $null -ne $_.Value -and
                    ($Value -isnot [String] -or $Value.Trim() -ne '')
                }

            # Use as many named parameters as possible
            foreach ($property in $propertiesToSet) {
                if ($command.Parameters.ContainsKey($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $replace.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($replace.Count -gt 0) {
                $params.Replace = $replace
            }
            if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                $params.Name = $UserInformation.DisplayName
            }

            Write-Host "Updating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            try {
                Set-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Set failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function RemoveAction {
    param(
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    begin {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()
    }

    process {
        if ($UserInformation.Status -eq 'OK') {
            try {
                $SqlCommand = $SqlConnection.CreateCommand()
                $SqlCommand.CommandText = "UPDATE dbo.GetActiveDirectoryUsers SET Action = ' ' WHERE SamAccountName = '{0}';" -f $UserInformation.SamAccountName
                $SqlCommand.ExecuteNonQuery()
            } catch {
                Write-Error -ErrorRecord $_
            }
        }
    }

    end {
        $SqlConnection.Close()
    }
}

#
# Main
#

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

# A status field is added to each entry. This is used to persistently record the result of our commands.
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
    Select-Object *, Status

# Update any fields which need modification
$usersToProcess | ForEach-Object {
    $_.Enabled = (Get-Variable $_.Enabled).Value
}

# Commit the changes to AD
# NewADUser internally filters usersToProcess on Action = Create
$usersToProcess | NewADUser
# SetADUser internally filters usersToProcess on Action = Update
$usersToProcess | SetADUser

# Update database
# RemoveAction internally filters usersToProcess on Status = OK
$usersToProcess | RemoveAction

Select allOpen in new window

sorry Chris,
ERROR: NewADUser : The server is unwilling to process the request
SQL-AD-O365-LF.ps1 (280, 19): ERROR: At Line: 280 char: 19
ERROR: + $usersToProcess | NewADUser
ERROR: +                   ~~~~~~~~~
ERROR:     + CategoryInfo          : NotSpecified: (CN=Test Second,...dpartner,DC=com:String) [Write-Error], ADException
ERROR:     + FullyQualifiedErrorId : ActiveDirectoryServer:0,NewADUser
ERROR:

for set-aduser

ERROR: SetADUser : A parameter cannot be found that matches parameter name 'Name'.
SQL-AD-O365-LF.ps1 (282, 19): ERROR: At Line: 282 char: 19
ERROR: + $usersToProcess | SetADUser
ERROR: +                   ~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidArgument: (:) [Write-Error], ParameterBindingException
ERROR:     + FullyQualifiedErrorId : NamedParameterNotFound,SetADUser
ERROR:

Cool, looking better. I wonder where it found the Name parameter... odd, that was the point in dynamically binding. One tick, let me install the AD module.

Awesome!!!

The next iteration. Parameter discovery is a little more targeted now. We're still dynamically discovering and binding, but I've limited the scope to individual parameter sets (as I should have done in the first place).

I've removed the erroneous Name parameter I was accidentally passing to Set-ADUser.

I've added brief help to each of the commands to describe the intent of each.

I don't expect this to resolve the Server is Unwilling error, but please can you paste the information it says it's pushing, need to see how close this is getting.

#Requires -Module ActiveDirectory

# Utility functions

function Get-CommandParameter {
    # .SYNOPSIS
    #   Get a set of parameters from a specific parameter set.
    # .DESCRIPTION
    #   Get a set of parameters from a specific parameter set.
    # .INPUTS
    #   System.String
    # .OUTPUTS
    #   System.Management.Automation.ParameterMetadata
    # .EXAMPLE
    #   Get-CommandParameter Set-ADUser -ParameterSetName Identity
    
    [OutputType([System.Management.Automation.ParameterMetadata])]
    param(
        # Retrieve parameters for the specified command.
        [Parameter(Mandatory = $true)]
        [String]$CommandName,

        # Limit results to parameters from the specified parameter set.
        [String]$ParameterSetName = '__AllParameterSets',

        # Return the results as a hashtable, using the parameter name as a key.
        [Switch]$AsHashtable
    )

    try {
        $hashtable = @{}

        $command = Get-Command -Name $CommandName -ErrorAction Stop
        $command.Parameters.Values | 
            Where-Object { $_.ParameterSets.Keys -contains $ParameterSetName } |
            ForEach-Object {
                if ($AsHashtable) {
                    $hashtable.Add($_.Name, $_)
                } else {
                    $_
                }
            }

        if ($AsHashtable) {
            $hashtable
        }
    } catch {
        throw
    }
}

# Generic functions

function Invoke-SqlQuery {
    # .SYNOPSIS
    #   Invoke an SQL query.
    # .DESCRIPTION
    #   Invoke an SQL query against a server described by a connection string.
    # .INPUTS
    #   System.String
    # .OUTPUTS
    #   System.Management.Automation.PSObject
    # .EXAMPLE
    #   Invoke-SqlQuery -Query "SELECT * FROM Table" -ConnectionString "Server=server\instance;Database=db;Trusted_Connection=yes;"

    [OutputType([System.Management.Automation.PSObject])]
    param(
        # An SQL query to execute.
        [Parameter(Mandatory = $true)]
        [String]$Query,

        # The connection string to use. A connection will be created prior to executing the query, then removed afterwards.
        [Parameter(Mandatory = $true)]
        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.PSObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

# Process functions

function NewADUser {
    # .SYNOPSIS
    #   Implements the create action.
    # .DESCRIPTION
    #   NewADUser dynamically binds parameters for the New-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String]$Path = "OU=TestUser,OU=test,DC=test,DC=com"
    )

    process {
        if ($UserInformation.Action -eq 'create') {
            $params = @{
                Path            = $Path
                AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
            }
            $otherAttributes = @{}

            # Create a list of properties (from UserInformation) which will be written to the new user
            $propertiesToSet = $UserInformation.PSObject.Properties | 
                Where-Object { 
                    $_.Name -notin 'Action', 'Password', 'Status' -and 
                    $null -ne $_.Value -and
                    ($Value -isnot [String] -or $Value.Trim() -ne '')
                }

            try {
                $validParameters = Get-CommandParameter New-ADUser -AsHashtable

                # Use as many named parameters as possible
                foreach ($property in $propertiesToSet) {
                    if ($validParameters.Contains($property.Name)) {
                        $params.($property.Name) = $property.Value
                    } else {
                        $otherAttributes.Add($property.Name, $property.Value)
                    }
                }
                
                # Load everything else into OtherAttributes
                if ($otherAttributes.Count -gt 0) {
                    $params.OtherAttributes = $otherAttributes
                }
                if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                    $params.Name = $UserInformation.DisplayName
                }

                Write-Host "Creating user with the following information"
                Write-Host ($params | ConvertTo-Json -Depth 3)

                New-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Create failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function SetADUser {
    # .SYNOPSIS
    #   Implements the update action.
    # .DESCRIPTION
    #   SetADUser dynamically binds parameters for the Set-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    process {
        $params = @{
            Identity = $_.SamAccountName
        }
        $replace = @{}

        # Create a list of properties (from UserInformation) which will be set on the existing user
        $propertiesToSet = $UserInformation.PSObject.Properties | 
            Where-Object {
                $_.Name -notin 'Action', 'Password', 'Status', 'SamAccountName' -and 
                $null -ne $_.Value -and
                ($Value -isnot [String] -or $Value.Trim() -ne '')
            }

        if ($UserInformation.Action -eq 'update') {
            $validParameters = Get-CommandParameter Set-ADUser -ParameterSetName Identity -AsHashtable

            # Use as many named parameters as possible
            foreach ($property in $propertiesToSet) {
                if ($validParameters.Contains($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $replace.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($replace.Count -gt 0) {
                $params.Replace = $replace
            }

            Write-Host "Updating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            try {
                Set-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Set failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function RemoveAction {
    # .SYNOPSIS
    #   Remove the action flag for every object in UserInformation where status is set to 'OK'
    param(
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    begin {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()
    }

    process {
        if ($UserInformation.Status -eq 'OK') {
            try {
                $SqlCommand = $SqlConnection.CreateCommand()
                $SqlCommand.CommandText = "UPDATE dbo.GetActiveDirectoryUsers SET Action = ' ' WHERE SamAccountName = '{0}';" -f $UserInformation.SamAccountName
                $SqlCommand.ExecuteNonQuery()
            } catch {
                Write-Error -ErrorRecord $_
            }
        }
    }

    end {
        $SqlConnection.Close()
    }
}

#
# Main
#

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

# A status field is added to each entry. This is used to persistently record the result of our commands.
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
    Select-Object *, @{Name = 'Status';Expression = 'NotProcessed'}

# Update any fields which need modification
$usersToProcess | ForEach-Object {
    $_.Enabled = (Get-Variable $_.Enabled).Value
}

# Commit the changes to AD
# NewADUser internally filters usersToProcess on Action = Create
$usersToProcess | NewADUser
# SetADUser internally filters usersToProcess on Action = Update
$usersToProcess | SetADUser

# Update database
# RemoveAction internally filters usersToProcess on Status = OK
$usersToProcess | RemoveAction

Select allOpen in new window

New-ADUser:

ERROR: NewADUser : The server is unwilling to process the request
SQL-AD-O365-LF.ps1 (377, 19): ERROR: At Line: 377 char: 19
ERROR: + $usersToProcess | NewADUser
ERROR: +                   ~~~~~~~~~
ERROR:     + CategoryInfo          : NotSpecified: (CN=Test Second,...,DC=com:String) [Write-Error], ADException
ERROR:     + FullyQualifiedErrorId : ActiveDirectoryServer:0,NewADUser
ERROR:

Set-ADUser:

ERROR: SetADUser : replace
SQL-AD-O365-LF.ps1 (379, 19): ERROR: At Line: 379 char: 19
ERROR: + $usersToProcess | SetADUser
ERROR: +                   ~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidOperation: (tfi:ADUser) [Write-Error], ADInvalidOperationException
ERROR:     + FullyQualifiedErrorId : ActiveDirectoryServer:0,SetADUser
ERROR:

>> Execution time: 00:00:01

Please can you post the informational blobs? Those that look like:

Updating user with the following information
{
    "postalcode":  "60311",

Select allOpen in new window

New-ADUser

Creating user with the following information
{
    "AccountPassword":  {
                            "Length":  10
                        },
    "userPrincipalName":  "tse@test.com",
    "DisplayName":  "Test Second",
    "samAccountName":  "tse",
    "Name":  "Test Second",
    "Department":  "Team Z",
    "Title":  " ",
    "GivenName":  "Test",
    "OtherAttributes":  {
                            "st":  "Zürich",
                            "mobile":  "",
                            "co":  "756",
                            "mail":  "test.second@test.com",
                            "l":  "Zürich",
                            "telephoneNumber":  "",
                            "c":  "CH",
                            "physicalDeliveryOfficeName":  "",
                            "sn":  "Second"
                        },
    "PostalCode":  "8001",
    "StreetAddress":  " 5",
    "Path":  "OU=TestUser,OU=test,DC=test,DC=com",
    "Enabled":  false
}

Why is DisplayName & Name same?

Dammit, it's still passing those empty values. I seriously thought I'd killed those.

It's the same because you previously had displayName pushing into the Name parameter:

New-ADUser -sAMAccountName $sAMAccountName -Name $DisplayName -Path $path -otherAttributes $otherAttributes -Enable $true -AccountPassword $password -PasswordNeverExpires $true 

Select allOpen in new window

Oh and you set PasswordNeverExpires. Let's see that too. One sec, going to take a longer look at those empty values.

I understand, but what I am wondering is why is it showing twice?
 "userPrincipalName":  "tse@test.com",
    "DisplayName":  "Test Second",
    "samAccountName":  "tse",
    "Name":  "Test Second",
    "Department":  "Team Z",
    "Title":  " ",
    "GivenName":  "Test",

Ahh it's the simple bugs that get you. Typo. Fixed.

Same again please (with user info again please :)).

#Requires -Module ActiveDirectory

# Utility functions

function Get-CommandParameter {
    # .SYNOPSIS
    #   Get a set of parameters from a specific parameter set.
    # .DESCRIPTION
    #   Get a set of parameters from a specific parameter set.
    # .INPUTS
    #   System.String
    # .OUTPUTS
    #   System.Management.Automation.ParameterMetadata
    # .EXAMPLE
    #   Get-CommandParameter Set-ADUser -ParameterSetName Identity
    
    [OutputType([System.Management.Automation.ParameterMetadata])]
    param(
        # Retrieve parameters for the specified command.
        [Parameter(Mandatory = $true)]
        [String]$CommandName,

        # Limit results to parameters from the specified parameter set.
        [String]$ParameterSetName = '__AllParameterSets',

        # Return the results as a hashtable, using the parameter name as a key.
        [Switch]$AsHashtable
    )

    try {
        $hashtable = @{}

        $command = Get-Command -Name $CommandName -ErrorAction Stop
        $command.Parameters.Values | 
            Where-Object { $_.ParameterSets.Keys -contains $ParameterSetName } |
            ForEach-Object {
                if ($AsHashtable) {
                    $hashtable.Add($_.Name, $_)
                } else {
                    $_
                }
            }

        if ($AsHashtable) {
            $hashtable
        }
    } catch {
        throw
    }
}

# Generic functions

function Invoke-SqlQuery {
    # .SYNOPSIS
    #   Invoke an SQL query.
    # .DESCRIPTION
    #   Invoke an SQL query against a server described by a connection string.
    # .INPUTS
    #   System.String
    # .OUTPUTS
    #   System.Management.Automation.PSObject
    # .EXAMPLE
    #   Invoke-SqlQuery -Query "SELECT * FROM Table" -ConnectionString "Server=server\instance;Database=db;Trusted_Connection=yes;"

    [OutputType([System.Management.Automation.PSObject])]
    param(
        # An SQL query to execute.
        [Parameter(Mandatory = $true)]
        [String]$Query,

        # The connection string to use. A connection will be created prior to executing the query, then removed afterwards.
        [Parameter(Mandatory = $true)]
        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.PSObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

# Process functions

function NewADUser {
    # .SYNOPSIS
    #   Implements the create action.
    # .DESCRIPTION
    #   NewADUser dynamically binds parameters for the New-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String]$Path = "OU=TestUser,OU=test,DC=test,DC=com"
    )

    process {
        if ($UserInformation.Action -eq 'create') {
            $params = @{
                Path            = $Path
                AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
            }
            $otherAttributes = @{}

            # Create a list of properties (from UserInformation) which will be written to the new user
            $propertiesToSet = $UserInformation.PSObject.Properties | 
                Where-Object { 
                    $_.Name -notin 'Action', 'Password', 'Status' -and 
                    $null -ne $_.Value -and
                    ($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
                }

            try {
                $validParameters = Get-CommandParameter New-ADUser -AsHashtable

                # Use as many named parameters as possible
                foreach ($property in $propertiesToSet) {
                    if ($validParameters.Contains($property.Name)) {
                        $params.($property.Name) = $property.Value
                    } else {
                        $otherAttributes.Add($property.Name, $property.Value)
                    }
                }
                
                # Load everything else into OtherAttributes
                if ($otherAttributes.Count -gt 0) {
                    $params.OtherAttributes = $otherAttributes
                }
                if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                    $params.Name = $UserInformation.DisplayName
                }

                Write-Host "Creating user with the following information"
                Write-Host ($params | ConvertTo-Json -Depth 3)

                New-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Create failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function SetADUser {
    # .SYNOPSIS
    #   Implements the update action.
    # .DESCRIPTION
    #   SetADUser dynamically binds parameters for the Set-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    process {
        $params = @{
            Identity = $_.SamAccountName
        }
        $replace = @{}

        # Create a list of properties (from UserInformation) which will be set on the existing user
        $propertiesToSet = $UserInformation.PSObject.Properties | 
            Where-Object {
                $_.Name -notin 'Action', 'Password', 'Status', 'SamAccountName' -and 
                $null -ne $_.Value -and
                ($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
            }

        if ($UserInformation.Action -eq 'update') {
            $validParameters = Get-CommandParameter Set-ADUser -ParameterSetName Identity -AsHashtable

            # Use as many named parameters as possible
            foreach ($property in $propertiesToSet) {
                if ($validParameters.Contains($property.Name)) {
                    $params.($property.Name) = $property.Value
                } else {
                    $replace.Add($property.Name, $property.Value)
                }
            }
            
            # Load everything else into OtherAttributes
            if ($replace.Count -gt 0) {
                $params.Replace = $replace
            }

            Write-Host "Updating user with the following information"
            Write-Host ($params | ConvertTo-Json -Depth 3)

            try {
                Set-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
            } catch {
                $UserInformation.Status = 'Set failed ({0})' -f $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function RemoveAction {
    # .SYNOPSIS
    #   Remove the action flag for every object in UserInformation where status is set to 'OK'
    param(
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    begin {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()
    }

    process {
        if ($UserInformation.Status -eq 'OK') {
            try {
                $SqlCommand = $SqlConnection.CreateCommand()
                $SqlCommand.CommandText = "UPDATE dbo.GetActiveDirectoryUsers SET Action = ' ' WHERE SamAccountName = '{0}';" -f $UserInformation.SamAccountName
                $SqlCommand.ExecuteNonQuery()
            } catch {
                Write-Error -ErrorRecord $_
            }
        }
    }

    end {
        $SqlConnection.Close()
    }
}

#
# Main
#

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"

# A status field is added to each entry. This is used to persistently record the result of our commands.
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
    Select-Object *, @{Name = 'Status';Expression = 'NotProcessed'}

# Update any fields which need modification
$usersToProcess | ForEach-Object {
    $_.Enabled = (Get-Variable $_.Enabled).Value
}

# Commit the changes to AD
# NewADUser internally filters usersToProcess on Action = Create
$usersToProcess | NewADUser
# SetADUser internally filters usersToProcess on Action = Update
$usersToProcess | SetADUser

# Update database
# RemoveAction internally filters usersToProcess on Status = OK
$usersToProcess | RemoveAction

Select allOpen in new window

> I understand, but what I am wondering is why is it showing twice?

Because Name and DisplayName are two completely different attributes.

BAM!!!

NICE!!!
All accounts made, with correct Attributes, perfectly enabled & disabled & SQL column also updated!!!

Excellent, long road, but hopefully not too dispiriting for you :)

Oh I forgot... Password Never Expires. Because it's a parameter for both New-ADUser and Set-ADUser, all we have to do is add the value to the usersToProcess collections after it's come out of SQL. The commands we have will take care of figuring out how to inject the value.

$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
    Select-Object *,
                  @{Name = 'PasswordNeverExpires'; Expression = $true },
                  @{Name = 'Status'; Expression = 'NotProcessed'}

Select allOpen in new window

Both New and Set will take it from there without further changes.

No Man, you have been very kind.
Now, 2 things left.
1) incorporating email notification on task completion , failure or success
2) creating the same accounts for O365

I had in earlier script email notification enabled with :
Send-MailMessage -To "abc@test.com" -SmtpServer "smtp.office365.com" -Credential $mycreds -UseSsl "User Creation Task - Successful" -Port "587" -Body "This is an automatically generated message.<br> The following Users have been created! <br> $($DisplayName) created! <br>Best regards<br><b>Your Support Bot</b>" -From "it@test.com" -BodyAsHtml

this was in the if statement on success

& 
                  Send-MailMessage -To "abc@test.com" -SmtpServer "smtp.office365.com" -Credential $mycreds -UseSsl "User Creation Task - Failed" -Port "587" -Body "This is an automatically generated message.<br> The following Users failed to create <br> $($DisplayName) failed to create, Error message: $($_.Exception.Message) <br>Best regards<br><b>Your Support Bot</b>" -From "it@test.com" -BodyAsHtml

on failure.

I have the credentials setup correctly, can you advise where to embed the same in new ?

So I entered this:

# A status field is added to each entry. This is used to persistently record the result of our commands.
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
Select-Object *, @{ Name = 'PasswordNeverExpires'; Expression = $true },
                    @{ Name = 'Status'; Expression = 'NotProcessed' }
# Update any fields which need modification


& got this error:

ERROR: Select-Object : Key "Expression" has illegal type System.Boolean; expected types are {System.String, System.Management.Automation.ScriptBlock}.
SQL-AD-O365-LF.ps1 (371, 1): ERROR: At Line: 371 char: 1
ERROR: + Select-Object *, @{ Name = 'PasswordNeverExpires'; Expression = $true },
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidArgument: (:) [Select-Object], NotSupportedException
ERROR:     + FullyQualifiedErrorId : DictionaryKeyIllegalValue1,Microsoft.PowerShell.Commands.SelectObjectCommand
ERROR: Get-Variable : Cannot validate argument on parameter 'Name'. The argument is null. Supply a non-null argument and try the command again.

Sorry, sorry. Very silly of me.

$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
    Select-Object *,
                  @{Name = 'PasswordNeverExpires'; Expression = { $true }},
                  @{Name = 'Status'; Expression = 'NotProcessed' }

Select allOpen in new window

Notifications. At this point we have a chance that some might work, and some might fail. I suggest that these are reported at the same time, but that the difference is highlighted. Let me put a sample together and you can see what you think, I just need to dig out the code I've used in the past for pretty reporting.

Ha Ha Ha,
I'm so glad you make mistakes too ;)

Works perfect!!

Can you please help me on Mail?

No rush, Thanks!
Chris, is it possible to invoke-pssession for O365 in the same script?
I want to use the same variables from SQL to create & make changes in O365 accounts....

Yes, absolutely. I can't advise to a great extent on the commands, no access to them / never used O365. In general terms it'll be another bolt-on step after both New and Set have had a turn.

It'll need a little control as well, Jason was going for domino affects earlier and until now we haven't had the potential. We do now though, because if New-ADUser fails it might not be prudent to try and do things with that user in O365.

Happily, there's a persistent Status field now, so we should only act if Status -eq 'OK'. Basically, it won't be a problem :)

I have the command for O365,
Just not sure how to invoke connection with SQL, but as you said, we'll manage!!!

Chris,
Did you get time to look for Notification?

Not yet, sorry. Bit busy today. I should have time later though, I haven't forgotten :)

NO worries, just getting excited to finish 1st part....

I tried to incorporate the O365 in it, but I guess it's not that simple as I thought....
Once this get's done, I will look at it. I'm sure will need help there too....

it creates the csv, but no email?
the SMTP server needs the password, where do I define it?
Is it possible not to create csv & send the mail directly?

Thanks!

This is the error:

ERROR: Send-MailMessage : The operation has timed out.
SQL-AD-O365-LF.ps1 (533, 2): ERROR: At Line: 533 char: 2
ERROR: +     Send-MailMessage @params
ERROR: +     ~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidOperation: (System.Net.Mail.SmtpClient:SmtpClient) [Send-MailMessage], SmtpException
ERROR:     + FullyQualifiedErrorId : SmtpException,Microsoft.PowerShell.Commands.SendMailMessage
ERROR:


The SMTP server is 'smtp.office365.com'

what worked for me was in my earlier email notification, i had declared the Credentials.
 -SmtpServer "smtp.office365.com" -Credential $mycreds -UseSsl  -Port "587"

It seems O365 SMTP server needs these...

Any thoughts?

What you you mean create the CSV and send the mail directly? We're doing that, the other function just deals with the pretty content of the email.

To add those parameters:

    $params = @{
        To          = $To
        From        = $From
        Subject     = $Subject
        Body        = $Body
        BodyAsHtml  = $true
        Attachments = '.\UserInformation.csv'
        SmtpServer  = "smtp.office365.com"
        Port        = 587
        Credential  = $Credential
    }
    Send-MailMessage @params

Select allOpen in new window

Now this does rather lead onto credential storage because you've got to fill that credential variable.

We can create a Credential like this:

$Credential = New-Object PSCredential($username, $securePassword)

Select allOpen in new window

You've created a secure string ($securePassword) before, it's nothing new. If you don't happen to have a password vault system I suggest you encrypt the credentials under the account running the script. Would you like instructions?

Sure
Thanks

I meant not to create CSV , but send the content in mail body
That way it can be seen directly on the mobile device too

It has both body (subset of fields) and attachment (everything) :)

Sorry
Since it didn't work, couldn't know

Password handling.

So, if you're going to include a password in the script for the SMTP connection you get a couple of options.

1. Plain Text
2. Encrypted using built-in stuff

To encrypt using built in stuff you effectively lock decryption of the password to a single user and single computer. That means we must run encryption under the same user on the same computer to give us valid cipher text.

'tis simple and one-time to create the encrypted password. Drop it a a file or something:

'PlainTextPassword' | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString

Select allOpen in new window

And to consume it at runtime:

Get-Content pwd.txt | ConvertTo-SecureString

Select allOpen in new window

All that's left is to make that into a Credential:

$Credential = New-Object PSCredential('TheUser', (Get-Content pwd.txt | ConvertTo-SecureString))

Select allOpen in new window

No one else can decrypt the password (well, without breaking the encryption scheme / brute force, that kind of thing).

Awesome Chris!
Works perfect!!!

Thanks Chris, Again!