Executing an SSIS package containing Analysis Services from ASP.NET

SQL Server Integration Services

blued555


I'm trying to execute an SSIS package from an ASP.NET web page. Using some code from http://msdn2.microsoft.com/en-us/library/ms403355.aspx I have managed to get this working when executing a package that contains a stored procedure, however it does not work when there is an Analysis Services cube to build.

Here is my code for the webservice

Code Snippet

Public Class SCCBuildDW

Inherits System.Web.Services.WebService

' LaunchPackage Method Parameters:

' 1. sourceType: file, sql, dts

' 2. sourceLocation: file system folder, (none), logical folder

' 3. packageName: for file system, ".dtsx" extension is appended

_

Public Function LaunchPackage( _

ByVal sourceType As String, _

ByVal sourceLocation As String, _

ByVal packageName As String) As Integer 'DTSExecResult

Dim packagePath As String

Dim myPackage As Package

Dim integrationServices As New Application

' Combine path and file name.

packagePath = Path.Combine(sourceLocation, packageName)

Select Case sourceType

Case "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()

End Function

End Class

This is the code from my page

Code Snippet

Protected Sub btnBuildDW_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnBuildDW.Click

ExecutePackage()

End Sub

Protected Sub ExecutePackage()

Dim launchPackageService As New SCCBuildDW.SCCBuildDW

Dim packageResult As Integer

Try

packageResult = launchPackageService.LaunchPackage("file", "c:\ssis", "PackageTest")

Catch ex As Exception

' The type of exception returned by a Web service is:

' System.Web.Services.Protocols.SoapException

lblResult.Text = "The following exception occurred: " & ex.Message

End Try

lblResult.Text = CType(packageResult, PackageExecutionResult).ToString

' Console.ReadKey()

End Sub

Private Enum PackageExecutionResult

PackageSucceeded

PackageFailed

PackageCompleted

PackageWasCancelled

End Enum

I'm using windows authentication and impersonating an identity. The identity has access to the analysis services role but the package still fails to run.

Can anyone offer any help or advice on this

Thanks

Top

Re: Executing an SSIS package containing Analysis Services from ASP.NET

blued555


Surely someone has had to do this before



Top

Re: Executing an SSIS package containing Analysis Services from ASP.NET

blued555





Top

Re: Executing an SSIS package containing Analysis Services from ASP.NET

JayH

This is probably related to the NTLM "double hop" problem. I assume that there are multiple machines involved here that you are trying to pass these impersonated credentials across

http://forums.microsoft.com/TechNet/ShowPost.aspx PostID=1586962&SiteID=17


Top

Re: Executing an SSIS package containing Analysis Services from ASP.NET

blued555

Thanks for the reply.

I have 2 machines, my web server and db server. After reading through the information in your link I think I need to clarify my security set up to check its definitely the 'double hop' problem.

The identity that I'm impersonating is one created for this appication to use, not specific to the user. The identity has been created as a login on my db server and a user on the db I'm connecting to. This allows me to run an SSIS package containing stored procedures only.

The AD user (identity) has been given access to the role required to run the Analysis Services Processing Task which is part of my SSIS package. Is this where the double hop is involved Passing the credentials from IIS to the Analysis Services Task




Top

Re: Executing an SSIS package containing Analysis Services from ASP.NET

JayH

blued555 wrote:

Thanks for the reply.

I have 2 machines, my web server and db server. After reading through the information in your link I think I need to clarify my security set up to check its definitely the 'double hop' problem.

The identity that I'm impersonating is one created for this appication to use, not specific to the user. The identity has been created as a login on my db server and a user on the db I'm connecting to. This allows me to run an SSIS package containing stored procedures only.

The AD user (identity) has been given access to the role required to run the Analysis Services Processing Task which is part of my SSIS package. Is this where the double hop is involved Passing the credentials from IIS to the Analysis Services Task



Thanks for the clarification. Since you only have two machines involved, and the identity context is being created on the web server and not impersonated from the web user, I doubt that "double hopping" is your problem. The typical scenario for double hopping is when the web server impersonates the web user's identity and then tries to use that identity context to connect to a remote SQL Server (three machines, two hops).

I suspect that either the impersonation is not working the way you expect or that the impersonated user does not have permission to do the SSAS processing. I recommend you test these scenarios by a) having your stored procedures log their user context to verify that impersonation is working, and b) log in to the SSIS machine with the user account in question and try to run the package directly to verify the user has permission.


Top

Re: Executing an SSIS package containing Analysis Services from ASP.NET

blued555

I got stuck working on another project so have just come back to this. I tried running the package as you suggested and it failed. However this was not down to permissions, someone had made changes to the Analysis Services Task causing it to fail regardless of which user tried to run it. This has been fixed and I can now execute the whole SSIS package successfully.

I'm not sure when the change was made, it must have been between when I tested running the package on the db server and then executing it from the webpage. Its now working and I've not had to change any of my code!

Thanks for your help with this.


Top