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.dts
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,
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.dts
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,
Daniel Wilson
Create a job that runs the package under SQL Agent. Then have your users connect to the server w/ SSMS and execute the job.
ASKER
Thanks,
The ports on the firewall are not open so you can't run SSMS remotely.
The ports on the firewall are not open so you can't run SSMS remotely.
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?
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?
ASKER
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,
If so, how does one write such a batch file?
Thanks,
ASKER
why was my response all bold? I didn't do that.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
>>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?
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?
ASKER
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?
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?
>>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.
No, I'd be going to Google. I'm just pretty sure this is the right architecture.
ASKER
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?
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?
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!
ASKER
ok thanks, Where would I find that link?
In the block w/ your question itself -- right of your time zone & above the Translate droplist
ASKER
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.
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.
ASKER
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.