Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Run SSIS packages from remote client with stored procedure Expand / Collapse
Author
Message
Posted Tuesday, January 12, 2010 9:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 9, 2011 7:38 AM
Points: 5, Visits: 48
[Ben describes the method I use.]
Post #846330
Posted Tuesday, January 12, 2010 10:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 7:25 PM
Points: 91, Visits: 819
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.
Post #846352
Posted Tuesday, January 12, 2010 12:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:37 PM
Points: 31, Visits: 83
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.




Post #846465
Posted Tuesday, January 12, 2010 3:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 7:25 PM
Points: 91, Visits: 819
You are correct macqueen, the end conversation is missing.
Thank you.
Post #846603
Posted Tuesday, January 12, 2010 3:48 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 25, 2010 10:10 AM
Points: 297, 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...
Post #846608
Posted Tuesday, January 12, 2010 4:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 7:25 PM
Points: 91, Visits: 819
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.
Post #846612
Posted Wednesday, January 13, 2010 8:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 9, 2011 7:38 AM
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?
Post #846942
Posted Wednesday, January 13, 2010 2:06 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:06 AM
Points: 27, Visits: 462
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
Post #847200
Posted Wednesday, January 13, 2010 3:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 7:25 PM
Points: 91, Visits: 819
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.
Post #847247
Posted Thursday, January 14, 2010 8:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 9, 2011 7:38 AM
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.
Post #847564
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse