I hope this topic is appropriate for this forum. If not, I pre-apologize. I have an SSIS package that takes data from active directory and dumps it to a table. Here is the query I am using: SELECT displayName, Mail, Title, physicalDeliveryOfficeName, description, telephoneNumber, departmentFrom 'LDAP://ourserver' WHERE objectClass='user' AND objectCategory='Person' I am dumping it into a table on SQL Server where the columns are all of data type: NTEXT. All columns come across GREAT except the column DESCRIPTION. We keep a piece of information in that column that is important and without it the SSIS is worthless. What is coming across in that column is the following: System.Object[] No values are coming across. It should be character data up to 10 in length. Anyone see this or know of something that I can do to force the "real" data to come through? Thank you in advance for any consideration you give. James jamestow@yahoo.com

Are you suggesting to change the data type of the column to varchar(255)? I have tried different data types on the tables and when I do it generates data types errors. I have also tried converting the columns to a different data type by going to the Advanced Editor dialog, choosing the Input and Output Properties, and changing the data types there. Those don't work either as they generate errors. Unless the destination table on SQL Server has NTEXT columns defined and I keep the data type for the columns as they are in the Input and Output Properties, it generates errors. Again, the other columns come across fine. They are the same data types as the column that I am having problems with. I am confused. Thank you for your response.

Just as an aside, I did convert the column to NVARCHAR(50)by going to the Advanced Editor dialog, choosing the Input and Output Properties, and changing the data types there. It ran with no errors but I still got the value System.Object[] Is it possible that this is a Code Page issue? I am really lost because it works for the other columns that are the same data types.

Can you try using this query instead?: Code Snippet SELECT displayName, Mail, Title, physicalDeliveryOfficeName, convert(varchar(50),description) description, telephoneNumber, departmentFrom 'LDAP://ourserver' WHERE objectClass='user' AND objectCategory='Person'

For clarification,is the "System.Object[]" data introduced when you are loading the table from your SELECT statement or when you are extracting data from the table using SSIS? This isn't clear from your post.

It looks like thedescription field is being returned as an object - not sure why, I'm not an AD expert. You might try using a script component to check out the type of object: Code SnippetMsgBox(Row.DescriptionColumn.GetType().ToString()) That might give you enough info to get your data out of it.

The System.Object[] is in the SQL 2005 table after running the data flow in SSIS. The data flow has a connection to the LDAP data source (with the query mentioned above), and it copies the results of the query to a SQL Server Data Flow Component (direct table access). When I run the SSIS package it completes successfully. I go to SQL Server and open the table (or write SQL to view the data) and the column DESCRIPTION has System.Object[] as the data. All the other columns have the expected data. I tried the following from a previous post: convert(varchar(50),description) And it generated errors. Thank you for your input. James

I also use the same Active Directory query on a report using Reporting Services. When I go to that report on the Data Tab (where you can execute the query and view results) I run the query and get back values for all the fields EXCEPT the field DESCRIPTION. All values for that field are: <Binary> Is it possible that the field DESCRIPTION is corrupt in Active Directory? Thoughts?

James Tow wrote: Is it possible that the field DESCRIPTION is corrupt in Active Directory? It seems to me that the most likely reason for this is that the data sored in this field in AD is in a format that SSIS and SSRS don't natively know how to deal with. The "System.Object[]" string says "this is an array of .NET objects" to me and that SSIS is simply calling its .ToString() method to get this value. I do not know the syntax for querying AD and/or LDAP, but it seems likely that there should be a way to update the source query to return the text stored in the description objects instead of the objects themselves. Sadly, I don't know what this technique might be...

Based on this MSDN Entry: http://msdn.microsoft.com/en-us/library/ms190803.aspx You can add Active Directory as a linked server like such: EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'GO Then query it like such: CREATE VIEW viewADContacts ASSELECT [Name], SN [Last Name], ST StateFROM OPENQUERY( ADSI, 'SELECT Name, SN, ST FROM ''LDAP://ADSISrv/ OU=Sales,DC=sales,DC=adventure-works,DC=com'' WHERE objectCategory = ''Person'' AND objectClass = ''contact''')GOSELECT * FROM viewADContacts I did add the server, queried it, and it worked BUT when I add the column named DESCRIPTION I get this 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. So this problem is now consistently pissing me off.

I have the same problem. I can tell you that if you build a vbs script that creates an excel spreadsheet and import the values into excel all works fine. if anyone knows the solution to this let us know please.

To all: Found the solution to this problem. I found this article: http://www.serverwatch.com/tutorials/article.php/1476961. If you follow the script they have you will find this code aDescription = oRecordSet.Fields("description")If Not IsNull(aDescription) Then Response.Write aDescription(0) I am guesing that the description field is some kind of an array. As a test I ran the script in an aspx page and it worked.

I finally gave up and wrote a c# application that connects to Active Directory, loops through the records and writes the values to SQL Server. It seems that this is the only solution. Thank you again to all for your suggestions.

Having problems with the Description and GUID fields also. Could you share the c# code on how to connect to AD and extract the records?thanks in advanceNP

Hi, I've done this in ASPX, but wanted equivalent AD extract functionality to run as a scheduled job in SSIS, so the following has resolved it, adding only a few seconds to querying ~1,000 groups. After loading name, distinguishedName via ADO.NET component then converting the columns to unicode strings I then have a script component with the following code to extract the description and output it as a new column on each row. This same format could be used for other problem columns. Dim adPath As String = "LDAP://DC=<YOUR DOMAIN HERE>,DC=<YOUR SUBDOMAINS>,DC=<AND AGAIN>,DC=com" Dim dn As String = Row.UnicodedistinguishedName.ToString() Dim Group As New DirectoryEntry("LDAP://" & dn) Dim description As String = Group.Properties("description").Item(0).ToString() Row.Description = description This works fine and negates having to write executables/manual web pages etc.Justin B

Here is a step-by-step on how to pull data, including multi-value fields, from AD. Took me the better part of a week to piece this together, so I thought I'd share. http://dataqueen.unlimitedviz.com/2012/09/get-around-active-directory-paging-on-ssis-import/<o:p></o:p> If you also want to include multi-value attributes such as Description then add this to your C# code.<o:p></o:p> http://dataqueen.unlimitedviz.com/2012/09/how-to-query-multi-value-fields-in-active-directory-using-ssis/<o:p></o:p> Cheers, MartinaMartina White