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!
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.