Run SSIS from Asp.net Application

Question

I am trying to run a simple SSIS package (copying data from an external folder to a SQL 2005 table). I want to run this package from an Asp.net 2.0 application. Any suggestions?

I searched a lot of different blogs and websites, but all of these methods fail (usually due to security issues)

dtexec / FILE "package name" etc.

EXEC master..xp_cmdshell @cmd (supposedly a very bad idea)

sp_start_job

app.LoadPackage (@ "\ servername \ sharename \ Package1.dtsx", null)

Thanks in advance for any help you can give me.

Answer 1

Well, there, Jack, what you listed is a Hijjuju of half-right answers.

SSIS. , , sp_start_job. , , SSIS.

- . , SQL Server (aka LOCAL SYSTEM), . , , . , :

CREATE CREDENTIAL MyCred WITH IDENTITY 'CORP\MyUser', SECRET = '<PassGoesHere>'
GO
sp_add_proxy @proxy_name='MyProxy', @enabled = 1, @credential = 'MyCred'
GO
sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy', @subsystem_id = 3
GO
sp_add_job @job_name = 'MyJob', @enabled = 1
GO
sp_add_jobstep 
    @job_name = 'MyJob', 
    @step_name = 'Run SSIS Package', 
    @subsystem = 'CMDEXEC', 
    @command = 'dtexec /F C:\Path\To\Package.dtsx', 
    @proxy_name = 'MyProxy'

:

• CREATE CREDENTIAL
• sp_add_proxy
• sp_grant_proxy_to_subsystem
• sp_add_job
• sp_add_jobstep

Answer 2

ASP.NET .NET, . , SSIS (SQLServer 2008), , NDA .

, Microsoft.SqlServer.Dts.Runtime.Wrapper, Package and Application DTSExecResult enum... .

Excel .

: , : Excel, , . - ASP.NET 3.5/Castle Project/SQLServer 2008. , , , "c:\UPLOAD". , SSIS SQL Server 2008.

(#):

Package package;
Application app;
DTSExecuteResult packageResult;

String packagePath = ""; // You have to get your physical path to your package.

app = new Application();
app.PackagePassword = "password"; // We have it here..
package = (Package)app.LoadPackage(packagePath, true, null);
packageResult = package.Execute();

, Microsoft.SqlServer.Dts.Runtime.Wrapper, .

, , SSIS-, .

, .