I wanted to gather Active Directory data into SQL Server table to analyze permissions on critical data. I wanted to use a SQL Server Agent Job that would refresh a couple of time per day. I also wanted it to be as clean and portable as possible, so I tried to run everything within the server itself and not rely on external resources which tend to complicate matters. This task seems pretty straight forward, however due to limitations in the OLE DB driver for Microsoft Directory Services and limitations in Powershell it has become quite a chore. The first problem I ran into was trying to bring in "description" from AD using a linked server. The query returned the error: Cannot get the data of the row from the OLE DB provider "ADSDSOObject" for linked server "ADSI". Could not convert the data value due to reasons other than sign mismatch or overflow.
Upon further examination I found that the "description" field in AD is a "multi value" field and the OLE DB provider doesn't know what to do with it so it errors. I then turned to Powershell which has been held up as the future of Microsoft product integration. Though it's a powerful tool, and did exactly what it was supposed to while running in a Command Prompt, once I put it into a SQL Job it threw a syntax error because the job engine could not parse the following line of code: $Command.CommandText = "INSERT INTO Test(Name, Title, Phone, description) Values('$($Name)', '$($Title)', '$($Phone)', '$($description)')"
I guess I will have to create an external .PS1 file and run it as an Operating System (CMDExec) file unless someone has a better suggestion.