查询AD以获取用户信息并使用sql表连接 [英] query AD for user info and join with sql table

问题描述

我正在尝试使用我的SSIS包中存在的脚本任务中的TableAdapter类来填充虚拟表.SQL包含行，如下面的屏幕截图所示。

我想从这些用户使用他们的电子邮件地址从ActiveDirectory获取UPN / SAmAccountname，并且只在RequestTime的ASC顺序中存储这些列 - Samaccountname，ADGroup，RequestType，RequestTime。基本上，填写虚拟表格如下
with只有以上提到的4列：

var table = new DataTable（）;

                var adapter = new OleDbDataAdapter（）;

                adapter.Fill（table，Dts.Variables [" User :: Users"。。Value）;

Dts变量用户是SSIS中的对象类型并存储SQL查询的输出它提供了如上面屏幕截图所示的列。

如何实现？是否可以从SQL查询中查找类似于SQL连接的AD？

看起来可以使用链接服务器，我参考（https://blog.sqlauthority.com/2016/03/30/sql-server-query-active-directory-data-using-adsi -ldap-linked-server /）但不确定我是否应该创建addlinkedserver存储过程，或者它是系统
SP。标题是"创建链接服务器"。但他们只执行它。我无法找到SP代码的帮助。

解决方案

嗨  msdnpublic1234，

谢谢在这里发布。

对于您的问题，您可以连接到数据库并读取数据库中的表。然后从数据表中获取电子邮件。您可以获取Samaccountname，ADGroup，RequestType，RequestTime 根据邮件地址。将所有数据
写入数据表中，然后将表写入数据库。

以下是有关如何从数据表中获取表的代码。

 DataTable dt1; 
 string connectionString = @" Data Source =（LocalDB）\ MSSQLLocalDB; AttachDbFilename = Database.mdf; Integrated Security = True" ;; 
 string sql =" SELECT * FROM Table1" ;; 
 using（SqlConnection conn = new SqlConnection（connectionString））
 {
 using（SqlCommand cmd = conn.CreateCommand（））
 {
 using（SqlDataAdapter ada = new SqlDataAdapter（sql，conn））
 {
 ada.Fill（sDs）; 
 dt1 = sDs.Tables [0]; 
} 
} 
} 

你可以通过更改下面的代码来获取特殊行。

更改

 string sql =" SELECT * FROM Table1"; 

到

 string sql =" SELECT ColumnName FROM Table1"; 

如果您想通过电子邮件获取AD详细信息，可以参考以下链接。

https://stackoverflow.com/questions/18658345/lookup-user-in-activedirectory-by-email-address/18658850

您可以保存表中的所有数据，然后将表写入数据库。

以下是将表写入数据库的代码。

 string connectionString = @" Data Source =（LocalDB）\ MSSQLLocalDB; AttachDbFilename = Database.mdf; Integ额定安全性=真"; 
 
 using（SqlConnection connection = new SqlConnection（connectionString））
 {
 connection.Open（）; 
 using（SqlBulkCopy bulkCopy = new SqlBulkCopy（connection））
 {
 foreach（dtmismatch.Columns中的DataColumn c）
 bulkCopy.ColumnMappings.Add（c.ColumnName，c.ColumnName ）; 
 
 bulkCopy.DestinationTableName = TableName; //表名
尝试
 {
 bulkCopy.WriteToServer（dtmismatch）; 
 MessageBox.Show（" Successful"）; 
} 
 catch（Exception ex）
 {
 MessageBox.Show（ex.Message）; 
} 
} 

最好的问候，

Wendy

Hi,

I am trying to fill a virtual table using TableAdapter class in script task present in my SSIS package.The SQL contains rows as shown in the screenshot below.

I want to get UPN/SAmAccountname from the ActiveDirectory for these users using their email addresses and store only these columns- Samaccountname,ADGroup,RequestType,RequestTime in the ASC order of RequestTime.Basically,fill the virtual table like below with only these above mentioned 4 columns:

var table = new DataTable();
                var adapter = new OleDbDataAdapter();
                adapter.Fill(table, Dts.Variables["User::Users"].Value);

Dts variable User is of object type within SSIS and stores the output of the SQL query that gives columns as shown in the above screenshot.

How can this be achieved?Is it possible to do a lookup on the AD from sql query similar to SQL joins?

Looks like it is possible using linked server,I refer to(https://blog.sqlauthority.com/2016/03/30/sql-server-query-active-directory-data-using-adsi-ldap-linked-server/) but not sure if i should create that addlinkedserver stored procedure or it is system SP.The heading says "create linked server" but they only execute it.I couldnt find the help on code for the SP.



解决方案

Hi msdnpublic1234,

Thank you for posting here.

For your question, you could connect to the database and read the table in database. And then get the email from the datatable. You could get the Samaccountname, ADGroup, RequestType, RequestTime  according to the mail address. Write all the data in a datatabe and then write the table to the database.

Here is the code about how to get the table from the datatable.

DataTable dt1;
 string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=Database.mdf;Integrated Security=True";
            string sql = "SELECT * FROM Table1";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    using (SqlDataAdapter ada = new SqlDataAdapter(sql, conn))
                    {
                        ada.Fill(sDs);
                        dt1 = sDs.Tables[0];
                    }
                }
            }

You could get the special row by changing the code below.

Change

string sql = "SELECT * FROM Table1";

To

string sql = "SELECT ColumnName FROM Table1";

If you want to get the AD details vis email, you could refer to the link below.

https://stackoverflow.com/questions/18658345/lookup-user-in-activedirectory-by-email-address/18658850

You could save all the data in the table and then write the table to database.

Here is the code about to write the table to database.

 string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=Database.mdf;Integrated Security=True";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                {
                    foreach (DataColumn c in dtmismatch.Columns)
                        bulkCopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);

                    bulkCopy.DestinationTableName = TableName;//table name
                    try
                    {
                        bulkCopy.WriteToServer(dtmismatch);
                        MessageBox.Show("Successful");
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }

Best Regards,

Wendy

这篇关于查询AD以获取用户信息并使用sql表连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！