[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel Upload - ASP.NET

Posted on 2010-08-18
7
Medium Priority
?
388 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

649 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