Need to load into a table or in an object to use in later transformations in SSIS

[EDN Admin]

/*
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 Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Text;
using System.DirectoryServices;
using System.Collections;
using System.Xml;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;
namespace ST_7d38085b306e413ab25c0177aadf0633.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
public class User1
{
/*
* Get all users and the groups that they are a member of; write out to tab delimited file
*
* Example: GetUserGroups("VS", "LDAP://dc=vs,dc=local")
*/
public void GetUserGroups(string domain, string path, string filepath)
{
// Step 1
DirectoryEntry searchRoot = new DirectoryEntry(path, null, null,
AuthenticationTypes.Secure);
DirectorySearcher dirSearch = new DirectorySearcher(searchRoot);
dirSearch.SearchScope = SearchScope.Subtree;
dirSearch.PropertiesToLoad.Add("samaccountname");
dirSearch.PropertiesToLoad.Add("memberof");
dirSearch.Filter = "(objectclass=person)";
dirSearch.PageSize = 1000;
string samaccountname = String.Empty;
ArrayList groups;
int personcount = 0;
int objectcount = 0;
using (searchRoot)
{ // Step 2
using (SearchResultCollection results = dirSearch.FindAll())
{
objectcount = results.Count;
using (StreamWriter sw = new StreamWriter(filepath))
{ // Step 3
foreach (SearchResult result in results)
{ // Step 4
++personcount;
groups = new ArrayList();
// Step 5
ResultPropertyCollection props = result.Properties;
foreach (DictionaryEntry entry in props)
{
ResultPropertyValueCollection values = entry.Value
as ResultPropertyValueCollection;
if (entry.Key.ToString() == "samaccountname")
samaccountname = GetSingleValue(values);
if (entry.Key.ToString() == "memberof")
groups = GetGroups(values);
}
foreach (string group in groups)
{ // Step 6
sw.WriteLine("{0}t{1}t{2}", samaccountname, domain,
group);
}
}
sw.Flush();
}
}
}
}
ArrayList GetGroups(ResultPropertyValueCollection values)
{
ArrayList valueList = new ArrayList();
foreach (object val in values)
{
string memberof = val.ToString();
string[] pairs = memberof.Split(,);
string[] group = pairs[0].Split(=);
valueList.Add(group[1]);
}
return valueList;
}
string GetSingleValue(ResultPropertyValueCollection values)
{
foreach (object val in values)
{
return val.ToString();
}
return null;
}
}
/*
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()
{
//DirectoryEntry de = Dts.Variables["gLDAPQuery"]
string gLDAPQuery = Dts.Variables["gLDAPQuery"].Value.ToString();
string Domain = Dts.Variables["Domain"].Value.ToString();
//string sSqlConn = Dts.Variables

User1 user1 = new User1();
//user1.GetUserGroups("VSA", "LDAP://dc=VSA,dc=local", "c:\temp\ADTest.txt");
user1.GetUserGroups(Domain, gLDAPQuery, "c:\temp\ADTest.txt");
// TODO: Add your code here
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}

View the full article