How to execute DTSX from Stored Procedures/Asp.Net ?

Let's imagine a common scenary where we have a server with our SQL Server 2005 and another server with our aps.net application running.
Our goal is to launch a dtsx from our web application and get back a @returncode that will inform us about the execution state.

1. Let's build our DTSX package, test it and publish on a server reachable from our asp.net application (can be the same server)
2. Let's create a Stored Procedure that will execute the DTSX passing to it some variable.
3. Let's create a function inside our web.application that will execute the Stored Procedure and get back the result.

Let's GO..

1. The Stored Procedure will execute the DTSX , passed as parameter from our web.application, trough the shell (xp_cmdshell), with the command DTExec. The Proceudre i built for this example is very flexible, so all the command line that the xp_cmdshell will execute is built in from the web.application

CREATE procedure [dbo].[spExecuteDTSX]

@cmd varchar(2000)

as

SET NOCOUNT ON

declare @returncode int
exec @returncode = master..xp_cmdshell @cmd
select @returncode Errore

Be sure to have enabled the xp_cmdshell (Sql Server Surface Attack Configuration)

2. Server side create an enumerator (it's not manditory, but will help us to have a cleaner code)

Public Enum DTExecRet
 package_OK = 0
 package_FAIL = 1
 package_ABORT = 3
 package_NOTFOUND = 4
 package_NOTLOAD = 5
 package_SNTXERROR = 6
End Enum

In the function\method that will execute your Stored Procedure (i imagine a button click), let's instanziate 3 variables now.
One for the dtsx server path, one for the dtsx name and another for the final string with the execute command.
In this example the server path is taken form the web.config and i pass to the DTSX also 2 variables (you have to configure the DTSX to accept 2 variables inbound).

Dim strPath As String = System.Configuration.ConfigurationManager.AppSettings("DTS").ToString
Dim strFile As String = "Package.dtsx"
Dim cmd As String = "DTExec /F """ & strPath & strFile & """ /SET \Package.Variables[sNome].Properties[Value];" & Me.txtNome.Text & " /SET \Package.Variables[nIDCliente].Properties[Value];" & Me.cbbCliente.SelectedValue

So now we have all ready and we can call our Stored Procedure in the way we are used to do passing the 3 parameters we created and get back a @returncode.
In my case the Procedure will return a Datasat.

objImportazione.cmd = cmd
Dim res As DTExecRet = objImportazione.RunDTSX

Once we have our @returncode we can easily check how went the whole process

strRes = String.Empty
Select Case res
  Case CLSImportazione.DTExecRet.package_OK
    strRes="ok"
  Case CLSImportazione.DTExecRet.package_ABORT
    strRes="abort"
  Case CLSImportazione.DTExecRet.package_FAIL
    strRes="fail"
  Case CLSImportazione.DTExecRet.package_NOTFOUND
    strRes="package not found"
  Case CLSImportazione.DTExecRet.package_NOTLOAD
    strRes="error loading package"
  Case CLSImportazione.DTExecRet.package_SNTXERROR
    strRes="sintax error"
End Select

CLSUtility.AlertScript(me,strRes)

for the msgbox chek this CLSUtility.AlertScript(me,"Your Message Here")

This is all guys, i hope it's quite clear and once again sorry if my english is not perfect.