Retrieving Active Directory information

• Pete Bishop

  Default port

  Points: 1485

  More actions

  November 23, 2022 at 7:29 pm

  #4117217

  I have a requirement to process AD information through an SSIS package and have hit a problem with the "objectClass" attribute. The ADO.NET source is configured as:

  

  and the package has converted all of the output data types to NTEXT. This isn't an issue for most of them as I've converted them to DT_STR using a Derived Column task. However, the "objectClass" is coming through to my SQL table as "System.Object[]".

  I've tried feeding the column into a Script Task and treating it as a BlobColumn and converting through:

  var blobLength = Convert.ToInt32(Row.objectClass.Length);

  var blobData = Row.objectClass.GetBlobData(0, blobLength);

  var stringData = System.Text.Encoding.Unicode.GetString(blobData);

  and all I end up with is "System.Object[]".

  Can anyone advise what I'm doing wrong? I *suspect* it's an issue with the way the data is being pulled (I'm just expecting "user" or "group" as I do when I run a similar query from PowerShell) but I can't see what change I can make.

• Ant-Green

  SSC Guru

  Points: 113544

  More actions

  November 24, 2022 at 10:19 am

  #4117522

  Do you need to do this via ADO / LDAP queries?

  My preferred method would be to combine the AD PowerShell module with DBATools and then use things like Get-ADUser from the AD module with ConvertTo-DbaDataTable then Write-DbaDataTable from DBATools to push it to the DB, but you can then still call the PowerShell from within SSIS via a execute process task to call pwsh.exe and pass it the ps1 script file.