Returning System.Object[] from Active Directory SQL Query

Question

I am working on creating a SSIS package that needs to access Active Directory. I have created an ADO.NET connection and wrote a select LDAP query to pull back the records (see below). I am able to retrieve the data successfully, but one of the Active Directory attributes is returning System.Object[] instead of the department number I desire. Is there a way to successfully convert the System.Object into the real data using the query? Thanks in advance.

Example query:

SELECT department, departmentnumber, sAMAccountName
FROM 'LDAP://example.com' WHERE objectClass='User'

Results:

|department |departmentnumber   |sAMAccountName|
|Hollywood  |System.Object[]    |ceastwood     |

UPDATE: After my research I will add that I don't believe this LDAP SQL query is possible to use if it's returning System.Object. I found many great examples of using a script task to perform a similar operation like this one HERE

I created a console application and built it up to insert my Active Directory data right into SQL. I would recommend following along to the articles found on Stack Over Flow. They helped me a great deal to accomplish this task, even though it wasn't the way I wanted.

If you use something like [SysInternals ADExplorer](https://learn.microsoft.com/en-us/sysinternals/downloads/adexplorer), what does it show for the Syntax of `departmentnumber`? — billinkc, Dec 07 '17 at 22:37
Using the above tool (thanks for that), while navigating to the CN=departmentNumber, it has several attributes all with different Syntax. Which attribute should I be looking for? The attributeSyntax Attribute Syntax = DirectoryString — Clint, Dec 08 '17 at 15:35
For what it's worth.. inside the Data Flow ADO NET Source, when I preview the query, it show the System.object at that level, so that rules out the data flow destination not causing the issue. — Clint, Dec 08 '17 at 15:59

score 1 · Answer 1 · answered Dec 11 '17 at 10:42

1

Before reading this further, please ensure that the object[] can be parsed as string[].

I'll share a sample way of how I'm converting the output of memberOf attribute from object[] to string[] for readability purpose in our environment:

object[] membership = user.Properties["MemberOf"].Value as object[];
                    if (membership != null)
                    {
                        string[] roles = Array.ConvertAll<object, string>(membership, convertObjectToString);
                        if (roles != null)
                        {
                            foreach (string role in roles)
                                LogInformation.log("Role " + role);
                        }
                        else
                            LogInformation.log("Member Of not found!");
                    }

Whereas, convertObjectToString() is a static method as declared below:

private static string convertObjectToString(object obj)
    {
        return (obj == null) ? string.Empty : obj.ToString(); 
    }

answered Dec 11 '17 at 10:42

Am_I_Helpful

18,735
7
49
73

I was trying to perform this using a sql query. Is the only way to successfully do this by creating a script task? – Clint Dec 12 '17 at 15:19
@Clint - yes, it is possible to achieve what you want. Search on the web for "Calling managed code from SQL-Server", e.g., https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/introduction-to-sql-server-clr-integration ; remember - it'd be equivalent to fight a war using a needle! – Am_I_Helpful Dec 12 '17 at 15:24
Haha, yes I agree. I have began writing some C# to perform this in a script task, but it bothers me that I can't get this to work in a SQL query. Thanks for the link. – Clint Dec 12 '17 at 15:34
@Clint - You may like to accept the answer as well, if it helped you. See how to accept an answer -> https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – Am_I_Helpful Jul 19 '18 at 05:44

Returning System.Object[] from Active Directory SQL Query

1 Answers1