Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 88
  • Last Modified:

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
0
blossompark
Asked:
blossompark
  • 6
  • 4
1 Solution
 
Surendra NathCommented:
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
 
Surendra NathCommented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 NathCommented:
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
 
Surendra NathCommented:
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
 
blossomparkAuthor Commented:
Perfect!! Thanks Surendra
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now