Solved

Excel Upload - ASP.NET

Posted on 2010-08-18
7
381 Views
Last Modified: 2013-11-10
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
Comment
Question by:stretch73
  • 4
  • 3
7 Comments
 
LVL 16

Expert Comment

by:carsRST
ID: 33464712
>>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
 

Author Comment

by:stretch73
ID: 33464745
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
 
LVL 16

Expert Comment

by:carsRST
ID: 33464795
>>SET @fileName = 'C:\Sites\imports\userName_6242010.xls'

Use either the server name or IP address to reference it.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 16

Expert Comment

by:carsRST
ID: 33464807
Example:

If server name were "Server1"

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

0
 

Author Comment

by:stretch73
ID: 33464995
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
 
LVL 16

Accepted Solution

by:
carsRST earned 500 total points
ID: 33465103
>>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
 

Author Closing Comment

by:stretch73
ID: 33465112
I'll just have to play with the syntax, thank you for your help with this.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question