2

I'm using a SSIS package to retrieve Data from active directory and to make a few conversions and write it into a SQL Server destination table (via update ole db command).

Now I've run into the problem that one of the fields retrieved is of type System.Byte[] and it is the TYPE that is written into the field of the sql server database (thus the field UserSID gets the value "System.Byte[]" instead of the user SID itself. the userSID is a unicode varcahr with a max. length of 255).

ole db source sql command:

SELECT extensionAttribute2, mail, objectSID FROM 'LDAP://XXXXXXX' WHERE objectClass='User'

So my question here is: how can I get the value and not the type back from the source object?

Lennart
  • 9,657
  • 16
  • 68
  • 84
Thomas
  • 2,886
  • 3
  • 34
  • 78
  • If I understand correctly, you are asking how to convert the string `"System.Byte[]"` into your actual data? It's not clear whether the data is already corrupt or you are just having trouble reading the byte array. – Moti Azu Dec 04 '14 at 09:12
  • that is my problem where I'm not sure about. the field objectSID should have the userSID inside and it by itself is of type System.Byte[]. The problem is that when I try to get that field I'm not getting the content but instead I'm getting the string "System.Byte[]" and that is unexpected. Thus I'm not sure if the data itself is the problem (don't believe it though else the AD users would have run into huge troubles already or if I do something wrong when I try to get that field) – Thomas Dec 04 '14 at 09:15
  • The string `"System.Byte[]"` is in your DB column or in your C# object? – Moti Azu Dec 04 '14 at 09:16
  • when I use dataviewer just after the source (or preview in the source) I see it as columnvalue for the objectSID value. In the database I see it as vlue in the UserSID column which is the destination column I write it to. (it = the String "System.Byte[]") – Thomas Dec 04 '14 at 09:18
  • If you don't do explicitly do something else, then when converting an `object` to a `string`, the object's `ToString` will be called. However the default implementation of `ToString` (inherited from `System.Object`) just return the type's name. You need to use a suitable conversion from `buye[]` (which is the underlying type for SIDs) into a user readable string: this should wrap a system method that will do the right formatting. – Richard Dec 04 '14 at 09:19
  • where can I do that conversion? (directly in the source itself if so how / where there?) – Thomas Dec 04 '14 at 09:20
  • Give me a chance to search :-): [`SecurityIdentifier.ToString`](http://msdn.microsoft.com/en-us/library/system.security.principal.securityidentifier.tostring%28v=vs.110%29.aspx). (It has a constructor that takes a byte array and offset.) – Richard Dec 04 '14 at 09:21
  • 1
    Or just convert a byte array to a string http://stackoverflow.com/questions/11654562/how-convert-byte-array-to-string – Moti Azu Dec 04 '14 at 09:22
  • where can I do that? (like mentioned the sql itself and thus the wrongly given back data is in an ADO.NET data source). Do I ahve to change the datasource then to a programmatical datasource instead of a "normal" source component? or can that be done somewhere in the source itself? – Thomas Dec 04 '14 at 09:24
  • Without the code you are using to read from the DB we're assuming the database has a `binary(n)` column but your reading as a string (thus ADO.NET is applying the default conversion). Read the columns value as a byte array (eg. `SqlDataReader.GetBytes`) and then convert yourself. – Richard Dec 04 '14 at 09:31
  • I think there is a misunderstanding there. I'm using a SSIS package to get the data and I'm using an ADO.NET datasource there (ssis from the sql server data tools). Thus normally I'm not able to use sqldatareader,... exceptit when I switch from an ADO.-NET source to a script source (which I would do rather not if possible as I know how delicate Ad can be and ssis so far made a good job there with handling it stably) – Thomas Dec 04 '14 at 09:35

0 Answers0