January 12, 2010 at 4:01 pm
nlarge (1/12/2010)
Jason,Just like in IT there is *most* of the time a solution that suits the problem. In this case, although the desired result is to *run* a package remotely, I have a couple of points that I wish to raise. One is that there is no transparency in this method. That is, if the package fails, how does one extrapolate the point in the package that failed? Also, how does one manipulate the variable assignments in the packages, both before and after the process has run in order to both reuse packages by reassigning them new variable values etc. Also, if we look at this process in a production environment, are we getting enough bang for our buck?
I was faced with the issue of having to run packages from an ASP.NET 2.0 application and required that there was total transparency on error handling and variable manipulation, as well as being aware of the security risks associated with a production environment when implementing procedures that execute on demand transactions against SQL Server, so I chose a different method.
The method I chose was to ask a fellow collegue to create me a WCF web/windows service that sits on the SQL server running SSIS. This WCF application exposes objects that can be manipulated to run a package, and, at any time, expose variables [via packagevariable() array object], errors etc [basically the ssis object model] to allow me to set variables in the package, run the package, extrapolate success and failure, and evaluate the values of variables following execution.
I hope that this adds resolve to this jigsaw puzzle in a more flavourable way.
But as I say at the begining of this post, the solution should fit the problem ...
Thanks, Nick.
Nick,
Thanks for your comments.
To your first point, that functionality can be built into this process quite easily. And your second point, that logic would have to be in the application making the stored procedure call much the same way that an application would do so with a web service.
There are quite a few ways to go about doing this and I have tried several. So when I figured out that I could do it this way and had not seen anyone else utilize service broker to run ssis packages I felt like this was a good opportunity for me to give something back to the community that has been providing me with solutions for several years now.
January 13, 2010 at 8:31 am
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?
January 13, 2010 at 2:06 pm
Nice write up. I agree with nlarge. The application code should be in the application using C#/ASP .Net. It would be easier for future debug, maintenance, integration from architecture point of view. I am sure this is not the only project.
Jason
http://dbace.us
😛
January 13, 2010 at 3:33 pm
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.
January 14, 2010 at 8:02 am
Jason,
Like I say, The solution must fit the problem, and in this case for you and your collegues it does.
Bottom Line, if I want to have all that you mention to run from a web application, using your method I would have to develop some kind of config file that would allow me to send the set switch parameters without having to modify code every time I want to run a different package with different parameters, and this is just not viable. Also, relying on manual intervention after a failure occurs by reading through a typical ssis logfile is a headache and a time waster - I tried this before and it brings back memories of early eighties COM objects and the like - too much information and not enough time to psychologically process it. With my method we only concentrate on areas where the errors occur by passing back the messages/errors instance. I would also have to find some way of executing the set switch from the web application as well. These are just some of the things that I can see that make the method that you describe a little more unmanageable than my method. Below is some example code. I have excluded some information for security reasons but I have supplied an example that shows how the theory would be used if it was implemented. Web Services do so much and for so little because they hide the methods from the calling functions. Your app just calls it and expects a result. It doesn't matter what goes on inside the service, pass/fail is enough for it. Hope this helps to further clarify. Using a service broker this would be hell. ???passing set statements??? This method is easier.
Function MyFunction
Try
ISSIS_FileSys = New MyApplication.SSIS_RunService.SsisWS
Call SetWebReferenceURLs()
'create an instance of an array to carry the variables over to ssis
Dim PackageVars(6) _
As SSIS_RunService.PackageVariable
'this calculates a value that we will pass to one particular variable later
Dim PeriodToRun As String = "00/2008"
PeriodToRun = _
CType(mpContentPlaceHolder.FindControl("ddPeriodMonth"), DropDownList).Text _
& "/" & _
CType(mpContentPlaceHolder.FindControl("ddPeriodYear"), DropDownList).Text
PackageVars(0) = New PackageVariable()
PackageVars(0).Name = "Joblist_ExportPeriod"
PackageVars(0).DataType = PackageVariableDataType.String
PackageVars(0).StringValue = PeriodToRun
PackageVars(1) = New PackageVariable()
PackageVars(1).Name = "RootPath"
PackageVars(1).DataType = PackageVariableDataType.String
PackageVars(1).StringValue = .ProjectPaths.URL.Current
'Pass the name of the user who is creating the files
PackageVars(2) = New PackageVariable()
PackageVars(2).Name = "CreatedByUN"
PackageVars(2).DataType = PackageVariableDataType.String
PackageVars(2).StringValue = GetCurrentUserName()
PackageVars(3) = New PackageVariable()
PackageVars(3).Name = "EASDB_SQLServer_Server"
PackageVars(3).DataType = PackageVariableDataType.String
PackageVars(3).StringValue = ServerName
PackageVars(4) = New PackageVariable()
PackageVars(4).Name = "EASDB_SQLServer_Database"
PackageVars(4).DataType = PackageVariableDataType.String
PackageVars(4).StringValue = DatabaseName
PackageVars(5) = New PackageVariable()
PackageVars(5).Name = "StoredProcConnection_Server"
PackageVars(5).DataType = PackageVariableDataType.String
PackageVars(5).StringValue = StoredProcServerName
PackageVars(6) = New PackageVariable()
PackageVars(6).Name = "StoredProcConnection_Database"
PackageVars(6).DataType = PackageVariableDataType.String
PackageVars(6).StringValue = StoredProcDatabaseName
Dim SSISPackagePath As String = "Packages\ExportFiles.dtsx"
Dim ReturnValue _
As SSIS_RunService.ExecuteSsisPackageInFileSystemResult = _
ISSIS_FileSys.ExecuteSsisPackageInFileSystem( _
SSISPackagePath, _
GrabSSPWD, _
PackageVars, _
"", _
"", _
"")
If ReturnValue.OperationStatus = OperationResult.Success Then
Return ReturnValue
Else
'if failure, then output failure information to
'writelog
HandleException(ReturnValue.Messages, _
"ExportDataFiles", _
Me.Page)
Return ReturnValue
End If
Catch ex As Exception
HandleException(ex, "ExportDataFiles", Me.Page)
Return Nothing
End Try
End Function
Notes:
SSIS_RunService is the name of the WCF service running on a sql server.
Set_WebReferenceURLs is a function that is a couple of lines that dynamically sets the http location of the ssis_RunService service, so that I can port between dev/qua and prod quite nicely.
HandleException is a function that I have to write exception logs to a logfile in the event that things go pear shaped in production.
January 14, 2010 at 8:38 am
Thanks Nick. It looks interesting and if it's not too much to ask, I would like to see your solution published in an article so I can explore what it may have to offer that I can use.
I would like to point out that retrieving information from the logs is quite simple for me as I have SSRS reports with all the filters I need to drill down to any problems quickly and efficiently. Also, my environment consists of roughly of 20 machines trying to run ssis packages at any given time and this solution allows me to easily throttle the number of packages executing at one time preventing the sql server's resources from becoming completely consumed. Another thing is, the machines requesting the packages to be executed can't be waiting around for the package to finish running before they begin processing the next job in their queue.
January 14, 2010 at 8:53 am
Hello Jason.
Sadly I don't feel safe posting any more than I currently have done. I just intended the code that I posted to be visual eye-candy of what the code can bring to the application. Notice how easy it is for me to just set the variables in code and execute the package!
In terms of having packages run asynchronously or synchronously, that can be managed through the web service. I personally want to be sure that these processes work one after the other for transparency when I trace back the flow in the event that some negative effect happens.
In all, I just wanted to make a point that "there is more than 1 way to implement solutions", as this is the absolute way that Microsoft work. I would also like to point out that it is important for developers to be constantly studying new technologies. If you stray from the path and don't catch up then you can be left on an island with a small penknife and it then becomes a long uphill stretch to get back into the race. Although I had another member of staff develop the web service for me, I will, at some point, be expected to "own" the implementation so I also need to do some catch up and learn WCF asap - thats the name of the game, and thats what makes software development exciting - New Technology.
Nick.
January 14, 2010 at 9:08 am
I think using WCF provides a robust solution to the need for executing SSIS packages remotely. I also think there is value in using Broker Servers when the resources for developing a WCF application are not available. The solution depends on the environment and resources available.
Agreed, passing a variable collection as you are doing is more attractive than having to build up a dtexec string that a stored procedure requires.
In regards to handling errors and getting error messages back from an SSIS package, I think WCF and Broker services offer similar functionality with a difference in implementation. Starting up a conversation to send a broker message to start a package does not mean that the conversation has to be over.
I use broker services to execute SSIS packages, and I also include broker messaging in my SSIS error handling where called for. If a task fails, then I can add an error handler to the task which calls a stored procedure to send broker messages back to the application. So a DBA can implement messaging with the skill set s/he already posses. This is where Broker Services shines in my opinion.
January 19, 2010 at 9:16 am
Jason,
I'd like to give this approach a try as it fits our use case scenario quite well but, I do not see the table creation scripts for the objects dbo.packageQueueLog and dbo.package_Queue; Would you have the table creation scripts, or parhaps could you post a replywith their schemas?
Thanks,
January 19, 2010 at 5:45 pm
phillip.snipes (1/19/2010)
Jason,I'd like to give this approach a try as it fits our use case scenario quite well but, I do not see the table creation scripts for the objects dbo.packageQueueLog and dbo.package_Queue; Would you have the table creation scripts, or parhaps could you post a replywith their schemas?
Thanks,
Phillip,
The table dbo.packageQueueLog isn't used in this solution and neither is the procedure dbo.spUpdatePackageQueueLog_executionID. Initially there was a lot more going on in this article, (logging, throttling, dynamic package variable values, etc...) but in order to get it approved by the editor I had to strip it down to the basics. It was a bit too busy I suppose.
The object dbo.package_Queue isn't a table, it is a service broker queue. The ddl for this object is:
create queue
package_Queue
with status =on,
activation (procedure_name = spExecQueuedPackages,
max_queue_readers = 1, --Change this value to allow more or less packages to be executed on this queue simultaneously.
execute as 'dbo');
Also, don't forget to end the conversation somewhere in your process (dbo.spExecQueuedPackages is a good spot) as I forgot to do this. Thanks to macqueen for pointing that out.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply