Solved

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

Posted on 2008-10-10
16
1,286 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
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 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
 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can I skip a node in XML? 9 36
SQL profiler 3 21
Need to replicate a Log table 4 14
T-SQL: problem comparing datetime 4 53
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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 setup several different housekeeping processes for a SQL Server.

696 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