SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Having a wired issus with SQl server and an SSIS package


Having a wired issus with SQl server and an SSIS package

Author
Message
itmasterw 60042
itmasterw 60042
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1083 Visits: 305
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
sgmunson
sgmunson
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75720 Visits: 6361
itmasterw 60042 - Tuesday, March 6, 2018 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

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)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search