March 6, 2018 at 10:08 am
Hi,
have an SSIS package that Just gets data from a table and puts it on the folder on the c drive on the SQL Server. Then it renames it (Yea, Month, hour, and minutes and some letters) and puts it on the and other folder on the c drive. I then have a Batch file that is run as a process task (the way I tried using a file system task and I will have the same issue) , to copy this file to an location on another server.
All this works great if I run it in SSIS on the server itself. I have it as a package on the SQL server, and it runs great from there too. Finally, I have a stored procedure that I run it all from, and if I run it on the Server itself, it runs great too.
However, if I run this from SSMS on my computer it will do everything but the part of moving it to another server, but again fully works if I run it in anyway from the server.
discovered this because I have VB.Net app that simply ran the stored proc and it ran everything except copy the file to the other server.
then tried it in SSMS from my computer and found the same thing.
I was thinking maybe it is a right thing but if so why would it run okay on the server, in any form.
Any ideas I would be really appreciated.
Thank you
March 8, 2018 at 8:32 am
itmasterw 60042 - Tuesday, March 6, 2018 10:08 AMHi,
have an SSIS package that Just gets data from a table and puts it on the folder on the c drive on the SQL Server. Then it renames it (Yea, Month, hour, and minutes and some letters) and puts it on the and other folder on the c drive. I then have a Batch file that is run as a process task (the way I tried using a file system task and I will have the same issue) , to copy this file to an location on another server.
All this works great if I run it in SSIS on the server itself. I have it as a package on the SQL server, and it runs great from there too. Finally, I have a stored procedure that I run it all from, and if I run it on the Server itself, it runs great too.However, if I run this from SSMS on my computer it will do everything but the part of moving it to another server, but again fully works if I run it in anyway from the server.
discovered this because I have VB.Net app that simply ran the stored proc and it ran everything except copy the file to the other server.
then tried it in SSMS from my computer and found the same thing.
I was thinking maybe it is a right thing but if so why would it run okay on the server, in any form.
Any ideas I would be really appreciated.Thank you
Execution context. I'm guessing whatever account it runs under on the server has access to the other server, while your windows login id perhaps does not. SSMS on your local machine will be using your Windows login as execution context, while the server may not be. Also, the SQL Server Agent Service has a service account that would need access if you run the package as part of an Agent job, as all Agent jobs run under the context of that service account,
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply