New-Aduser from SQL

Sorry Chris,
But what do you mean I haven't assigned the value?
It's the same as other variables...

Thanks,
I did it like this:
# 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
      $DisplayName = $dr.GetValue($DisplayNameOrdinal)

& seems to work, but how do I get to country?
Sorry

Yes,  I have double checked it...

Can you please have a look at the code & see where I messed up....

<#	
	
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+'@test.com' as userPrincipalName,
				 PostalCode as postalcode,
				 MobilePhone as mobile,
				 OfficePhone as telephoneNumber,
				 Department as department,
				 Title as Title,
				 Office as physicalDeliveryOfficeName,
				 Country as Country,
				 'Abc-123456' as Password
from GetActiveDirectoryUsers where Action = 'yes' "

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

$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")
$CountryOrdinal = $dr.GetOrdinal("Country")

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
	$DisplayName = $dr.GetValue($DisplayNameOrdinal)
	$Country = $dr.GetValue($Country)
	
	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
			default
			{
				$otherAttributes.Add($attribute, $dr.GetValue($i))
			}
		}
	}
	# Create Active Directory User Account
	

	try
	{
		
		New-ADUser -sAMAccountName $sAMAccountName -Name $DisplayName -Path $path -otherAttributes $otherAttributes -Enable $true -AccountPassword $password -ChangePasswordAtLogon $true -Country $Country
		
		Write-Host "UserID $($DisplayName) created!"
	}
	catch
	{
		Write-Host "There was a problem creating UserID $($DisplayName). The account was not created!"
	}
}

$dr.Close()
$cn.Close()

Select all Open in new window

Thanks for that, now it shows:

There was a problem creating UserID Test Now. The account was not created!
Error message: The specified directory service attribute or value does not exist
Parameter name: Country

Can you please see what am I missing?

So I made these changes:

for ($i = 0; $i -le $colCount - 1; $i++)
      {
            $attribute = $dr.GetName($i)
      <#      
            switch ($attribute)
            {
                  "Password"{ } #Ignore
                  "SAMAccountName" { } #Ignore
                  default
                  {
                        $otherAttributes.Add($attribute, $dr.GetValue($i))
                  }
            } #>
            
            if ($attribute -notin 'Password', 'SamAccountName', 'Country')
            {
                  $otherAttributes.Add($attribute, $dr.GetValue($i))
            }
      }
      # Create Active Directory User Account
      

& I got error:

There was a problem creating UserID Test Now. The account was not created!
Error message: A value for the attribute was not in the acceptable range of values

I tried that, it accepts
 c as c,
CountryCode as co,

but still doesn't take Country???

The problem is that if i query aduser in powershell it shows it has accepted the 2.
If I give country explicitly then it shows in GUI, but when I pass country in other attributes, it doesn't accept?

c is 2 character code, which works fine, but Country is the real name & doesn't accept

i understand, let me try for a while, but as for the question asked, you have answered it.
Thanks.

Chris,
Is it possible that after the accounts are created, the script sets the value of Column "Action" to blank instead of Yes?
If you see in the code I posted above, I am pulling the values for entries where Action is Yes..

from GetActiveDirectoryUsers where Action = 'yes' "

I want this to be at the end of the script, so that next time I don't have to worry about already created accounts.

Thanks!

That would be just Awesome if you could have a look!!!

Here is the complete code as of now, it does create a user, I added some code at the bottom to set the value for Action to be blank, but I get an error "There is already an open DataReader associated with this command"

I'm marking the Question Answered, however, if you could help me on this, I would be Really Grateful!
Regards,
A

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+'@test.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,
						
				 'Abc-123456' as Password
from GetActiveDirectoryUsers where Action = 'yes' "
#Country as co,	
###########################

$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")


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
	$DisplayName = $dr.GetValue($DisplayNameOrdinal)
#	$Country = $dr.GetValue($Country)
	
	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
			default
			{
				$otherAttributes.Add($attribute, $dr.GetValue($i))
			}
		}
		
	}
	# Create Active Directory User Account
	try
	{
		
		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)"
	}
	
	$updateqry = "update dbo.GetActiveDirectoryUsers set Action = ' ' where Action = 'Yes';"
	$cmd.CommandText = $updateqry
	$dr = $cmd.ExecuteNonQuery()
	
}


$dr.Close()
$cn.Close()

Select all Open in new window

I managed it.