c# - SSIS - 搜索字符串 - 脚本任务

Question

我想知道是否可以使用脚本任务(C#)将查询结果作为数组输入？我找到了一段代码，它使用脚本任务但没有将查询结果的每个值赋值到变量中。

//Read list of Tables with Schema from Database
string query = "Select * From "+TableName;
SqlCommand cmd = new SqlCommand(query, myADONETConnection);
//myADONETConnection.Open();

DataTable d_table = new DataTable();
d_table.Load(cmd.ExecuteReader());
myADONETConnection.Close();



string FileFullPath = DestinationFolder +"\\"+ FileNamePart +"_" + datetime + FileExtension;

StreamWriter sw = null;
sw = new StreamWriter(FileFullPath, false);

// Write the Header Row to File
int ColumnCount = d_table.Columns.Count;

for (int ic = 0; ic < ColumnCount; ic++)
{
   sw.Write(d_table.Columns[ic]);
   if (ic < ColumnCount - 1)
   {
      sw.Write(FileDelimiter);
   }
}
sw.Write(sw.NewLine);

// Write All Rows to the File
foreach (DataRow dr in d_table.Rows)
{
   for (int ir = 0; ir < ColumnCount; ir++)
   {
       if (!Convert.IsDBNull(dr[ir]))
       {
           sw.Write(dr[ir].ToString());
       }
       if (ir < ColumnCount - 1)
       {
           sw.Write(FileDelimiter);
       }
    }
       sw.Write(sw.NewLine);
 }

谢谢。

Answer 1

基于以下评论:

Basically, I'll make a query that returns multiple columns and rows and I'd like to assign each data in a variable, like a Foreach loop container. This code is an example

我假设您希望将查询结果存储在表中并逐行遍历结果并在变量中分配列值。然后，您应该使用Execute SQL Task 来执行查询并将结果集存储在类型为System.Object 的变量中，然后遍历结果集(结果表) 使用带有 ADO 枚举器 的 ForEach 循环容器。

要获得分步指南，您可以引用以下链接之一:

• Looping Through a Result Set with the ForEach Loop
• SSIS Basics: Using the Execute SQL Task to Generate Result Sets