SolvedPrivate

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

Posted on 2014-02-05
10
78 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
  • 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now