SQL Server Integration Services SSIS 2005 - How to Let Users Run the Package?

Question

So I made a package in SSIS to read data in from a text file and load it into a database table.

What's the best way to set this up for non technical end users to run this when desired?

My boss was thinking to have a SP launch it, and then have a report made in reporting services launch the stored procedure. Surely there's a better way though!

Answer 1

ASP.NET is a good solution: http://www.codeproject.com/KB/aspnet/SSIS_DOTNET.aspx

Answer 2

From the command prompt:

DTEXEC.EXE /F "C:\YOUR_PACKAGE.dtsx"

Full syntax here: http://msdn.microsoft.com/en-us/library/ms162810.aspx

Answer 3

You could also create a SQL Agent Job to run the package with an empty schedule. Then create a web front end to call EXEC msdb.dbo.sp_start_job N'Your SSIS Job Name';

Answer 4

It's not entirely straightforward, and comes with a number of health warnings, but it is possible to configure as SSRS report to use an SSIS package as a datasource - see http://msdn.microsoft.com/en-us/library/ms345250.aspx.

With this enabled, you could add an extra step to your SSIS package to output one (or more) report datasets to an SSRS report.