[Webinar] Streamline your web hosting managementRegister Today

x
?
SolvedPrivate

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

Posted on 2014-02-05
10
Medium Priority
?
89 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month8 days, 1 hour left to enroll

607 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