c# - 将数据从 AD 拉入数据库的 SSIS 脚本任务

Question

需求:将大量数据(OU 内的所有用户)从事件目录中提取到数据库表中。

方法:我的任务是使用 SSIS 来执行此操作，因为作为同一通宵任务的一部分，还有其他项目需要完成，这些都是标准的 ETL 任务，因此这只是流程中的另一个步骤。

代码:

/*
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;
        }
    }
}

我希望你们中的很多人会认出这与数据女王这篇帖子中的代码基本相同:http://dataqueen.unlimitedviz.com/2012/09/get-around-active-directory-paging-on-ssis-import/当我自己编写的代码遇到很多问题时，我已根据自己的目的对其进行了调整。

目前，这一切都在一个脚本任务中。是的，我已经添加了相关引用资料，所以它们就在那里。

问题:当我在 SSIS 中运行脚本任务时(单独运行以避免包的其他部分干扰的任何机会)我得到:

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()

拜托，任何人，任何想法????????

Answer 1

所有归功于@billinkc(billinkc - 如果你添加一个答案我会接受它而不是这个但我不喜欢留下未回答的问题所以现在添加你的答案因为你没有在最后添加答案周)

"My guess is the failing line is string employeeID = results.Properties["employeeID"][0].ToString(); as you are probably getting things like Service accounts which won't have an employeeID defined. Copy your code into a proper .NET project (I like console) and step through with the deubgger to find the line number. – billinkc"