Solved

Excel Upload - ASP.NET

Posted on 2010-08-18
7
383 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

736 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