?
Solved

Excel Upload - ASP.NET

Posted on 2010-08-18
7
Medium Priority
?
386 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

764 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