How to Quickly Query LDAP in Visual Studio SSIS

At times, you need to do a quick query against LDAP, but if your access is quite restricted, sometimes instead of using SQL Server Management Studio, you can also use SSIS Project within Visual Studio to do it.

Steps

1. Launch Visual Studio.

2. Create a new project and double-click on Integration Services Project.

   

3. Name your Project name (e.g. ITNotaQueryLDAP), then click Create button.

   

4. For this exercise, we use the default Package.dtsx. If it’s not opened by default, just double click it on the right pane window.

5. Drag Data Flow Task from SSIS Toolbox window to the Control Flow window.

   

6. Now we want to add a Connection Manager by right-clicking on a blank area under Connection Managers pane and select New ADO.NET Connection….

   

   A smaller window titled Configure ADO.NET Manager will pop up, click on the New button.

   

7. In a Connection Manager window, for the Provider, select OLE DB Provider for Microsoft Directory Services (under .Net Providers for OleDb). Then, click OK.

   

8. Once you’re back in Connection Manager window, click on the Test Connection button. Make sure you see the Test connection succeeded screen. Click both OK buttons.

   

9. After you clicked OK, you can see the configuration summary of your SSIS connection. Click OK again.

   

10. At this point, you have a Data Connection in your Connection Managers which you can rename to LDAP Data Connection.

    

11. Next, you can either double-click on the Data Flow Task or click on the Data Flow tab and drag ADO NET Source from the left pane, under Other Sources.

    Double click on the ADO NET Source box, and select the LDAP Data Connection for ADO.NET connection manager:, SQL command for Data access mode: and use the following SQL command as an example under SQL Command text:.

    LDAP://DC=ITNota,DC=Corp,DC=com;(&(objectCategory=Person)
    (sAMAccountName=Smith));SN,givenName,displayName,
    mailNickName,sAMAccountName,mail,msExchHideFromAddressLists,
    homeMDB;subtree
    

    This query specifically looks for all records for anyone with a last name of “Smith” in the domain. You need to replace the values of the DCs to your own domain and also the search string (Smith) to make it work in your situation. Click Preview button.

    

12. Once you clicked Preview button, You should see the first 200 rows.

    

That’s all there is to it.

Of course, there’s a caveat of using this as you can only view the first 200 rows of your result. This is not intended to be used as a solution of any deployment other than a workaround when you are limited with what you can access and you need to find a few records from your LDAP/Domain Services.