Querying Active Directory
-
I have a load process gets a list of people from a database and runs a script component that calls a method on a custom DLL to get the AD GUID for the person, then I populate a table with the results. The process works, but takes about 20 minutes to process ~5000 rows and it appears that the Script Component is a "blocking" component which is where the issue is.
Does anyone have a another/better(?) way to do this? Better meaning faster?
I am using the custom DLL because it is also used in an application and already has the method written to use the correct property in AD to return the GUID.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Jack,
How do you know which is the slow part? I would recommend you test each step one-by-one to determine if the call to the custom DLL is indeed the slow part.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
I see where you are coming from, but the dataflow has only 4 steps:
1. Get the appropriate rows from SQL Server - ~5000 rows returned sub-second.
2. Script Component Querying AD using the employee number (attribute in AD) to get the AD SID
3. Conditional Split - some won't have an AD Account so I eliminate them - sent to Konesans Trash Destination
4. Insert Rows into OLE DB Destintation - ~2300 rows. This "should" be quick.
The only thing I can see as a bottleneck is the Script Component as I use all the other transform types elsewhere and I don't see any performance issues with them there.
How would you suggest I isolate the other steps?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Try to direct to Trash Destination first only the source. Then include the script component, still directing to Trash Destination. Then you should know by this moment if the issue is indeed not related to the OLE DB Destination.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Seconds to go to trash destination from the OLE DB Source. I'm 5 minutes in with the script component going to the trash destination and still running. Pretty sure the script component querying AD is the issue.
Looks like it is waiting to process each row instead of passing them along as it process them.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Jack,
Is it possible to query multiple GUIDs using this custom DLL library? If that is the case, you have to change your script a bit making it asynchronous and buffer a batch of GUIDs, querying them once.
If that is not the case, you have to look for other approach for querying AD. I think there is OLE DB Source for AD if I'm not wrong. You can set up a Lookup Transformation with it.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply