SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Run SSIS packages from remote client with stored procedure


Run SSIS packages from remote client with stored procedure

Author
Message
nlarge
nlarge
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 48
[Ben describes the method I use.]
JasonRowland
JasonRowland
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 1152
Ben Sullins-437405 (1/12/2010)
Would this work if the SQL Database Engine was not installed on the same box as SSIS? To my knowledge SSIS packages can only be called locally and in a large distributed architecture I generally don't run SSIS on the same box as the DB. For remote package execution however you can build a web service to run locally on the SSIS boxes that can call the packages. I've seen this method work very elegantly in the past...


This solution will only work where Integration Services and SQL Server are both on the same machine.

It must be nice to have seperate hardware for each service.
macqueen
macqueen
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 115
Jason, thanks for putting this together. I think this will prove useful to folks trying to solve the problem of running an SSIS package from a stored procedure.

One comment. You do not issue an END CONVERSATION in your the stored procedures which send and recieve messages. If you query SYS.Conversation_Endpoints in your database, I expect that you will see a number of conversations that are in your estimation over, but broker services still reports them as open.

To clean this up, you can run the following:
SELECT 'END CONVERSATION ''' + CAST(conversation_handle as char(36))+ ''' WITH CLEANUP'
FROM sys.conversation_endpoints

Then execute the output from the SELECT.



JasonRowland
JasonRowland
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 1152
You are correct macqueen, the end conversation is missing.
Thank you.
Ben Sullins-437405
Ben Sullins-437405
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 Visits: 267
It must be nice to have seperate hardware for each service.


It's actually more work...


Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...
JasonRowland
JasonRowland
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 1152
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.
nlarge
nlarge
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 48
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?
jswong05
jswong05
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 476
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
:-P
JasonRowland
JasonRowland
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 1152
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.
nlarge
nlarge
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 48
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search