Any possibilities to save the message tab information in SSIS

Question

Any possibilities to save the message tab information in SSIS

Tony1234

Right there with Babe

Points: 723
More actions
April 23, 2014 at 1:56 am

#292648

Hi All,
I have a package to execute the .SQl file in a folder. Is there any possibility to save message tab information into a variable in execute sql task.
Example:
In sql file I have a sample update query as shown below.
Update Test_table
Set Name =’one’
Where ID =1
when we run the above query in SSMS it will give the information like "(1 row(s) affected)”.
Is there any possibility to save this message tab information in SSIS execute sql task.
Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lempster SSCoach Points: 15651 More actions · Answer 1

So basically you want a row count?

In that case, add a 'SELECT @@ROWCOUNT' statement to your Execute SQL task in SSIS, set the ResultSet to 'Single Row' and then on the 'Result Set' screen, assign the result to a variable - you will need to create the variable or have an existing one available. You can then use that variable in subsequent tasks, e.g. in the message body of a Send Email task.

Regards

Lempster

Tony1234 Right there with Babe Points: 723 More actions · Answer 2

Hi Lempster,

Thank You very much for your replay.

Exactly I am expecting row count. But the approach you have mentioned will not workout for my scenario because, I am executing query file (.sql) as SQLSourceType as File connection in my execute sql task.

Thanks,

Tony.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

Tony1234 (4/23/2014)
Hi Lempster,
Thank You very much for your replay.
Exactly I am expecting row count. But the approach you have mentioned will not workout for my scenario because, I am executing query file (.sql) as SQLSourceType as File connection in my execute sql task.
Thanks,
Tony.

I don't know a way of doing this directly. Maybe you can load the query from the file into a string variable, append SELECT @@ROWCOUNT, then execute that?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Lempster SSCoach Points: 15651 More actions · Answer 4

Phil Parkin (4/23/2014)
I don't know a way of doing this directly. Maybe you can load the query from the file into a string variable, append SELECT @@ROWCOUNT, then execute that?

Yes, I see no reason why that wouldn't work.