I want to get data from Active Directory and insert it into an SQL server table using SSIS.
Using this very helpfull link http://agilebi.com/cs/blogs/jwelch/archive/2007/04/07/retrieving-information-from-active-directory-with-ssis.aspx this is what I did:
1. Created an ADO.NET connection with .Net Provider for OleDb\OLE DB Provider for Microsoft Directory Services.
2. Created an OleDb connection with Native OLE DB\SQL Native Client.
3. Drop a Data Flow Task on the Control Flow.
4. Drop a DataReader Source that uses the ADO.NET connection on the Data Flow. I used a SqlCommand like
Select cn 'LDAP://DC=domainname,DC=com' Where objectClass='User'
Pushing the preview button displays a warning about the data type not being supported and that it will be converted to DT_NTEXT.
But then the preview displays records just fine.
5. Drop a OLE DB Destination that uses OleDb connection on the Data Flow.
6. Performed data conversions.
5. Connect the Data Conversion task and the OLE DB Destination and mapped the columns.
6. Execute the Data Flow Task.
This results in the following error, on the ADO .NET source:
Error: 0xC02090F5 at DFT Load AD into MasterData, ADO_SRC ActiveDirectory : The component "ADO_SRC ActiveDirectory" (1) was unable to process the data. 'ADsDSOObject' failed with no error message available, result code: -2147016669(0x80072023).
Error: 0xC0047038 at DFT Load AD into MasterData, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "ADO_SRC ActiveDirectory" (1) returned error code 0xC02090F5.I was not able to find usefull information about these errors :-(
I have the exact same issue and am running SQL Server 2008 R2 64 bit. I have tried numerous work arounds none of which have fully worked. I did change the Input/Output properties of the ADO NET data source to DT_WSTR with a length of 4000, upped the DefaultBufferMaxRows, upped the DefaultBufferSize, and set ValidateExternalMetadata to false with limited luck as it will now import some of the records but not all then fails with the same error.
I have followed the advice of several articles:
Is this a bug in SSIS or maybe an AD policy setting? The MaxPageSize setting should not be the issue for me as I am retrieving more than the default of 1000 records.
You can't use a Data Flow Task to import records from AD if more records are returned in your query than the paging file size allows. The above error message is consistent with being unable to retrieve the data due to more records than the paging file size. Instead of using a Data Flow Task, use a C# script task. Here is the step by step: