CODEWITHSUNDEEP
c#sql-serverssisldap
Thomas
Thomas

Reputation: 2984

Using SSIS - sources for querying active directory

I'm using SSIS to access the ldap server. After playing around a bit I managed to create a OLE-DB- connection (Can't use the ADO.NET connection). The most crucial part about the connection itself (in the connection manager) is that I leave out the "server", thus that field is blank as the server can switch. The provider is the .Net Providers for OleDb\OLE DB Provider for Microsoft Directory Services.

That works so far with the following command then being used in the source:

SELECT extensionAttribute2 FROM 'LDAP://Mydonaimname' WHERE objectClass='User'

Now comes the strange part and also my question:

When I'm using ADO NET Source the above statement with the mentioned connection works BUT I get a warning as extensionAttribute2 is of type System.object which ADO NET Source does not support. The preview is correct though.

When I try the same statement and connection with a OLE DB Source I get an OLE DB Error (sadly no useful description is given in the error message only general error with 0x0 as error code) when I try to see the preview. Despite looking around and trying I did not find anything there so my question is:

  1. Is there any way to get OLE DB Source working ?
  2. If not then is there a way to get the warning in ADO NET Source to disappear?

Upvotes: 3

Views: 6069

Answers (1)

Darka
Darka

Reputation: 2768

I have used several sources to try to get data as you wrote (I don't have staight access to AD):

Processing Active Directory Information in SSIS - used first example

Universal Connector Integration Details - gave me examples of what I can get

So using first example:

1) I have created OLE DB Connection Manager. Selected provider Native OLE DB/OLE DB Provider for Microsoft Directory Services and added my server name;

2) Create variable var1 as Object;

3) Then dragged Execute SQL task. Opened editor: selected Full result set for ResultSet, Direct input for SQLSourceType, set BypassPrepare to True; selected Connection created in first step; gave SqlStatement:

SELECT ExtensionAttribute2 FROM 'LDAP://eurolith-dc' WHERE objectClass='User'

Also tested with this because I don't have any
SELECT cn FROM 'LDAP://eurolith-dc' WHERE objectClass='User'

Also in Result Set tab added variable created in step 2 and set Result Name = 0;

3) Created another variable Variable as data type Object, will use later for view;

4) To show result I used Foreach Loop Container and Script task. In the Foreach Loop Container I changed Enumerator to Foreach ADO Enumerator and selected ADO object source variable as var1. Also mapped variable to new created Variable and Index=0. Inside this container I dragged Script task. In the editor I have selected ReadOnlyVariables=User::Variable. Opened script and pasted this code:

    String localVar = Convert.ToString(Dts.Variables["User::Variable"].Value);
    if (!String.IsNullOrEmpty(localVar))
    {
        MessageBox.Show(Dts.Variables["User::Variable"].Value.ToString());
    }

I am checking if variable is NULL, because I think I don't have any info in ExtensionAttribute2. So I have checked with cn.

This example worked for me.

And I guess, that you also have some NULL values for ExtensionAttribute2 and that why you got error. Try to check with cn and you will see.

Upvotes: 2

Related Questions