The code below works perfectly when I run the webservice in debug mode. I
can invoke any of the webmethods including the one below. This particular
method reads data from an Excel file and inserts it into a table in a SQL
Server 2008 database. The SSIS package is store in the MSDB database.

Unless I was executing this code as the creater of the SSIS package, it was
throwing an error on: SSIS.ExistsOnSqlServer

When I changed the Package Roles: Reader Role and Writer Role to Public, it
no longer throws errors. But returns a 1 which means the package failed on
this line: Return myPkg.Execute().

Is there a way to debug (step through) a SSIS package that is executing on
the server? I can login as an ordinary user but I need to be able to step
through the package.


<WebMethod()> Public Function ExecuteDTSPkg(ByVal sServer As String,
ByVal sXLSPath As String) As Integer
'parameters:
'sSever is the SQL Server Named Instance running on Server
'sXLSPath is the path to the spreadsheet
Dim pkgName As String = "ImportGrad"
Dim myPkg As Package
Dim SSIS As New Application

Try
If Not File.Exists(sXLSPath) Then
Dim fileEx As New Exception
Throw fileEx
End If
If SSIS.ExistsOnSqlServer(pkgName, sServer, String.Empty,
String.Empty) Then
'load package
myPkg = SSIS.LoadFromSqlServer(pkgName, sServer,
String.Empty, String.Empty, Nothing)
'set Excel path for Connection Manager
myPkg.Connections("Excel Connection
Manager").Properties("ExcelFilePath").SetValue(myP kg.Connections("Excel
Connection Manager"), sXLSPath)
'set connection string for Connection Manger
'myPkg.Connections("Excel Connection
Manager").ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ sXLSPath + ";Extended Properties=""EXCEL 8.0;HDR=YES"";"
myPkg.Connections("Excel Connection
Manager").ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ sXLSPath + ";Extended Properties=""EXCEL 12.0;HDR=YES"";"
Return myPkg.Execute()
End If
Catch ex As Exception
Throw ex
End Try

End Function

What are you using for the package protection level?

When you save an SSIS package there is a protection level setting, and
default is "encrypt with user key". You might need to play with this setting.
The protection level is also adjustable via package properties in BIDS.

To test, set the protection level to encrypt with a password like "1", then
execute the package from SQL Agent (or otherwise) using the password.

Here is a MSDN article with more details on setting the protection level in
SSIS: http://msdn.microsoft.com/en-us/library/ms141747.aspx

Good luck!

--
-Ian Beckett


"Whiskey Romeo Lima" wrote: