如何在SSIS中创建多个命令文本c#
问题描述:
最初,我在我的ssis C#脚本任务中定义了commandtext,该任务计算了表A的行数。现在我需要再添加两个commandtext来计算表b和C中的行因为我需要在我的自定义电子邮件中包含该查询的输出。如何在SSIS中创建多个命令文本c#
try
{
dbConnection.Open();
if (dbConnection.State == ConnectionState.Open)
{
OleDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = "select count(*) as Total_Source from [dbo].A";
dbCommand.CommandText = "select count(*) as Total_Destination from [dbo].B";
dbCommand.CommandText = "select count(*) as Total_Blank from [dbo].C where ColumnA = ''";
OleDbDataReader dbReader = dbCommand.ExecuteReader();
if (dbReader.HasRows)
dtResults.Load(dbReader);
string theSum = dtResults.Rows[0]["Total_Source"].ToString();
string theSum1 = dtResults.Rows[0]["Total_Destination"].ToString();
//string theSum2 = dtResults.Rows[0]["Count_Blank"].ToString();
我相信我需要为表B和C(这在上面的脚本中是不正确的)定义命令文本,但我不知道该怎么做。
感谢任何帮助!
答
我还没有尝试过,但我认为如果CommandText
是string
属性,那么您将在每个句子上覆盖它,使用=
运算符。在变量
//...
OleDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandType = CommandType.Text;
var sb = new System.Text.StringBuilder();
sb.Append("select count(*) as Total_Source from [dbo].A;"); // Notice semicolon at the end of the string.
sb.Append("select count(*) as Total_Destination from [dbo].B;");
sb.Append("select count(*) as Total_Blank from [dbo].C where ColumnA = '';");
dbCommand.CommandText = sb.ToString();
OleDbDataReader dbReader = dbCommand.ExecuteReader();
//...
答
商店的数量和返回那些在一个选择 - 让您的这个SQL语句:
你可以试试这个
DECLARE @total_Source AS INT;
DECLARE @total_Destination AS INT;
DECLARE @total_Blank AS INT;
SELECT @total_Source=Count(*) FROM [dbo].A;
SELECT @total_Destination=Count(*) FROM [dbo].B;
SELECT @total_Blank=Count(*) FROM [dbo].C WHERE ColumnA = ''";
SELECT @total_Source AS Total_Source, @total_Destination AS Total_Destination, @total_Blank AS Total_Blank
+0
也@ @ Eliasib13说,你覆盖你的CommandText。我甚至没有注意到这一点。 –
我没有成功追加较早尝试... –
我认为返回值被前面的查询结果覆盖,你需要分开每个查询并单独执行 – LONG