• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

Excel Upload - ASP.NET

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
0
stretch73
Asked:
stretch73
  • 4
  • 3
1 Solution
 
carsRSTCommented:
>>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
Really not a best practice on this.  I would go with performance and space issues.


>>If I stick with keeping in on the IIS server, how do I point the stored procedure at the file on the IIS server?
It appears you're passing the file name to a variable within your SSIS package.  Just need to make sure your SSIS package (what ever account it runs under) has permissions to the IIS server/folder to where the Excel file resides.  If it does, then shouldn't be an issue.  

0
 
stretch73Author Commented:
cars, thanks for your response.

Not following you on this:
Really not a best practice on this.  I would go with performance and space issues.

I plan on deleting the file after the records are imported but I'm not sure if the storage was what you were referring to.

I can arrange for the permissions, pretty sure the folder already has them on the IIS server, what I'm really looking for is how to incorporate the server name into this line:

SET @fileName = 'C:\Sites\imports\userName_6242010.xls'
0
 
carsRSTCommented:
>>SET @fileName = 'C:\Sites\imports\userName_6242010.xls'

Use either the server name or IP address to reference it.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
carsRSTCommented:
Example:

If server name were "Server1"

SET @fileName = '\\Server1\Sites\imports\userName_6242010.xls'

0
 
stretch73Author Commented:
Okay, I think it's close but it's not digging this:

SET @ssispath = 'fileUploadPackage'

The package is stored in the file system folder, is there a specific syntax for how to reference that.  Elementary I'm sure, still getting a feel for SSIS.
0
 
carsRSTCommented:
>>The package is stored in the file system folder, is there a specific syntax for how to reference that.  
If possible, can you log in to the server that will run the SSIS package?  Then see if you can browse to the folder holding the package, copy out the path, and put to your @ssispath variable, along with the package name.

Something like this (as an example):
SET @ssispath = 'c:\SSIS\\dts\packages\fileUploadPackage.dtsx'




0
 
stretch73Author Commented:
I'll just have to play with the syntax, thank you for your help with this.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now