Solved

How can I remotely run a dtsx file that is local to the SQL Server

Posted on 2008-10-10
16
1,276 Views
Last Modified: 2013-11-10
I have a dtsx file saved to a shared folder local to the SQL Server.  If I RDS into the SQL Server, browse to the file and double-click it, I get the Execute Package Utility window where I can click Execute and it works perfectly.

I want to allow one of my users to execute this file from their PC without having them RDS into the SQL Server.  I tried browsing to the file remotely and double-clicking \\SQLServer\Share\file.dtsx but that just brings up the local Execute Package Utility window and executing the file from there does not work.

I read something about using a batch file.  Has anybody been able to do this and if so, could you please instruct me how to do it?

Thanks,
0
Comment
Question by:GaryRasmussen
  • 9
  • 7
16 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22689964
Create a job that runs the package under SQL Agent.  Then have your users connect to the server w/ SSMS and execute the job.
0
 
LVL 1

Author Comment

by:GaryRasmussen
ID: 22690215
Thanks,
The ports on the firewall are not open so you can't run SSMS remotely.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22690416
I see ... in that kind of environment opening a file share is probably not available anyway ... though I don't see that working anyway.

A batch file would have to run on the SQL server -- so you're back to the problem of access.

Would you consider putting a web server on the SQL machine & having an ASPX page run the job?
0
 
LVL 1

Author Comment

by:GaryRasmussen
ID: 22690905
The file is located in a shared folder on the SQL Server.  That folder is shared and I can click the dtsx file remotely using VPN but it wants to execute from my machine rather than from where it resides.  I just need it to run from there and not try to run from the remote computer.  If a batch file can launch the dtsx package, then couldn't I also leave the batch file on the share and just have the user double-click the batch file?

If so, how does one write such a batch file?

Thanks,
0
 
LVL 1

Author Comment

by:GaryRasmussen
ID: 22695109
why was my response all bold?  I didn't do that.
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 22701799
>>I tried browsing to the file remotely and double-clicking \\SQLServer\Share\file.dtsx but that just brings up the local Execute Package Utility window and executing the file from there does not work.

In this scenario, the actual execution of the program (DTS package) occurs on your client computer, not the server.

>>If a batch file can launch the dtsx package, then couldn't I also leave the batch file on the share and just have the user double-click the batch file?

The batch file doesn't change the limitation above.

You have to do something to get it to execute on the server.  You have been connecting by RDP -- works, but not acceptable for the users in question. Setting up a SQL Agent job would also work, but again is not acceptable for the users in question.

That's what I suggest hosting a web site for this purpose only on the server.  You would put a script there that would be run when the user goes to that web page.  That would put the execution on the server.
0
 
LVL 1

Author Comment

by:GaryRasmussen
ID: 22704468
So the only way to do this is to build a website on the SQL Server?  Seems like alot of work just to be able to launch a single SSIS package.  If I do go ahead and build a website for just this reason, how do I run the package in C#/ASP.NET and how do I return the results to the user that runs the package like the Execute package utility does.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22704574
>>So the only way to do this is to build a website on the SQL Server?

That's the only way I can think of.  Of course, you could custom-develop a client-server system ... but when a proven client-server system like a web browser and IIS already exists, building a web page sounds a lot easier.

>>how do I return the results to the user that runs the package like the Execute package utility does.

How does the package return results?  
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:GaryRasmussen
ID: 22704669
I don't know how the package returns results.  If I run it using the Execute Package Utility or at the command line, I see a page of validation steps, and processes that started and completed and at the bottom it says something like completed 100% Succcessful.  That would be good to let the user know if they are going to run the package from a web page.

Where this comes from I don't know.  Do you have any asp.net code that gives an example of doing this?  If you do not, don't go out a look for some as I can do that.  I just figured that since you suggested this method, you have probably done it before and probably have some code?
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22711088
>>Do you have any asp.net code that gives an example of doing this?

No, I'd be going to Google.  I'm just pretty sure this is the right architecture.
0
 
LVL 1

Author Comment

by:GaryRasmussen
ID: 22713400
Thanks Daniel,

This is not a small task.  You have to setup a website and you have to setup everything so that it uses .NET 2.0 (don't hose any other 1.1 applications on the SQL Server).  Then you need VS2005 or VS 2008 because if you are like me and only have VS2003, you can only develop for .NET 1.1.  Then you get to deal with ther security and permission problems that accompany running a DTS package from an ASP.NET page.

Does anybody have any other idea?
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22713590
To get others' attention, your best bet will be to click the Request Attention link.  If there's a nicer way to handle it, I'd like to know too!
0
 
LVL 1

Author Comment

by:GaryRasmussen
ID: 22713632
ok thanks,  Where would I find that link?
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22713680
In the block w/ your question itself -- right of your time zone & above the Translate droplist
0
 
LVL 1

Author Comment

by:GaryRasmussen
ID: 22714927
Thanks Daniel.  I finally got all the kinks worked out and was able to develop an asp.net web page that is able to do all of this.  Looking back, the end result is quite simple but getting here was kinda a PITA.  I was lucky in that I am a developer and I was able to do all of this on my own.

The hardest part was moving from .NET 1.1 to 2.0.  There was settings in IIS, setting up the site to use 2.0 without affecting the other sites that must use 1.1.  Then there was transitioning myself from VS2003 to VS2008 over night.  Oh and I had to recreate the SSIS package in different ways until I found one that would play well between ASP, IIS, and SQL.
0
 
LVL 1

Author Closing Comment

by:GaryRasmussen
ID: 31505145
While this is a solution, it was "way" more work than what I was looking for.  But I guess you gotta do what ya gotta do sometimes so that you can move on.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now