The ExecuteSQL task makes it easy to capture the resultset of a query or stored proc in a variable. But what about the messages that would normally appear in the Messages tab when running queries in Management Studio or Query Analyzer? There must be a way to display and log rowcounts and other messages that a query normally produces.
Various package/task logging configrations haven't helped. I've read discussions about logging rowcounts in data flow tasks, but what about capturing messages in ExecuteSQL and other control flow tasks that run T-SQL?
I only have enough time to dabble in this technology so I may be missing something obvious or approaching this incorrectly. Any suggestion, reference, constructive criticism or reponse would be appreciated.
It is possible to capture the values of output parameters when using stored procedures with the Execute SQL Task. These values can be stored in SSIS variables to be further examined.
I hope this helps.
|||Duane Douglas wrote:
It is possible to capture the values of output parameters when using stored procedures with the Execute SQL Task. These values can be stored in SSIS variables to be further examined.
I hope this helps.
Except the OP is looking for diagnostic messages... For instance, in an update statement that only updates one row, you get "1 rows affected" or whatever the message says... The OP wants to be able to capture that output.|||
Duane Douglas wrote: It is possible to capture the values of output parameters when using stored procedures with the Execute SQL Task. These values can be stored in SSIS variables to be further examined.
I hope this helps.
Except the OP is looking for diagnostic messages... For instance, in an update statement that only updates one row, you get "1 rows affected" or whatever the message says... The OP wants to be able to capture that output.
Perhaps I don't completely understand what you're trying to accomplish. "Rows affected" is captured by the @.@.ROWCOUNT t-sql variable. As far as I know, the only other messages displayed in the query results window are errors which can also be captured by t-sql.|||
Phil Brammer wrote: Duane Douglas wrote: It is possible to capture the values of output parameters when using stored procedures with the Execute SQL Task. These values can be stored in SSIS variables to be further examined.
I hope this helps.
Except the OP is looking for diagnostic messages... For instance, in an update statement that only updates one row, you get "1 rows affected" or whatever the message says... The OP wants to be able to capture that output.
Perhaps I don't completely understand what you're trying to accomplish. "Rows affected" is captured by the @.@.ROWCOUNT t-sql variable. As far as I know, the only other messages displayed in the query results window are errors which can also be captured by t-sql.
Duane is correct. As a matter of course I generally finish off my Execute SQL Tasks with:
SELECT @.@.ROWCOUNT AS RowCnt
and store it in a package-scoped variable.
-Jamie
|||Perhaps I don't completely understand what you're trying to accomplish. "Rows affected" is captured by the @.@.ROWCOUNT t-sql variable. As far as I know, the only other messages displayed in the query results window are errors which can also be captured by t-sql.Phil Brammer wrote: Duane Douglas wrote: It is possible to capture the values of output parameters when using stored procedures with the Execute SQL Task. These values can be stored in SSIS variables to be further examined.
I hope this helps.
Except the OP is looking for diagnostic messages... For instance, in an update statement that only updates one row, you get "1 rows affected" or whatever the message says... The OP wants to be able to capture that output.
What about other messages thrown by SQL? I don't know what they are, but "Command(s) completed successfully." is a valid message. Can it be captured somehow? What about other messages that might be generated? (I don't know what they are, as I'm just speaking for the original poster.)|||
What about other messages thrown by SQL? I don't know what they are, but "Command(s) completed successfully." is a valid message. Can it be captured somehow? What about other messages that might be generated? (I don't know what they are, as I'm just speaking for the original poster.)
I may be completely wrong about this but messages such as "Command(s) completed successfully." are messages generated by SSMS, not the database engine. Hence, they wouldn't be accessible from SSIS.
-Jamie
|||
The answers and additional questions from all of you--Jamie, Phil and Duane along with some additional time experimenting with SSIS logging has been very helpful. I was indeed looking for SSMS or Query Analyzer message functionality to be duplicated in SSIS. Now I understand there are a few techniques that, combined, should provide the same messages.
It would be nice if such basic information were included in otherwise excellent references such as BOL or Kirk Haselden's Integration Services book. (Maybe this is mentioned somewhere and I missed it...). Anyway, thanks again.
|||
The answers and additional questions from all of you--Jamie, Phil and Duane along with some additional time experimenting with SSIS logging has been very helpful. I was indeed looking for SSMS or Query Analyzer message functionality to be duplicated in SSIS. Now I understand there are a few techniques that, combined, should provide the same messages.
It would be nice if such basic information were included in otherwise excellent references such as BOL or Kirk Haselden's Integration Services book. (Maybe this is mentioned somewhere and I missed it...). Anyway, thanks again.
Mike,
No problem.
If you think that BOL is short somewhere then tell them. The option to provide feedback is taken very seriously (I know from experience). In this case its more SQL engine or T-SQL stuff rather than SSIS - but provide it anyway.
-Jamie
|||
SSMS is merely a GUI for SQL Server. Having said that, all of SQL Server's functionality can be accessed via T-SQL. For example, when a query executes without errors (in other words, when it completes successfully), SQL Server sets the @.@.ERROR variable to "0". This value can be returned to the client via a stored proceedure output parameter.The answers and additional questions from all of you--Jamie, Phil and Duane along with some additional time experimenting with SSIS logging has been very helpful. I was indeed looking for SSMS or Query Analyzer message functionality to be duplicated in SSIS. Now I understand there are a few techniques that, combined, should provide the same messages.
It would be nice if such basic information were included in otherwise excellent references such as BOL or Kirk Haselden's Integration Services book. (Maybe this is mentioned somewhere and I missed it...). Anyway, thanks again.
One should bear in mind that SSIS is a very advanced feature of SQL Server. As such, a mastery of T-SQL is extremely helpful when using SSIS.
|||SSMS is merely a GUI for SQL Server. Having said that, all of SQL Server's functionality can be accessed via T-SQL. For example, when a query executes without errors (in other words, when it completes successfully), SQL Server sets the @.@.ERROR variable to "0". This value can be returned to the client via a stored proceedure output parameter. One should bear in mind that SSIS is a very advanced feature of SQL Server. As such, a mastery of T-SQL is extremely helpful when using SSIS.
Agreed, but mastery of T-SQL is not the central issue in this case. I've used @.@.ERROR, @.@.ROWCOUNT, etc. in scripts, stored procs and triggers plenty of times. DTS/SSIS is where I'm on less familiar ground--only recently finding time to delve deeper. My frustration, which Phil Brammer's questions helped to tease out, was explained when Jamie Thompson stated "...messages such as "Command(s) completed successfully." are messages generated by SSMS, not the database engine. Hence, they wouldn't be accessible from SSIS." This together with your posts made it clear that the message stream provided by SSMS or QA is not available in SSIS and rolling your own, so to speak, is the only solution. Okay, I can take it from there.
However, this is disappointing. If you're building an SSIS solution from scratch it's not a problem. But in this case I'm using SSIS to automate more than a dozen vendor developed DDL and DML scripts that otherwise have to be run manually in SSMS. One script would have to be manually rerun more than 40 times, entering a different "batch number" each time. I let them know I wasn't too happy about that one. Anyway, instead of someone sitting there babysitting this convoluted upgrade/cleanup process for hours (or in this case days), SSIS makes it easy to automate the entire process--run the scripts in proper order, manage precedence constraints, enumerate the batch numbers in a forEach loop, etc.
Everything is working beautifully except for logging. A few of the scripts contain 20 or more individual queries. Running them in SSMS or QA would automatically give me a nice message log of the number of records affected, output from print statements embedded in the scripts, error messages, etc. If I understand you guys correctly, there's no built-in functionality to generate this kind of message stream in SSIS. I'd have to break the big scripts down, create ExecuteSQL tasks for each individual query (around 50 to 60 altogether) add and map output parameters for each and configure logging to capture all this. Not a happy prospect.
Is there any reason Microsoft couldn't duplicate SSMS/QA message stream functionality in a future release of SSIS? It would save a lot of work in cases like this. I'll suggest it unless someone has a good reason why it couldn't be done or (preferably) knows of a workaround for this situation.
Happy holiday!
|||OK, so then how do you write the contents of the package-scoped variable to the log?
I think I've got my rowCnt into a variable but I can see what value it is.
I've tried having the package log 'OnVariableValueChanged' events, but it just tells me the name of the variable, not the value.
(I'm using SQL2005 SP2)
Thanks
|||
OK, so then how do you write the contents of the package-scoped variable to the log?
I think I've got my rowCnt into a variable but I can see what value it is.
I've tried having the package log 'OnVariableValueChanged' events, but it just tells me the name of the variable, not the value.
(I'm using SQL2005 SP2)
Thanks
If you are just trying to see the value of the variable, add a breakpoint and use the Watch window to view it. If you need to record the value, you could add an event handler for the OnVariableValueChanged event, and use an Execute SQL task to write it to the database.
No comments:
Post a Comment