SSIS C# Script Task variable information not displaying in the Execution results tab

Hi,
I have the following C# Script Task running in an SSIS package in Visual Studio
 public void Main()
        {
            // TODO: Add your code here
               Dts.TaskResult = (int)ScriptResults.Success;
            OleDbDataAdapter oleDA = new OleDbDataAdapter();
            DataTable dt = new DataTable();
            DataColumn col = null;
            DataRow row = null;
            string strMsg = null;

            oleDA.Fill(dt, Dts.Variables["JobsFailedToRun"].Value);

            foreach (DataRow row_ in dt.Rows)
            {
                row = row_;
                foreach (DataColumn col_ in dt.Columns)
                {
                    col = col_;
                   // strMsg = strMsg + col.ColumnName + ": " + row[col.Ordinal].ToString() + Environment.NewLine;
                    strMsg = strMsg  + row[col.Ordinal].ToString() + Environment.NewLine;
                }
                MessageBox.Show("The job "+strMsg+" failed to start");
                strMsg = "";
                    bool fireAgain = false;
                  Dts.Events.FireInformation(1, "The job ", strMsg, "", 0, ref fireAgain);
                    Dts.Events.FireInformation(1, "TEST!!!!!!!!!!!!!!!!!!", "Put a message here", "", 0, ref fireAgain);


            }
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
    }
}

Open in new window


The task runs as expected, outputting data with the SHOW command. But it is not displaying the contents of the strMsg variable in the execution results window, see below
error-message-not-displaying.jpg
Any guidance appreciated. Thanks
blossomparkAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Surendra NathConnect With a Mentor Technology LeadCommented:
why are you setting the strMSG to blank after you display the messagebox.show...

MessageBox.Show("The job "+strMsg+" failed to start");
strMsg = "";
bool fireAgain = true;
Dts.Events.FireInformation(1, "The job ", strMsg, "", 0, ref fireAgain);
Dts.Events.FireInformation(1, "TEST!!!!!!!!!!!!!!!!!!", "Put a message here", "", 0, ref fireAgain);

I think this should be done after wards you log the message here

MessageBox.Show("The job "+strMsg+" failed to start");
bool fireAgain = true;
Dts.Events.FireInformation(1, "The job ", strMsg, "", 0, ref fireAgain);
Dts.Events.FireInformation(1, "TEST!!!!!!!!!!!!!!!!!!", "Put a message here", "", 0, ref fireAgain);
strMsg = "";
0
 
Surendra NathTechnology LeadCommented:
Messagebox.show will come as a popup while the SSIS is getting executed...

See the below example on how it works.
http://www.sqldbpros.com/2012/11/create-a-messagebox-in-ssis-with-c/
0
 
blossomparkAuthor Commented:
Hi, Surrendra,
Yes, the Message box will pop up, and it does...but the contents of the variable strMsg should also be displayed in the execution display window after where it says "the job information"
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Surendra NathTechnology LeadCommented:
it may be possible that the execution is not going into the below for loop at all

So i suggest to initialize the strMsg with a default value than null and see if that is getting pulled....

                strMsg = 'Default Message';
                foreach (DataColumn col_ in dt.Columns)
                {
                    col = col_;
                   // strMsg = strMsg + col.ColumnName + ": " + row[col.Ordinal].ToString() + Environment.NewLine;
                    strMsg = strMsg  + row[col.Ordinal].ToString() + Environment.NewLine;
                }
                MessageBox.Show("The job "+strMsg+" failed to start");

Open in new window


See if that displays something in strMsg now and if it does then we can simply say it is not going into foreach loop at all..
0
 
blossomparkAuthor Commented:
it is going into the loop,
"TEST!!!!!!!!!!!!!!!!!!", "Put a message here"
is displayed twice in the  grab above..
I know that  it should loop twice as there are two entries in the JobsFailedToRun Object Variable.
0
 
blossomparkAuthor Commented:
The issue is that
Dts.Events.FireInformation(1, "The job ", strMsg, "", 0, ref fireAgain);

should display the contents of the strMsg variable in the execution results window but it is not
0
 
blossomparkAuthor Commented:
When the package executes, The Message Box displays the first row of the table returned  in the execute sql task "Jobs Failed to Run", which is O_SRHR
MessageBox.Show("The job "+strMsg+" failed to start");
1
On the second iteration of the loop
The Message Box displays the second row of the table returned  in the execute sql task "Jobs Failed to Run", which is O_ASCOR
2
as there are only 2 rows in this table, the loop stops and the package ends
3
But the values O_SRHR and O_ASCOR should be displayed in the progress tab by
                  Dts.Events.FireInformation(1, "The job ", strMsg, "", 0, ref fireAgain);

Open in new window

4jobs failed to run properties
0
 
Surendra NathTechnology LeadCommented:
An extract from MSDN

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.idtscomponentevents.fireinformation.aspx
 

Because firing of an event may be expensive, the run-time engine provides a mechanism for suppressing events that you are not interested in. Every event firing method has a FireAgain parameter. If the value of this variable is false, after the method returns, the caller will not fire this event again for the duration of the current execution.

so, I think changing the parameter from false to true might log the message.


MessageBox.Show("The job "+strMsg+" failed to start");
strMsg = "";
bool fireAgain = true;
Dts.Events.FireInformation(1, "The job ", strMsg, "", 0, ref fireAgain);
Dts.Events.FireInformation(1, "TEST!!!!!!!!!!!!!!!!!!", "Put a message here", "", 0, ref fireAgain);
0
 
blossomparkAuthor Commented:
Tried that,
still the same
0
 
blossomparkAuthor Commented:
Perfect!! Thanks Surendra
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.