Using SSIS Sources to Query the Active Directory
I am using SSIS to access the ldap server. With a little work, I was able to create an OLE-DB connection (Can't use ADO.NET connection). The most important part of the connection itself (in the connection manager) is that I am leaving the "server" so the field is blank as the server might switch. The provider is the .Net provider for the OleDb \ OLE DB provider for Microsoft Directory Services.
This works so far with the following command, which is used in the source:
SELECT extensionAttribute2 FROM 'LDAP://Mydonaimname' WHERE objectClass='User'
Now the weird part comes up as well as my question:
When I use ADO NET Source the above statement with the specified connection works, BUT I get a warning because extensionAttribute2 is of type System.object, which ADO NET Source does not support. The preview is correct though.
When I try to use the same statement and OLE DB source connection, I get an OLE DB error (unfortunately, the error message only lacks a helpful description with a generic 0x0 error as the error code) when I try to preview it. Despite looking around and trying, I didn't find anything there, so my question is:
- Is there a way to get OLE DB Source to work?
- If not, is there a way to remove the warning in the ADO NET source?
source to share
I've used several sources to try and get the data as you wrote (I don't have direct access to AD):
Processing Active Directory Information in SSIS - First Example Used
General about connector integration - gave me examples of what I can get
So using the first example:
1) I created OLE DB Connection Manager
. Selected vendor Native OLE DB/OLE DB Provider for Microsoft Directory Services
and added my server name;
2) Create variable var1 as Object;
3) Then drag Execute SQL task
. Open editor: selected Full result set
for ResultSet
, Direct input
for SQLSourceType
, set BypassPrepare
to True
; the selected Connection
one created in the first step; gave SqlStatement
:
SELECT ExtensionAttribute2 FROM 'LDAP://eurolith-dc' WHERE objectClass='User'
Also tested with this one because I have none
SELECT cn FROM 'LDAP://eurolith-dc' WHERE objectClass='User'
Also in the added tab Result Set
added the tab created in step 2 and install Result Name = 0
;
3) Created another variable Variable
as Object data object, will be used later for viewing;
4) To show the result, I used Foreach Loop Container
and Script task
. In Foreach Loop Container
I changed Enumerator
to Foreach ADO Enumerator
and chose ADO object source variable
as var1
. Also, the variable is mapped to the newly created Variable
and Index=0
. Inside this container, I've dragged Script task
. In the editor, I selected ReadOnlyVariables=User::Variable
. Opened the 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 the variable is NULL because I think I have no information in the ExtensionAttribute2
. So I checked with cn
.
This example worked for me.
And I suppose you also have multiple NULL values for ExtensionAttribute2
and why you got the error. Try checking with cn
and you will see.
source to share