December 30, 2013 at 7:26 am
I am developing ETL packages that will transfer large datasets to a datamart. During development, I want to limit the amount of data returned to make it easier to find and fix problems. To that end, I've built parameterized queries using table-valued functions like this:
CREATE FUNCTION dw.[uf_MyTableValuedFunction]
(
-- Parameters:
@howmuch float = 100.0
)
RETURNS TABLE AS RETURN
...
SELECT TOP (@howmuch) PERCENT ...
FROM ...
...
During development, I set the @howmuch variable to a small number -- say 1 or 0.5 -- to limit the amount of data returned. During production, @howmuch will be set to 100 to get everything.
What I'd like to know is this:
At runtime, can I detect that my package is running under the debugger or not? Say that I am in BIDS. I open Solution Explorer and right-click on my package and choosed "Execute Package" from the context menu. When the package runs, I'd like to set the @howmuch variable according to whether I'm running under the debugger or not
e.g. build an expression for the SQL Statement to be executed like this:
"SELECT * FROM uf_MyTableValuedFunction ( " +
@[System::<some variable that indicates debug mode] : 1 : 100 +
" )"
Is there some system variable or combination of variables that I can use for this purpose? (the obvious one, @[System::Debug] does not appear to exist)
December 30, 2013 at 7:43 am
Presumably you run the debugger on a PC during development, but the package will run on a server in production? If that's the case, you can test the value of the MachineName variable.
John
December 30, 2013 at 7:47 am
John Mitchell-245523 (12/30/2013)
Presumably you run the debugger on a PC during development, but the package will run on a server in production? If that's the case, you can test the value of the MachineName variable.
Thanks John! I had thought of that before and I'll use it if there is nothing more definitive. I'm really looking for a generic technique that doesn't depend on where the package is running -- just how it is being run.
I suppose my search is futile.
December 30, 2013 at 7:51 am
How about the InteractiveMode variable?
Indicates whether the package is run in interactive mode. If a package is running in SSIS Designer, this property is set to True. If a package is running using the DTExec command prompt utility, the property is set to False.
John
December 30, 2013 at 7:55 am
John Mitchell-245523 (12/30/2013)
How about the InteractiveMode variable?Indicates whether the package is run in interactive mode. If a package is running in SSIS Designer, this property is set to True. If a package is running using the DTExec command prompt utility, the property is set to False.
John
that's it! how did I miss it? Oh well, now I'm off and running. thanks, John!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply