I've been working with Dan, who handles our ADS administration, on extracting data from our employee tables, which are fed from an extract from our HR system, to populate the contact information in ADS from the HR data directly. This allows us to disable accounts in AD when the employees have been terminated and create accounts for new employees automatically. PowerShell is the ideal tool for this task, and Dan figured out how to populate the ADS data from a spreadsheet extracted from the database table containing the required data, but didn't know how to query the database directly. I spent a bit of time polling the web for the "best" way to do this, and here's the script I sent him:
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=MyServer;Integrated Security=SSPI;Initial Catalog=AdventureWorks");
$ds = new-object "System.Data.DataSet" "dsPersonData"
$q = "SELECT [ContactID]"
$q = $q + " ,[FirstName]"
$q = $q + " ,[LastName]"
$q = $q + " ,[EmailAddress]"
$q = $q + " ,[Phone]"
$q = $q + " FROM [AdventureWorks].[Person].[Contact]"
$da = new-object "System.Data.SqlClient.SqlDataAdapter" ($q, $cn)
$da.Fill($ds)
$dtPerson = new-object "System.Data.DataTable" "dtPersonData"
$dtPerson = $ds.Tables[0]
$dtPerson | FOREACH-OBJECT { " " + $_.ContactID + ": " + $_.FirstName + ", " + $_.LastName + ", " + $_.EmailAddress + ", " + $_.Phone }
Of course, it's been modified to extract data from AdventureWorks, and I built a stored procedure to return the data he wanted rather than code the query directly in the script, but I just wanted to share how easy it is to pull data directly from a database table in PowerShell. Dan can now pipe the resultset directly into the ADS database, and when our coworkers use Outlook they can view properties for anyone in our organization and see their address, phone number, etc.
In another post I'll demonstrate how to use PowerShell to manage servers via SMO.
Allen