Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Any way to see or view the results of an execute sql task ? Expand / Collapse
Author
Message
Posted Thursday, October 24, 2013 2:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:51 PM
Points: 183, Visits: 480
I want to see the results of my execute SQL query. Is there any simple way to do that ? I am looking for some kind of viewer or even file where the results can be written to.

Post #1508273
Posted Thursday, October 24, 2013 3:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
You cannot see this in SSIS itself.
You'll need to go to the database and check if the query was executed.
You could also use SQL Server Profiler to verify if the query has been sent to the database correctly.
Or you could implement some auditing in the query itself.

The Execute SQL Task will only tell you if the query has been successfully executed or not and how long it took.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1508288
Posted Monday, October 28, 2013 4:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 4:13 AM
Points: 124, Visits: 329
Write the results of your SQL query out to a variable, try single row first, if you have a full result set then it gets a little more tricky.

1 . Create a string variable eg: @resultsfromquery.

2. In you SQL Task container, choose ResultSet Single row

3. Choose Result Set from the menu on the side GENERAL, ParameterMapping, Result Set, Expressions.

4. Set the Result Name to 0 and the VariableName to your variable eg: User::resultsfromquery

5. Add a breakpoint to your SQLTask, you can use Break when the container receives the OnPostExecute

6. Start Debug (F5)

7. When debug starts you will have the extra option of adding a WATCH window.

8. When the watch window appears drag your variable onto the watch window.

When your package hits the breakpoint, you will then see your results appear in your variable.

If you are doing a full result set you will need to set up a for each loop container

Hope this helps

Paul
Post #1508796
Posted Monday, October 28, 2013 7:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
pnr8uk (10/28/2013)
Write the results of your SQL query out to a variable, try single row first, if you have a full result set then it gets a little more tricky.

1 . Create a string variable eg: @resultsfromquery.

2. In you SQL Task container, choose ResultSet Single row

3. Choose Result Set from the menu on the side GENERAL, ParameterMapping, Result Set, Expressions.

4. Set the Result Name to 0 and the VariableName to your variable eg: User::resultsfromquery

5. Add a breakpoint to your SQLTask, you can use Break when the container receives the OnPostExecute

6. Start Debug (F5)

7. When debug starts you will have the extra option of adding a WATCH window.

8. When the watch window appears drag your variable onto the watch window.

When your package hits the breakpoint, you will then see your results appear in your variable.

If you are doing a full result set you will need to set up a for each loop container

Hope this helps

Paul


That only works with SELECT statements of course, which are kind of pointless in an Execute SQL Task unless you assign the results to a variable




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1508850
Posted Monday, October 28, 2013 8:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 4:13 AM
Points: 124, Visits: 329
Well yes but the poster seemed to want that? :-S
Post #1508914
Posted Monday, October 28, 2013 2:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
pnr8uk (10/28/2013)
Well yes but the poster seemed to want that? :-S


Not sure, he doesn't mention the type of query
If it's an update statement, you see nothing in SSIS.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1509107
Posted Monday, October 28, 2013 2:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 4:13 AM
Points: 124, Visits: 329
Dude... what are you trying to do here? I'm just trying to help the guy out, there's no mention of an update in his post or I wouldn't have put down my solution. I'm not trying to say you were wrong or anything. Aren't these forums about trying to provide a solution, not undermine any poster or helper?

I want to see the results of my execute SQL query. Is there any simple way to do that ? I am looking for some kind of viewer or even file where the results can be written to.

Post #1509116
Posted Tuesday, October 29, 2013 1:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
Dude, relax. I was just pointing out that there's not enough information in the original post to know what kind of query it is.
If it is an update/delete, the variable is useless, if it's a select, it's not.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1509197
Posted Wednesday, October 30, 2013 8:55 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 281, Visits: 1,056
This is one of two reasons I don't use the execute SQL task. If I get called at 1 AM because the SQL failed I want to know where it failed and what error I got. I don't want to have to rerun the SQL in SSMS to find the error. And I may have to determine if I can rerun the earlier steps in the SQL, may need to restore or recreate certain files. Not things you want to have to think about at 1 AM.

The second reason is I have to update the SSIS package anytime I make a SQL change. I know this can be a good thing if you keep versions of each update. But luckily we haven't ran into any issues, yet.

We use an 'Execute process task' to call a 'bat' file that executes the SQL. I can set this to put the log from the SQL out to a file that I can then review.

So until someone can tell me how I can see this same type of 'log' information for an execution of a SQL job, I will not be using the execute SQL task.
Post #1509822
Posted Wednesday, October 30, 2013 9:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:07 PM
Points: 3,559, Visits: 7,679
below86 (10/30/2013)
So until someone can tell me how I can see this same type of 'log' information for an execution of a SQL job, I will not be using the execute SQL task.

If you just want to know where and why the task failed, you could use SQL Server error handling to get an even better error log. To avoid changes on the SSIS package, you could simply use Stored Procedures instead of the full query.
I'm not sure that your way is the way to go, but if it works for you, then I can't suggest you to stop using it, just wanted to give some ideas.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1509839
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse