I have most of the components done for this but I'm looking for some best practice advice. We are working on some new functionality that allows users to upload an Excel spreadsheet into a SQL Server 2005 database. I've got the functionality in place to upload the file to the web server. I also have a stored procedure that calls an SSIS package that will load the records into the table if I specifically point it at the Excel spreadsheet. What I'm trying to figure out is should I upload the file to the database server or keep it stored on the IIS server and have the stored procedure point the package at the file on the IIS server. If I stick with keeping in on the IIS server, how do I point the stored procedure at the file on the IIS server?
DECLARE @cmd VARCHAR(1000)
DECLARE @ssispath VARCHAR(1000)
DECLARE @fileName VARCHAR(1000)
'the name of the upload file, changes dynamically based on user and date uploaded
SET @fileName = 'C:\Sites\imports\userName_6242010.xls'
'the name of the SSIS package
SET @ssispath = 'fileUploadPackage'
SELECT @cmd = 'dtexec /F "' + @ssispath + ' /SET \Package.Variables[User::fileName].Properties[Value];"' + @fileName + '"'
EXEC master..xp_cmdshell @cmd