Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1292
  • Last Modified:

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

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
GaryRasmussen
Asked:
GaryRasmussen
  • 9
  • 7
1 Solution
 
Daniel WilsonCommented:
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
 
GaryRasmussenAuthor Commented:
Thanks,
The ports on the firewall are not open so you can't run SSMS remotely.
0
 
Daniel WilsonCommented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
GaryRasmussenAuthor Commented:
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
 
GaryRasmussenAuthor Commented:
why was my response all bold?  I didn't do that.
0
 
Daniel WilsonCommented:
>>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
 
GaryRasmussenAuthor Commented:
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
 
Daniel WilsonCommented:
>>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
 
GaryRasmussenAuthor Commented:
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
 
Daniel WilsonCommented:
>>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
 
GaryRasmussenAuthor Commented:
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
 
Daniel WilsonCommented:
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
 
GaryRasmussenAuthor Commented:
ok thanks,  Where would I find that link?
0
 
Daniel WilsonCommented:
In the block w/ your question itself -- right of your time zone & above the Translate droplist
0
 
GaryRasmussenAuthor Commented:
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
 
GaryRasmussenAuthor Commented:
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now