如何填充从Active Directory用户信息的SQL Server表？

问题描述

在SSIS包，我怎么填充与用户在多个Active Directory域的SQL Server表（在同一林中）使用VB.NET？

Using VB.NET in an SSIS package, how do I populate an SQL Server table with the users in multiple Active Directory domains (in the same forest)?

推荐答案

下面是从一个域导入Active Directory用户信息到数据库表VB.NET的帮助下在样本逻辑的脚本组件的配置的来源的。该样品在SSIS 2012进行测试，但应该在2008年及以上的SSIS。这个逻辑不能在2005 SSIS工作，因为命名空间的 System.DirectoryServices.AccountManagement 的引入只能在.NET Framework 3.5的和2005 SSIS使用.NET框架2.0

Script Component (VB.NET) with System.DirectoryServices

Here is a sample logic that import Active Directory users information from one domain into database table with the help of VB.NET in Script Component configured as Source. This sample was tested in SSIS 2012 but should work in SSIS 2008 and above. This logic will not work in SSIS 2005 because the namespace System.DirectoryServices.AccountManagement was introduced only in .NET framework 3.5 and SSIS 2005 uses .NET Framework 2.0

• 创建一个SSIS包。此示例使用SSIS 2012。

• Create an SSIS package. This sample uses SSIS 2012.

创建的 OLEDB连接管理器 的，将连接到SQL Server数据库。如果你创建了一个数据源，添加数据源到包的连接管理器选项卡。

Create an OLEDB Connection Manager that would connect to the SQL Server database. If you created a data source, add the data source to the package's connection manager tab.

拖放的 数据流任务 的到的控制流量的标签。

双击的 数据流任务 的切换到的数据流的标签。

拖放的 脚本组件 的到的数据流的标签。

检查 来源 上的选择脚本组件类型的对话框，然后点击确定。

Check Source on the Select Script Component Type dialog and click OK.

双击脚本组件，打开的脚本转换编辑器的。点击 输入和输出 的标签页。

Double-click the Script Component to open the Script Transformation Editor. Click Inputs and Outputs tab page.

重命名输出到ActiveDirectory中给一个有意义的名字。

Rename the Output to ActiveDirectory to give a meaningful name.

选择输出列，然后单击添加列添加每个下面提到列。这仅是为了说明本实施例。您可能需要增加你的preference列。

Select Output Columns and click Add Column to add each of the below mentioned columns. This is only to illustrate this example. You might need to add columns of your preference.

Name              Data Type                Length
----------------- ------------------------ ------
FirstName         Unicode string [DT_WSTR]    255
LastName          Unicode string [DT_WSTR]    255
SAMAccountName    Unicode string [DT_WSTR]    255
UserPrincipalName Unicode string [DT_WSTR]    255

• 定义的列后，点击的 剧本 的标签页

  • After defining the columns, click Script tab page

    更​​改ScriptLanguage到的 的Microsoft Visual Basic 2010 的

    Change the ScriptLanguage to Microsoft Visual Basic 2010

    在Solution Explorer中，用鼠标右键单击该脚本组件项目，然后单击 添加引用... 的。添加对下面的命名空间。

    On the Solution Explorer, right-click the Script Component project and click Add Reference.... Add references to the following namespaces.

    System.DirectoryServices
    System.DirectoryServices.AccountManagement
    

    • 粘贴下面的VB.NET code到脚本组件。替换部分的 ＆lt;您正在访问的域名放在这里＆GT; 您合适的域名。在code初始化的 PrincipalContext 和 PrincipalSearcher 的中的 preExecute 的方法对象然后部署它们的 PostExecute 的方法。 CreateNewOutputRows方法遍历每个AD中的行获取的用户属性信息。
    • Paste the below VB.NET code into the Script component. Replace the section <Your domain name goes here> with your appropriate domain name. The code initializes PrincipalContext and PrincipalSearcher objects in PreExecute method and then disposes them in PostExecute method. CreateNewOutputRows method loops through each of the row found in AD to fetch the user attributes information.

    #Region "Imports"
    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
    Imports System.DirectoryServices.AccountManagement
    Imports System.DirectoryServices
    
    #End Region
    
    <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
    <CLSCompliant(False)> _
    Public Class ScriptMain
        Inherits UserComponent
    
        Dim principalContext As PrincipalContext = Nothing
        Dim principalSearcher As PrincipalSearcher = Nothing
    
        Public Overrides Sub PreExecute()
            principalContext = New PrincipalContext(ContextType.Domain, "<Your domain name goes here>")
            principalSearcher = New PrincipalSearcher(New UserPrincipal(principalContext))
            MyBase.PreExecute()
        End Sub
    
        Public Overrides Sub PostExecute()
            principalContext = Nothing
            principalSearcher = Nothing
            MyBase.PostExecute()
        End Sub
    
        Public Overrides Sub CreateNewOutputRows()
    
            For Each principal As Principal In principalSearcher.FindAll()
    
                Dim entry As DirectoryEntry = TryCast(principal.GetUnderlyingObject(), DirectoryEntry)
    
                With ActiveDirectoryBuffer
                    .AddRow()
    
                    If entry.Properties("givenName").Value IsNot Nothing Then
                        .FirstName = entry.Properties("givenName").Value.ToString()
                    Else
                        .FirstName = "Unknown"
                    End If
    
                    If entry.Properties("sn").Value IsNot Nothing Then
                        .LastName = entry.Properties("sn").Value.ToString()
                    Else
                        .LastName = "Unknown"
                    End If
    
                    If entry.Properties("samAccountName").Value IsNot Nothing Then
                        .SAMAccountName = entry.Properties("samAccountName").Value.ToString()
                    Else
                        .SAMAccountName = "Unknown"
                    End If
    
                    If entry.Properties("userPrincipalName").Value IsNot Nothing Then
                        .UserPrincipalName = entry.Properties("userPrincipalName").Value.ToString()
                    Else
                        .UserPrincipalName = "Unknown"
                    End If
    
                End With
    
            Next
        End Sub
    
    End Class
    

    • 关闭脚本转换编辑器。

      • Close the Script Transformation Editor.

        拖放一个OLE DB目标到Data Flow选项卡。脚本组件连接到OLE DB目标到源输出重定向。选择适当的OLE DB连接管理和其中该数据应该被插入到表

        Drag and drop an OLE DB Destination onto the Data Flow tab. Connect the Script component to the OLE DB destination to redirect the source output. Select the appropriate OLE DB Connection Manager and the table where the data should be inserted into.

        该样品只从一个域提供负载信息。如果您有多个域，你可以将它们存储在表中。取所有域列表的信息，并使用的 Foreach循环容器 的可控制流来遍历各个领域，并使用上述获取的用户信息上述做法。可能也有一个更好的方式在VB.NET做到这一点。

        This sample provides loading information from only one domain. If you have multiple domains, you could stored them in a table. Fetch information of all the domain lists and use Foreach Loop Container available on the Control Flow to loop through each domain and get the users information using the above mentioned approach. May be there is also a better way to do this within VB.NET.

        您可以找到在MSDN下面链接的Active Directory用户属性的完整列表。您需要点击链接，了解与LDAP显示名称。

        You can find the complete list of Active Directory user attributes in the below MSDN link. You need to click the links to find the LDAP-Display-Name.

        的所有属性（视窗）的

        下面是另一个可能有助于获取用户对象属性链接

        Here is another link that might help to get the user object attributes

        的用户对象属性（视窗）的

        这篇关于如何填充从Active Directory用户信息的SQL Server表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！