July 21, 2010 at 1:33 pm
I'm trying to log the values in my package variables when the package executes. I know how to set up breakpoints so that's easy when I'm debugging. However, after I've deployed my package I'd like to see what's in those variables (esp. those that change during the run).
I've made a stored procedure that will update an audit table as I run. But, to update my table for multiple variables I have to manually put in that stored procedure multiple times and change the variable name as a parameter to get it to log.
I'd like to put that sp into a for each loop and then pass in each variable that is in the package. I'm having a hard time figuring out how one loops through each individual variable.
I've also tried just doing this as a script task too--where I'd loop through a script task, passing in each variable one at a time, and then using string builder write out all my variable values.
So, the crux of it is--is it possible to dynamically loop through all the variables?
July 22, 2010 at 6:48 pm
I guess you should use Script Task and Log the values of variables in the code. That way you can see the values in SSIS log table (SQL Server 2005 - sysdtslog90; SQL Server 2008 - sysssislog).
July 22, 2010 at 11:26 pm
That's what I've ended up doing. And it works really nicely. What'd I'd like though is to be able to build a more generic package that could be placed in any package and have that one package be able to loop through all of the variable names regardless of how many there are in the calling package. It just seems like there ought to be a way to access all of the variables somehow. Sort of like how you can loop through objects on a web page via the DOM. Are all of the variables just stored in an array someplace that is available to a script task?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply