I've been reading and trying to figure out the best way to accomplish this, but so far I haven't been able to do so.
In SQL Server 2000, we have a stored procedure that executes a DTS package. This DTS package imports data from an Excel spreadsheet to a table. This stored procedure uses a proxy account and sp_cmdshell like this:
-- Run the DTS package using the proxy account
EXEC master..xp_cmdshell 'dtsrun /sMyServer /UMyProxyAccount /PMyProxyAccountPassword /NMyDTSPackage', NO_OUTPUT
That is working perfectly in SQL Server 2000. However, we're in the process of migrating to SQL Server 2005. I've created a SSIS package in SQL Server 2005 that is supposed to import the same data to the 2005 table. I've saved the SSIS in the SQL Server. Nevertheless, I don't know the best way to call this SSIS package from a 2005 stored procedure. One issue is that other staff members will have to execute this stored procedure and they do not have administrative permissions. I'm hoping the solution would be as simple as in 2000, but I am not sure.
Any ideas on how to accomplish this?