SolvedPrivate

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

Posted on 2014-02-05
10
85 Views
Last Modified: 2016-02-10
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
Comment
Question by:blossompark
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39836668
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
 

Author Comment

by:blossompark
ID: 39836715
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39836742
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:blossompark
ID: 39837157
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
 

Author Comment

by:blossompark
ID: 39837171
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
 

Author Comment

by:blossompark
ID: 39838307
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39838401
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
 

Author Comment

by:blossompark
ID: 39838416
Tried that,
still the same
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39838421
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
 

Author Closing Comment

by:blossompark
ID: 39838472
Perfect!! Thanks Surendra
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question