• nlarge (1/13/2010)


    Thanks Jason for your comment.

    I would be very grateful if you could explain [or point me in the direction of] an explanation of how to extract information like the value of a user variable such as ErrorFileRowCount, and how to modify variable values in the same way that I can when I use my method, by using the service broker method. Exactly how much more work would that entail? Would I benefit from expanding on the service broker method as-opposed to creating a WCF service that can manipulate items through ASP.NET code?

    To inject a value into a variable, that would be done with the /Set switch. Any switch in the dtexec utility can be used and passed into the @dtExec parm.

    Example:

    --Run a package on server SQL1 in the SSIS Package Store named pkg1 and set the user variable @rootFolder value to \\someServer\share

    exec spRunSSISPackage '/DTS "\File System\pkg1" /SERVER "SQL1" /SET "\Package.Variables[User::rootFolder].Properties[Value]";"\\someServer\share" '

    If you need a variable value extracted and returned to the application right away, this solution probably isn't good for your situation. However, a script task could be added and an event fired with that variables value that is picked up by the logging configuration.

    I currently have 2 places (not included in this article) where data is stored to assist with troubleshooting.

    First, I write to a log table with the name of the machine and application making the initial stored procedure call that queues up the package as well as the date and time and the string that was passed into the @dtExec parameter. When the message is picked up from the queue, the record in the log table is updated with the date and time it was processed as well as the output from dtexec. So, if a non-existant package is passed into the @dtExec parm, I will see the machine, app, datetime, string passed to dtexec and the value "Return code: 5" in the outcome column.

    http://msdn.microsoft.com/en-us/library/ms162810(SQL.90).aspx

    Second, I use logging in the SSIS packages for any tasks whose events I am interested in and they are logged to a table.

    As for making a decision on this as compared to a WCF service, I personally would use this because I understand it and can support it without burdening my already overworked colleagues. Also, the applications queuing up the packages in my environment don't care if the package runs or not. They just send the message to run a package and the outcome of those package executions are dealt with later on in the workflow process.