Forum Discussion
Capture stored proc prints from C# SSIS Script task when completed successfully (2016)
I am trying to output the print messages from a stored proc that is being execute from SSIS script task. When the execution fails, I get all I want in the execution results tab (From Visual Studio), but I don't get anything in my output file when the execution completes successfully and this is what I have to accomplish. The output file is not even being created ! (???)
Context
This is a very big process. Calling structure contains about 30 stored procs. Clients are sending us millions of records that needs to go through many rules depending on many criteria. That being said, it not because the process completes successfully that it is indeed successful. There are various print messages and counts in the process, so we need to capture them and output the results every time we run the job.
I am not really familiar with SSIS or .net... I am a SQL/SAS programmer, so I am a little stuck with this. I understood that there are no events that are being triggered in the script that I wrote. I was thinking maybe using StatementCompletedEventArgs but I cannot seem to output the prints that way. Maybe capture them in a variable and then output the result after ?
This is what I have so far...
public void WriteToLog(string message)
{
string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
string LogFile = "\\fld6filer\\BrsSystems\\Workitems\\TF19816\test_" + datetime + ".log";
using (StreamWriter writer = new StreamWriter(LogFile))
{
writer.Write(message);
}
}
public void Main()
{
string par1 = "26";
string par2 = "202111";
string par3 = "09";
string par4 = "tblISF09202111";
string par5 = "tblSUFGenericPseudo202111";
string par6 = "tblSUFGenericPseudo202111";
string par7 = "tblSUFGenericPseudo202111";
string par8 = "tblSUFGenericPseudo202111";
string par9 = "tblSUFGenericFF202111";
string par10 = "1";
try
{
//USE ADO.NET Connection from SSIS Package to get data from table
SqlConnection MyConnection = new SqlConnection();
MyConnection = (SqlConnection)Dts.Connections["ADODB"].AcquireConnection(null);
//MyConnection.Open();
SqlCommand Storproc = new SqlCommand();
Storproc.Connection = MyConnection;
Storproc.CommandTimeout = 7200;
Storproc.CommandType = CommandType.StoredProcedure;
Storproc.CommandText = "[SurveyInterface].[uspCEProcessingMainScriptAllProcesses]";
SqlParameter Parameter1 = new SqlParameter("@InputSurveyGroupCodeId", par1);
SqlParameter Parameter2 = new SqlParameter("@InputReferencePeriod", par2);
SqlParameter Parameter3 = new SqlParameter("@InputSurveyCodeId", par3);
SqlParameter Parameter4 = new SqlParameter("@InputISFTable", par4);
SqlParameter Parameter5 = new SqlParameter("@InputSUFFrameTable", par5);
SqlParameter Parameter6 = new SqlParameter("@InputExtraVarSUFTable", par6);
SqlParameter Parameter7 = new SqlParameter("@InputPreviousPseudoFrameTableName", par7);
SqlParameter Parameter8 = new SqlParameter("@InputPreviousPseudoFrameTableExtra", par8);
SqlParameter Parameter9 = new SqlParameter("@InputFFTable", par9);
SqlParameter Parameter10 = new SqlParameter("@DEBUG_MODE", par10);
Storproc.Parameters.Add(Parameter1);
Storproc.Parameters.Add(Parameter2);
Storproc.Parameters.Add(Parameter3);
Storproc.Parameters.Add(Parameter4);
Storproc.Parameters.Add(Parameter5);
Storproc.Parameters.Add(Parameter6);
Storproc.Parameters.Add(Parameter7);
Storproc.Parameters.Add(Parameter8);
Storproc.Parameters.Add(Parameter9);
Storproc.Parameters.Add(Parameter10);
Storproc.ExecuteNonQuery();
WriteToLog ****** prints ****************;
MyConnection.Close();
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
WriteToLog(ex.Message);
Dts.Events.FireError(0, "ERROR", ex.Message, null, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
Mylene
3 Replies
The output file is not even being created ! (???)MyleneC , the log file is (will) located on a network share, rarly a good idea.
Are all required access permission for the share granted?
Olaf
olafhelper I also tried something like this...
using (var reader = Storproc.ExecuteReader())
{
do
{
if (reader.Read())
Console.WriteLine(reader.GetInt32(0));
}
while (reader.NextResult());
}
But it's not working either...- This is our output directory. It has nothing to do with the issue. Please note that I have modify the description of this ticket. I realized that it was not clear enough. Please let me know what you think 😉
