September 11, 2015 at 9:24 am
SQL Server 2008 and user connecting with SSIS.
How can I trace this user's activity? When I try I do not get the sql they are running. I know their package is pulling data. I assume this is because the code is 'complied' and not really running straight sql type code.
I have looked online and found that there are settings that need to be set in the package for the trace to be able to work as expected.
The issue is that I can't really tell the user this or it's basically saying 'if you don't have this setting set then i can't see what you are doing'.
What I'm asking is, how can I get what the SSIS package is doing if I don't have any control over the package?
Any help is greatly appreciated. Thanks in advance.
Live to Throw
Throw to Live
Will Summers
September 11, 2015 at 11:29 am
YOu can definitely using Profiler/SQL Trace or XEvents to see what SQL an SSIS package is sending to a SQL Server, you just need to make sure you are connected to the same SQL Server(s) (multiple traces if you need to conect to multiple servers). If you are wondering what is happening in a component/task that doesn't connect to a database that is completely different.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 11, 2015 at 12:08 pm
sorry, should have been more clear. I'm doing the trace programmaticly. here is the code I use:
EXEC @rc = sp_trace_create @TraceID output, 0, @NFNAME, @FS, NULL
EXEC sp_trace_setevent @TraceID, 41, 1, 1
EXEC sp_trace_setevent @TraceID, 41, 1, 1
EXEC sp_trace_setevent @TraceID, 41, 6, 1
EXEC sp_trace_setevent @TraceID, 41, 11, 1
EXEC sp_trace_setevent @TraceID, 41, 12, 1
EXEC sp_trace_setevent @TraceID, 41, 13, 1
EXEC sp_trace_setevent @TraceID, 41, 14, 1
EXEC sp_trace_setevent @TraceID, 41, 15, 1
EXEC sp_trace_setevent @TraceID, 41, 16, 1
EXEC sp_trace_setevent @TraceID, 41, 17, 1
EXEC sp_trace_setevent @TraceID, 41, 35, 1
EXEC sp_trace_setevent @TraceID, 41, 40, 1
EXEC sp_trace_setevent @TraceID, 41, 48, 1
EXEC sp_trace_setfilter @TraceID, 11, 0, 0, N'trace name'
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
EXEC sp_trace_setstatus @TraceID, 1
I have removed the error checking and development lines for ease of reading.
September 11, 2015 at 12:27 pm
You are collecting SQL:StmtCompleted and you need RPC:Completed to get what SSIS is doing.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 11, 2015 at 12:41 pm
I will try that. thanks.
September 11, 2015 at 12:50 pm
I have a simple ssis/bids package that connects to my server using the user's id/pwd. It then runs a simple SELECT GETDATE() against my server.
I added the following line and there was no change in the trace file:
EXEC sp_trace_setevent @TraceID, 41, 10, @on
I need to setup a trace that captures the SELECT GETDATE(). If I connect using a query window my trace captures everything I do, but I can't get it to capture what happens in an SSIS/BIDS package.
September 11, 2015 at 1:24 pm
Well, you didn't add the RPC:Completed event and SSIS is using RPC to send it's queries. Check out https://msdn.microsoft.com/en-us/library/ms186265.aspx That's the 2016 version of BOL, but trace hasn't changed because it has been deprecated since 2012 was released so MS isn't doing any development on it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 11, 2015 at 1:38 pm
If this isn't the line I am suppose to add then what is?
EXEC sp_trace_setevent @TraceID, 41, 10, @on
It's option 10 which is what I added - RPC:Completed. And that link is actually the one I used to find it.
September 11, 2015 at 1:46 pm
Will1922 (9/11/2015)
If this isn't the line I am suppose to add then what is?EXEC sp_trace_setevent @TraceID, 41, 10, @on
It's option 10 which is what I added - RPC:Completed. And that link is actually the one I used to find it.
No the 3rd parameter is the column_id not the event_id/event_class. You need something like:
EXEC sp_trace_setevent @TraceID, 10, 1, @on
This says event_id 10 which is RPC:Completed and Column 1 which is TextData.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 11, 2015 at 1:59 pm
Ah. I see now. duh. I wrote this code a month or so ago. I will test. thanks.
September 11, 2015 at 2:02 pm
Awesome! That fixed it. Thanks again for the help.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply