Importing Active Directory with DTS

[klewis10367]

Does any one know how to import Active Directory using DTS. I have connected to the AD Directory through Linked Servers but you can only query 1000 records. I need to import all records.

 

[atiaran]

Hi there

This is the kind of queries I used and it actually loads the data more than 1000 records. You just need to amend it slightly for the company involved. You can use this in a stored procedure and the store procedure to run as a sql job.

SELECT TOP 100 PERCENT Rowset_1.*, sn + ', ' + givenName AS FULL_NAME
FROM OPENQUERY(ADSI,
'SELECT title, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn, mail, userAccountControl FROM ''LDAP://DC=our,DC=domain,DC=org'' WHERE objectCategory = ''Person'' AND objectClass=''user'' and userAccountControl>1000')
Rowset_1
WHERE sn IS NOT NULL
UNION
SELECT TOP 100 PERCENT Rowset_1.*, sn + ', ' + givenName AS FULL_NAME
FROM OPENQUERY(ADSI,
'SELECT title, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn, mail, userAccountControl FROM ''LDAP://DC=our,DC=domain,DC=org'' WHERE objectCategory = ''Person'' AND objectClass=''user'' and userAccountControl<1000')
Rowset_1

Atia

 

[mrdenny]

You can also do a full dump of AD to a file using the AD comand line tool csvde.exe then import that file using DTS/SSIS/BCP/Bulk Insert.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com