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.DTSPi
Note: I am developing this directly on the database server.
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.DTSPi
Note: I am developing this directly on the database server.
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.DTSPi
==========================
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.DTSPi
==========================
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.
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.
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....
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....
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?
Where else might this be?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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
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
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!