I have a SSIS package that I want users to be able to execute by clicking a button on an a web page. The package does not require any parameters to be passed to it. Previously I've executed DTS packages without any problems but after a fair bit of investigation and trawling the net I've not found a way to do this successfully with SSIS. Some code I've tried -
Dim app As New Application() ' ' Load package from file system ' Dim package As Package = app.LoadPackage("c:\ssis\Package.dtsx", Nothing) 'package.ImportConfigurationFile("c:\ExamplePackage.dtsConfig") 'Dim vars As Variables = package.Variables 'vars("MyVariable").Value = "value from c#" Dim result As DTSExecResult = package.Execute() lblResult.Text = "Package Execution results: {0} " & result.ToString()All I get is a message 'Failure'.
Does anyone have an example of how to do this?
Your requirement is different and you are doing in wrong way. The mentioned code run the package on the same machine.
http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1801974&SiteID=1
|||
ok I've gone for option number 3 on your first link which uses a webservice to execute the package and then I can run the webservice through my asp.net page.
http://msdn2.microsoft.com/en-us/library/ms403355.aspx
This is my webservice - the example taken from that page,
<WebMethod()> _Public Function LaunchPackage( _ByVal sourceTypeAs String, _ByVal sourceLocationAs String, _ByVal packageNameAs String)As Integer'DTSExecResultDim packagePathAs String Dim myPackageAs PackageDim integrationServicesAs New Application' Combine path and file name. packagePath = Path.Combine(sourceLocation, packageName)Select Case sourceTypeCase"file"' Package is stored as a file. ' Add extension if not present.If String.IsNullOrEmpty(Path.GetExtension(packagePath))Then packagePath =String.Concat(packagePath,".dtsx")End If If File.Exists(packagePath)Then myPackage = integrationServices.LoadPackage(packagePath,Nothing)Else Throw New ApplicationException( _"Invalid file location: " & packagePath)End If Case"sql"' Package is stored in MSDB. ' Combine logical path and package name.If integrationServices.ExistsOnSqlServer(packagePath,".",String.Empty,String.Empty)Then myPackage = integrationServices.LoadFromSqlServer( _ packageName,"(local)",String.Empty,String.Empty,Nothing)Else Throw New ApplicationException( _"Invalid package name or location: " & packagePath)End If Case"dts"' Package is managed by SSIS Package Store. ' Default logical paths are File System and MSDB.If integrationServices.ExistsOnDtsServer(packagePath,".")Then myPackage = integrationServices.LoadFromDtsServer(packagePath,"localhost",Nothing)Else Throw New ApplicationException( _"Invalid package name or location: " & packagePath)End If Case Else Throw New ApplicationException( _ "Invalid sourceType argument: valid values are'file', 'sql', and 'dts'.")End Select Return myPackage.Execute()
and my asp.net page
Protected Sub btnBuildDW_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles btnBuildDW.Click ExecutePackage()End Sub Protected Sub ExecutePackage()Dim launchPackageServiceAs New SCCBuildDW.SCCBuildDWDim packageResultAs Integer Try packageResult = launchPackageService.LaunchPackage("file","c:\ssis","Package")Catch exAs Exception' The type of exception returned by a Web service is: ' System.Web.Services.Protocols.SoapException lblResult.Text ="The following exception occurred: " & ex.MessageEnd Try lblResult.Text = packageResult.ToString'CType(packageResult, PackageExecutionResult).ToString ' Console.ReadKey()End Sub Private Enum PackageExecutionResult PackageSucceeded PackageFailed PackageCompleted PackageWasCancelledEnd EnumI'm getting PackageFailed. The webservice runs and returns 1 - which must be PackageFailed.
Any ideas what I'm doing wrong? The webservice is using windows authentication and the folder the package is in has Everyone Full Control.
|||I've made some progress. It was failing due to permissions, I've altered these and can now execute a package containing a stored procedure. However my original package runs a stored procedure and then an Analysis Services Task. This still fails - I've checked the user is a member of the role that has access to run the task.
Is there something else I need to do?
|||I'm still stuck on this - can anyone offer any help?
|||I have this working now on my local machine. As soon as I move it to the live server I get PackageFailed when I attempt to run it. Has anyone experienced similar problems?
Given the amount of information on the internet I'm beginning to think I'm the only one who wants to execute SSIS in ASP.NET!
|||i have similar issues too. maybe running ssis dtsx package using web services is not a good idea at all. especially if we run into so many authentication issues.
what about using sql server agent to run ssis package!
|||I'm now using a stored procedure to execute the package and this works without problems.
Details of my code can be found here -http://forums.asp.net/p/1149972/1873190.aspx#1873190
|||I'm also having the same problems you have described. I don't have the option of executing from a stored procedure.
I noticed that when I use the <identity impersonate="true"> and specified the username and password, that I got much further, but the package still fails.
I'm guessing that I'll probably end up adjusting the permissions on certain directories and so forth until this works.
Hi all,
this might be useful.
http://www.codeproject.com/useritems/CallSSISFromCSharp.asp?df=100&forumid=309846&exp=0&select=1518271
I tried using this and it works.
