Solved

Capturing multiple SQL messages (rows affected etc) while executing a SQL statement using SSIS

Posted on 2010-11-11
14
1,289 Views
Last Modified: 2012-06-27
When I run a query in SQL, I get a result set and another tab of Messages....I have a large stored procedure that I want to run in SSIS and I want to capture the messages window (not a result set) for review.

For example, if I run the following query:
Update test set testfield = 1 where datefield = '1/1/'2010'
Update test set testfield = 2 where datefield = '2/1/'2010'

I would like to capture the messages screen which might read:
(1551 row(s) affected)
(8773 row(s) affected)

I would like to save those two (in the real case thousands) messaage lines in a file so that I can save the messages for our records....and perhaps review them for error messages, warnings, etc.

Thanks!
0
Comment
Question by:ryanmaves
[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
  • 5
  • 5
  • 2
  • +2
14 Comments
 
LVL 4

Expert Comment

by:Amgad_Consulting_Co
ID: 34118114
Hi,

use "SET NOCOUNT ON;' before your query


regards
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 34119097
use the @@rowcount

http://www.brettb.com/SQL_Help_Rowcount_Rows_Affected.asp
Update test set testfield = 1 where datefield = '1/1/'2010'
SELECT @@rowcount AS 'RowsChanged'

Open in new window

0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34119735
From a meta data perspective you will probably have to use the SET FMT_ONLY ON as well as the set nocount on.
0
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34120499
If the author needs to capture the informational messages then they may be better off using:

SET NOCOUNT OFF
0
 

Author Comment

by:ryanmaves
ID: 34122232
I think my question may have been worded poorly.  The SQL script is running inside an "Execute SQL Task" within a SSIS package.  When I run the same SQL script within the MSSQL Server Management Studio, I get a tab labelled "Result Set" and a tab labelled "Messages."  The Messages tab contains thousands of lines of messages that range from "0 rows affected" to "Warning NULL value may affect summary..." to "You do not have permissions or table X does not exist"....whatever the messages are....I want them ALL (not just the row count).  I can't find where to get them (and thus store them) from WITHIN the SSIS package.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34122932
I don't think you can actually do that. Plus the error messages will stop the ssis package.
0
 

Author Comment

by:ryanmaves
ID: 34122960
No, the won't stop the execution of the query, or the SSIS package if it is set up correctly.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34122981
When you say setup correctly do you mean to set the accepted errors higher?
0
 

Author Comment

by:ryanmaves
ID: 34123004
Well, that is one way to accept the messages (accepted errors set higher)....but the entire script will finish and I can set the next step to start on completion rather than success.  We're kind of off-topic here.  I just want to catch the messages whether they be errors or not.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34123099
I suppose you may be able to run this using osql from the command line and output that to a file and import that file but it's not a pretty solution....
0
 

Author Comment

by:ryanmaves
ID: 34123138
Ok...can you give me an example of how to do that?
0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
ID: 34123227
Have a look at this for the osql part then just import the file created.

http://coppockscoolness.blogspot.com/2008/02/osql-query-to-text-file-thanks-to-brain.html
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34123270
>> I get a tab labelled "Result Set" and a tab labelled "Messages." ...<<
This is a function of the SSMS tool and as such does not exist in SSIS.  If you need that then you will have to build a .NET app to retrieve those messages.

>>No, the won't stop the execution of the query, or the SSIS package if it is set up correctly. <<
That depends.  It is true that Informational messages (such as "x number of rows affected") and Warning messages (such as "Warning: Null value is eliminated by an aggregate or other SET operation") do not cause the task to fail, Error messages (such as "Cannot find the object "%.*ls" because it does not exist or you do not have permissions") will cause the task to fail.
0
 

Author Closing Comment

by:ryanmaves
ID: 35838563
Sorry for the wait.  Points are yours.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server maintenance plan 8 64
Enabled trace flag 4135 or 4199 - SQL SERVER 2 34
SQL QUERY 3 45
What is needed to become a DBA? 7 56
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

752 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