StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSSIS Script Task to pull data from AD into a DB
text
Body
copied!<p>Requirement: to pull a large amount of data (all users within an OU) from active directory into a database table.</p> <p>Method: I have been tasked with using SSIS to do this because there are other items to be completed as part of the same overnight task which are standard ETL things so this would just be another step in the process flow.</p> <p>Code:</p> <pre><code>/* Microsoft SQL Server Integration Services Script Task Write scripts using Microsoft Visual C# 2008. The ScriptMain is the entry point class of the script. */ using System; using System.Data; using System.Data.SqlClient; using System.DirectoryServices; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; namespace ST_dc256a9b209442c7bc089d333507abeb.csproj { [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase { #region VSTA generated code enum ScriptResults { Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure }; #endregion /* The execution engine calls this method when the task executes. To access the object model, use the Dts property. Connections, variables, events, and logging features are available as members of the Dts property as shown in the following examples. To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value; To post a log entry, call Dts.Log("This is my log text", 999, null); To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true); To use the connections collection use something like the following: ConnectionManager cm = Dts.Connections.Add("OLEDB"); cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"; Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. To open Help, press F1. */ public void Main() { //Set up the AD connection; using (DirectorySearcher ds = new DirectorySearcher()) { //Edit the filter for your purposes; ds.Filter = "(&(objectClass=user)(|(sAMAccountName=A*)(sAMAccountName=D0*)))"; ds.SearchScope = SearchScope.Subtree; ds.PageSize = 1000; //This will page through the records 1000 at a time; //Set up SQL Connection string sSqlConn = Dts.Variables["SqlConn"].Value.ToString(); SqlConnection sqlConnection1 = new SqlConnection(sSqlConn); SqlCommand cmd = new SqlCommand(); SqlDataReader reader; cmd.CommandType = CommandType.Text; cmd.Connection = sqlConnection1; //Read all records in AD that meet the search criteria into a Collection using (SearchResultCollection src = ds.FindAll()) { //For each record object in the Collection, insert a record into the SQL table foreach (SearchResult results in src) { string sAMAccountName = results.Properties["sAMAccountName"][0].ToString(); //string objectCategory = results.Properties["objectCategory"][0].ToString(); string objectSid = results.Properties["objectSid"][0].ToString(); string givenName = results.Properties["givenName"][0].ToString(); string lastName = results.Properties["sn"][0].ToString(); string employeeID = results.Properties["employeeID"][0].ToString(); string email = results.Properties["mail"][0].ToString(); //Replace any single quotes in the string with two single quotes for sql INSERT statement objectSid = objectSid.Replace("'", "''"); givenName = givenName.Replace("'", "''"); lastName = lastName.Replace("'", "''"); employeeID = employeeID.Replace("'", "''"); email = email.Replace("'", "''"); sqlConnection1.Open(); cmd.CommandText = "INSERT INTO ADImport (userName, objectSid, firstName, lastName, employeeNo, email) VALUES ('" + sAMAccountName + "','" + objectSid + "','" + givenName + "','" + lastName + "','" + employeeID + "','" + email + "')"; reader = cmd.ExecuteReader(); string propertyName = "Description"; //or whichever multi-value field you are importing ResultPropertyValueCollection valueCollection = results.Properties[propertyName]; //Iterate thru the collection for the user and insert each value from the multi-value field into a table foreach (String sMultiValueField in valueCollection) { string sValue = sMultiValueField.Replace("'","''"); //Replace any single quotes with double quotes //sqlConnection1.Open(); cmd.CommandText = "INSERT INTO ADImport_Description (userName, objectSid, objectDescription) VALUES ('" + sAMAccountName + "','" + objectSid + "','" + sValue + "')"; reader = cmd.ExecuteReader(); //sqlConnection1.Close(); } sqlConnection1.Close(); } } } Dts.TaskResult = (int)ScriptResults.Success; } } } </code></pre> <p>I expect a lot of you will recognise this as being basically the same as code from this post from the data queen: <a href="http://dataqueen.unlimitedviz.com/2012/09/get-around-active-directory-paging-on-ssis-import/" rel="nofollow">http://dataqueen.unlimitedviz.com/2012/09/get-around-active-directory-paging-on-ssis-import/</a> which I've adapted for my purposes as I was running into a lot of issues with the code I'd written myself.</p> <p>This is all in one script task on its own at the moment. Yes, I've added in the relevant references so they are there.</p> <p>Problem: when I run the script task in SSIS (on its own to avoid any chance of the other parts of the package interfering) I get:</p> <pre><code>Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index at System.Collections.ArrayList.get_Item(Int32 index) at System.DirectoryServices.ResultPropertyValueCollection.get_Item(Int32 index) at ST_dc256a9b209442c7bc089d333507abeb.csproj.ScriptMain.Main() </code></pre> <p>Please, anyone, any ideas????????</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload