asked on

SSIS Package, need to access Active Directory

I am trying to build an SSIS package that accesses Active Directory to check for AMAAccountNames.

In SSMS I can run this statement (using a linked server)...
SELECT * from openquery (ADSI, 'select sAMAccountName from ''LDAP://...'' ')
It works.

In my SSIS package I am able to create a connection to active directory (using the OLE DB Provider for Microsoft Directory Services) and the "Test Connection" button returns "...succeeded" but when I go to create a OLE DB Data Source it errors out.

The details...

In my OLE DB Source I select my "test succeeded" connection named "ADSI" for the "OLE DB connection manager" dropdown, then "SQL command" from "Data access mode". In the "SQL command text" I put this...

select sAMAccountName from 'LDAP://...connection info..."

When I click Preview I get this error...
Error at Data Flow Task [OLE DB Source [359]]: An OLE DB error has occurred. Error code: 0x80040E21.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)

Note: I am developing this directly on the database server.

exx1976

I'm by no means an SSIS expert, I've actually done very little SQL coding, but..

A quick look turned up the following MSDN article about parameter mappings: http://msdn.microsoft.com/en-us/library/ms141696.aspx

Also this infromation from a microsoft.com forum:
--------------------------
1. Add a variable to the package to contain your query. But put the query in parentheses and give it an alias. For example:

(SELECT MyField from MyDatabase.MyTable where MyField != '') a

2. Set the "Data access mode" of your "OLE DB Source" to "Table name or view name variable". Select the variable created in the step above.

I don't know why this works. It looks like either SSIS or the OLE DB provider converts this to "SELECT * FROM " + <table or view name>". But SSIS or the OLE DB driver is breaking when it uses a table name versus when it uses a SQL command, because the same syntax doesn't work if your data access mode is set to "SQL Command".
------------------------------

Hope this helps!

Bruce

ASKER

I tried this, putting my AD select string in the variable but I'm getting a similar error when I try to close the OLE DB Source error.

You didn't mention, what type of OLE DB Connection did you make? I am trying your solution with the aforementioned OLE DB Provider for Microsoft Directory Services and I am still getting an error when it tries to parse the SQL statement (from a variable). Basically the same one I had when I was selecting SQL command from the Data access mode.

=========================================
Error at RCDB Active Directory Update [WF Active Directory [218]]: An OLE DB error has occurred. Error code: 0x80040E37.

Error at RCDB Active Directory Update [WF Active Directory [218]]: Opening a rowset for "select sAMAccountName from ''LDAP://...connection info...''" failed. Check that the object exists in the database.

------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

=========================================

Bruce

ASKER

If you have a minute, here's how to try this...

Drag an OLE DB Source onto your Data Flow
Double click on the new Source
Next to the OLE DB connection manager dropdown click the New button
On the Configure OLE DB Connection Manager click the New button
On the Connection Manager select OLE DB Provider for Microsoft Directory Services
Put ADSI in the Server or file name box (assuming your AD server name is ADSI)
Click Test Connection (it should succeed)
If successful, click OK on the Connection Manager
Click OK on the Configure OLE DB Connection Manager
==Now here's where I'm having the trouble, completing the setup of the OLE DB Source Editor.==
On the OLE DB Source Editor select SQL command in the Data access mode.
In the SQL command text enter the AD query (i.e. select sAMAccountName from ''LDAP://...)

That errors out but I'm not sure why.

exx1976

Found this.. Seems to be relevant.. ?

SSIS by default will use " doublequotes as the quoted identifier if the OLE DB Provider does not override these settings.

Try changing the data source to "Enable Quoted Identifiers" to True (1) to suppot " doublequotes around the LDAP string....

Bruce

ASKER

"Enable Quoted Identifiers" to True (1) <--I'm not finding this in the Connection Manager dialog for my ADSI connection.

Where else might this be?

ASKER CERTIFIED SOLUTION

exx1976

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bruce

ASKER

Yes that does work. My only problem is then credential associated with the Linked Server. I don't want it tied directly to my login.

I've got to leave for the day and because Big Brother is installing more tools on my machine. I'll be back at this in the morning.

Thank you so much for sticking with me on this...!!

-Bruce

SOLUTION

exx1976

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bruce

ASKER

When I setup my Linked Server I selected "Be made using security context:" on the Security page and provided my user name and password. I just changed the setting to "Be made using the login's current security context:" and the view seems to still work.

So I have my AD data now, without being tied to my login. This was one of the primary reasons for me to start this task.

Thank you so much for your help. If I could give you more than 500 pts I would. Most likely I'll be back for other AD questions.

Thanks!!!

-Bruce