Any possibilities to save the message tab information in SSIS

  • 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.

  • 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

  • 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.

  • 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?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.

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

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