Importing logon hours from active directory to SQL Server

I am trying to pull all the users from the Active Directory, I am able to pull all the records but when I read the data in the tables it displays as SYSTEM.BYTE[](for logonhours), need to convert before inserting that into SQL table.

I did a googling and found some solution like first converting that into byte then into a string and inserting it, where you can read the data, but I am not able to implement that.

Can anyone help me out?

I am inserting into SQL Server 2005 and importing all the data using SSIS ( since there is a limitation on ADSI when you query, you get only 1000 records I am using a custom script and setting a page default to more than 1000 to retrive all the records from active directory) below is my custom script how I am pulling my records from ADSI:

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Option Strict Off
Imports System
Imports System.Data
Imports System.DirectoryServices
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()

        Dim arrayDomains As String()
        ReDim arrayDomains(1)
        arrayDomains(0) = "LDAP://DC=cba,DC=acb,DC=com"
        arrayDomains(1) = "LDAP://DC=abc,DC=bca,DC=com"
        Dim Domain As String
        For Each Domain In arrayDomains

            Dim objSearch As New DirectorySearcher()
            objSearch.SearchRoot = New DirectoryEntry(Domain)
            objSearch.Filter = "(&(objectclass=user)(objectcategory=Person)(samAccountType=805306368))"
            '(|(&(!(groupType:1.2.840.113556.1.4.803:=1))(groupType:1.2.840.113556.1.4.804:=14))(samAccountType=805306368))
            objSearch.SearchScope = SearchScope.Subtree
            objSearch.PageSize = 999
            objSearch.ServerTimeLimit = New TimeSpan(0, 15, 0)
            objSearch.ClientTimeout = New TimeSpan(0, 30, 0)
            objSearch.PropertiesToLoad.Add("cn")
            objSearch.PropertiesToLoad.Add("c")
            objSearch.PropertiesToLoad.Add("department")
            objSearch.PropertiesToLoad.Add("displayName")
            objSearch.PropertiesToLoad.Add("distinguishedName")
            objSearch.PropertiesToLoad.Add("employeeID")
            objSearch.PropertiesToLoad.Add("extensionAttribute14")
            objSearch.PropertiesToLoad.Add("extensionAttribute2")
            objSearch.PropertiesToLoad.Add("givenName")
            objSearch.PropertiesToLoad.Add("l")
            objSearch.PropertiesToLoad.Add("lastLogon")
            objSearch.PropertiesToLoad.Add("logonHours")
            objSearch.PropertiesToLoad.Add("mail")
            objSearch.PropertiesToLoad.Add("manager")
            objSearch.PropertiesToLoad.Add("physicalDeliveryOfficeName")
            objSearch.PropertiesToLoad.Add("postalCode")
            objSearch.PropertiesToLoad.Add("pwdLastSet")
            objSearch.PropertiesToLoad.Add("sn")
            objSearch.PropertiesToLoad.Add("st")
            objSearch.PropertiesToLoad.Add("streetAddress")
            objSearch.PropertiesToLoad.Add("telephoneNumber")
            objSearch.PropertiesToLoad.Add("title")
            objSearch.PropertiesToLoad.Add("userAccountControl")
            objSearch.PropertiesToLoad.Add("whenCreated")

            Dim colQueryResults As SearchResultCollection
            col开发者_StackOverflowQueryResults = objSearch.FindAll()

            Dim objResult As SearchResult
            For Each objResult In colQueryResults
                'Console.WriteLine("3")
                '
                ' Add rows by calling AddRow method on member variable called "<Output Name>Buffer"
                ' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"
                '
                Output0Buffer.AddRow()
                If objResult.Properties.Contains("cn") AndAlso objResult.Properties("cn")(0) IsNot Nothing Then
                    Output0Buffer.cn = objResult.Properties("cn")(0).ToString()
                End If

                If objResult.Properties.Contains("c") AndAlso objResult.Properties("c")(0) IsNot Nothing Then
                    Output0Buffer.c = objResult.Properties("c")(0).ToString()
                End If
                If objResult.Properties.Contains("department") AndAlso objResult.Properties("department")(0) IsNot Nothing Then
                    Output0Buffer.Department = objResult.Properties("department")(0).ToString()
                End If
                If objResult.Properties.Contains("displayName") AndAlso objResult.Properties("displayname")(0) IsNot Nothing Then
                    Output0Buffer.DisplayName = objResult.Properties("displayName")(0).ToString()
                End If
                If objResult.Properties.Contains("distinguishedName") AndAlso objResult.Properties("distinguishedName")(0) IsNot Nothing Then
                    Output0Buffer.DistinguishedName = objResult.Properties("distinguishedName")(0).ToString()
                End If
                If objResult.Properties.Contains("employeeID") AndAlso objResult.Properties("employeeID")(0) IsNot Nothing Then
                    Output0Buffer.EmployeeID = objResult.Properties("employeeID")(0).ToString()
                End If
                If objResult.Properties.Contains("extensionAttribute14") AndAlso objResult.Properties("extensionAttribute14")(0) IsNot Nothing Then
                    Output0Buffer.ExtensionAttribute14 = objResult.Properties("extensionAttribute14")(0).ToString()
                End If
                If objResult.Properties.Contains("extensionAttribute2") AndAlso objResult.Properties("extensionAttribute2")(0) IsNot Nothing Then
                    Output0Buffer.ExtensionAttribute2 = objResult.Properties("extensionAttribute2")(0).ToString()
                End If
                If objResult.Properties.Contains("givenName") AndAlso objResult.Properties("givenName")(0) IsNot Nothing Then
                    Output0Buffer.GivenName = objResult.Properties("givenName")(0).ToString()
                End If
                If objResult.Properties.Contains("l") AndAlso objResult.Properties("l")(0) IsNot Nothing Then
                    Output0Buffer.L = objResult.Properties("l")(0).ToString()
                End If
                If objResult.Properties.Contains("lastLogon") AndAlso objResult.Properties("lastLogon")(0) <> 0 AndAlso objResult.Properties("lastLogon")(0) IsNot Nothing Then
                    Output0Buffer.LastLogon = DateTime.Parse(DateTime.FromFileTime(objResult.Properties("lastLogon")(0).ToString()))
                End If
                'If objResult.Properties.Contains("pwdLastSet") AndAlso objResult.Properties("pwdLastSet")(0) <> 0 AndAlso objResult.Properties("pwdLastSet")(0) IsNot Nothing Then
                '    Output0Buffer.PwdLastSet = DateTime.Parse(DateTime.FromFileTime(objResult.Properties("pwdLastSet")(0)).ToString())
                'End If
                If objResult.Properties.Contains("logonHours") AndAlso objResult.Properties("logonHours")(0) IsNot Nothing Then
                    Output0Buffer.LogonHours = objResult.Properties("logonHours")(0).ToString()
                End If
                If objResult.Properties.Contains("mail") AndAlso objResult.Properties("mail")(0) IsNot Nothing Then
                    Output0Buffer.Mail = objResult.Properties("mail")(0).ToString()
                End If
                If objResult.Properties.Contains("manager") AndAlso objResult.Properties("manager")(0) IsNot Nothing Then
                    Output0Buffer.Manager = objResult.Properties("manager")(0).ToString()
                End If
                If objResult.Properties.Contains("physicalDeliveryOfficeName") AndAlso objResult.Properties("physicalDeliveryOfficeName")(0) IsNot Nothing Then
                    Output0Buffer.PhysicalDeliveryOfficeName = objResult.Properties("physicalDeliveryOfficeName")(0).ToString()
                End If
                If objResult.Properties.Contains("postalCode") AndAlso objResult.Properties("postalCode")(0) IsNot Nothing Then
                    Output0Buffer.PostalCode = objResult.Properties("postalCode")(0).ToString()
                End If
                If objResult.Properties.Contains("pwdLastSet") AndAlso objResult.Properties("pwdLastSet")(0) <> 0 AndAlso objResult.Properties("pwdLastSet")(0) IsNot Nothing Then
                    Output0Buffer.PwdLastSet = DateTime.Parse(DateTime.FromFileTime(objResult.Properties("pwdLastSet")(0)).ToString())
                End If
                'If objResult.Properties.Contains("pwdLastSet") AndAlso objResult.Properties("pwdLastSet")(0) IsNot Nothing Then
                '    Output0Buffer.PwdLastSet = objResult.Properties("pwdLastSet")(0).ToString()
                'End If
                If objResult.Properties.Contains("sn") AndAlso objResult.Properties("sn")(0) IsNot Nothing Then
                    Output0Buffer.Sn = objResult.Properties("sn")(0).ToString()
                End If
                If objResult.Properties.Contains("st") AndAlso objResult.Properties("st")(0) IsNot Nothing Then
                    Output0Buffer.St = objResult.Properties("st")(0).ToString()
                End If
                If objResult.Properties.Contains("streetAddress") AndAlso objResult.Properties("streetAddress")(0) IsNot Nothing Then
                    Output0Buffer.StreetAddress = objResult.Properties("streetAddress")(0).ToString()
                End If
                If objResult.Properties.Contains("telephoneNumber") AndAlso objResult.Properties("telephoneNumber")(0) IsNot Nothing Then
                    Output0Buffer.TelephoneNumber = objResult.Properties("telephoneNumber")(0).ToString()
                End If
                If objResult.Properties.Contains("title") AndAlso objResult.Properties("title")(0) IsNot Nothing Then
                    Output0Buffer.Title = objResult.Properties("title")(0).ToString()
                End If
                If objResult.Properties.Contains("userAccountControl") AndAlso objResult.Properties("userAccountControl")(0) IsNot Nothing Then
                    Output0Buffer.UserAccountControl = objResult.Properties("userAccountControl")(0).ToString()
                End If
                If objResult.Properties.Contains("whenCreated") AndAlso objResult.Properties("whenCreated")(0) IsNot Nothing Then
                    Output0Buffer.WhenCreated = CDate(objResult.Properties("whenCreated")(0).ToString())
                End If

            Next

        Next

    End Sub

End Class

You need to create a function to convert that OctetString to a String. If you directly convert it that will show System.Byte[] this is because OctetString is a Byte Array type. Fof the function do something like this

Private Function ConvertByteArrayToString(xArray As Byte()) As String
    Dim [sByte] As String = ""
    For Each x As Byte In xArray
        [sByte] += x.ToString() & " "
    Next
    Return [sByte]
End Function

and use it like such

If objResult.Properties.Contains("logonHours") AndAlso objResult.Properties("logonHours")(0) IsNot Nothing Then
    Output0Buffer.LogonHours = ConvertByteArrayToString(objResult.Properties("logonHours"))
End If