如何在SQL Server Integration Services项目中使用OLE DB提供程序进行目录服务？

I can setup a connection manager that uses the Directory Services OLE provider and points to one of our domain controllers. Then, in the Data Flow area, I create an OLE DB Source and set the Data Access Mode to "SQL Command".

我可以设置一个使用目录服务OLE提供程序并指向我们的域控制器之一的连接管理器。然后,在数据流区域中,我创建一个OLE DB源并将数据访问模式设置为“SQL命令”。

I then use this query to pull data from Active Directory:

然后我使用此查询从Active Directory中提取数据:

Select employeeid, sAMaccountName
From 'LDAP://MyCompany.com'
Where objectClass = 'user'
  and objectClass = 'Person'
  and objectClass <> 'Computer'

If I parse the query, it says that it parses correctly. If I open the Query Builder, it gives me this error.

如果我解析查询,它会说它正确解析。如果我打开查询生成器,它会给我这个错误。

Error in FROM clause: near 'WHERE'. Unable to parse query text.

FROM子句出错:'WHERE'附近。无法解析查询文本。

I then click OK and it opens the Query Builder where I can successfully run the query and get results from AD. However, when I try to click OK to apply the changes or go into the Columns view, I get this error:

然后我单击确定,它打开查询生成器,我可以成功运行查询并从AD获取结果。但是,当我尝试单击“确定”以应用更改或进入“列”视图时,出现此错误:

Error at Data Flow Task [OLE DB Source 1 [941]]: An OLE DB error has occurred. Error code: 0x80040E21.

数据流任务出错[OLE DB Source 1 [941]]:发生OLE DB错误。错误代码:0x80040E21。

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

其他信息:来自HRESULT的异常:0xC0202009(Microsoft.SqlServer.DTSPipelineWrap)

I have also tried setting up the DB source using the "SQL command from variable" Access Mode. I set up a string variable with the value being the query... but that just gives me the same error.

我还尝试使用“来自变量的SQL命令”访问模式来设置数据库源。我设置了一个字符串变量,其值为查询...但这只是给了我同样的错误。

Because the error is so generic, I can't seem to find a description of what is actually wrong.

因为错误是如此通用,我似乎无法找到实际错误的描述。

Does anyone know how to successfully use the OLE DB Provider for Microsoft Direcotry Services? And/or does anyone know a better way to do this inside of SSIS?

有谁知道如何成功使用OLE DB Provider for Microsoft Direcotry Services?和/或有没有人知道在SSIS中更好的方法呢?

1 个解决方案

#1

Assuming you named the linked server (your Active Directory OLE DB provider) "ADSI", here's essentially what you'd need:

假设您将链接服务器(您的Active Directory OLE DB提供程序)命名为“ADSI”,这里基本上是您需要的:

SELECT samAccountName,
       employeeID
FROM OPENQUERY(ADSI,
  'SELECT samAccountName, employeeID
   FROM ''LDAP://MyCompany.com''
   WHERE objectClass=''Person''
   AND objectClass = ''User''
   AND NOT objectClass = ''Computer''')