Hello team,
I have SSIS package deployed on server A and this will invoke .bat file from server B. And before invoking it will update .bat file with latest file information from folder and then call.
Can anybody help me to know if this possible and if yes how? T.I.A
Last time I had to do something like this I used a Script task to make the modifications to the .bat file then to launch it on the remote server use PSEXEC.
Thank you for reply. Can you please share me sample command to invoke PSEXEC?
T.I.A
You can google it. It's part of Windows Sysinternals. I don't have it handy at the moment.
Hi All,
Here is complete scenario..
I have requirement to push the file to mainframe using sftp (tectia tool). Need to use .bat file and SQL Server Agent to schdule the .bat file.
SQL Server is installed on server A and tectia tool, source file (need to push) located on server B.
On Server A - I have created SSIS package and used Execute Process Task with following command
Executable C:\Windows\System32\cmd.exe
Arguments /c "server B name\bat file folder\master_sftp.bat"
then deployed the package on server A. Created SQL Agent job that call deployed package.
On server B (where tectia and push file is located) I have added Userkeys (pub keys) into profile of Service account ID which is used to run SQL Server Agent.
master_sftp.bat has following line
sftpg3 --user=idxyz.ab.org -B "server B name\bat file folder\child_sftp.bat"
and in child_sftp.bat has following lines
bin
put "server B name\file folder\abc.xlsx"
rename abc.xlsx abc_todaydate.xlsx
quit
when I run SQL Agent job I am getting below error sometimes
Executed as user: Service Account ID. C:\Windows\system32>sftpg3 --user=idxyz.ab.org -B "server B name\bat file folder\child_sftp.bat" 'sftpg3' is not recognized as an internal or external command, operable program or batch file. Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 5:09:46 PM Error: 2018-03-30 17:09:46.59 Code: 0xC0029151 Source: Execute Process Task Execute Process Task Description: In Executing "C:\Windows\System32\cmd.exe" idxyz.ab.org -B "server B name\bat file folder\child_sftp.bat" at "", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:09:46 PM Finished: 5:09:46 PM Elapsed: 0.219 seconds. The package execution failed. The step failed.
and sometimes getting error as
Executed as user: Service Account ID. Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 5:11:48 PM Error: 2018-03-30 17:11:49.15 Code: 0xC0029151 Source: Execute Process Task Execute Process Task Description: In Executing "C:\Windows\System32\cmd.exe" "/c "server B name\bat file folder\child_sftp.bat" at "", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:11:48 PM Finished: 5:11:49 PM Elapsed: 0.187 seconds. The package execution failed. The step failed.
But when tried to run .bat file manually in server B using command line then getting error as
C:\Windows\system32>\server B name\bat file folder\child_sftp.bat
C:\Windows\system32>sftpg3 --user=idxyz.ab.org -B "server B name\bat file folder\child_sftp.bat"
sftp> bin
File transfer mode is now binary.
sftp> put "server B name\file folder\abc.xlsx"
Error: Not connected.
sftp> rename abc.xlsx abc_todaydate.xlsx
Error: Not connected.
sftp> quit
Please help me out. what need to modify?
T.I.A
Moved ahead!!
I have resolved problem of not running .bat file on other machine. Now I want to use SQL Server Agent Job with different service account and run that .bat file. My SQL Server is still on machine A and .bat file is on machine B?
Can anyone please provide me how to create job that call .bat file?
T.I.A
why are you using sql server agent to run a bat file? are you doing anything that touches sql database at all in this process? or are you just using sql job for its ease of scheduling?
Just to ease of scheduling. I need to get options.. I know we can do it with windows scheduler task. But I want to have SQL Agent option also.
Please let me know the details to proceed.
T.I.A
you need to KISS it
Keep It Simple Snorky.
is the exe/bin for sftpg3 in the PATH environment variable of Server B?
Yes.
#1 so if you went to server B and ran
sftpg3 --user=idxyz.ab.org -B "server B name\bat file folder\child_sftp.bat
what happens?
#2 does Service Account have permissions to run things in Server B
#3 Can you login into Server B and try #1
I logged on to server B with service_account_B and ran .bat on command line and it worked perfectly. I pushed files to mainframe.
now I want to schedule this using agent job. This agent job is on server A with service_account_A. how to proceedd and complete this loop.
first we need to understand your complicated process before recommending on how to proceed.
so you have 2 different service accounts?? the plot thickens. in each post you surprise us with a new element you did not have in previous post.
what service account is sql server running as Server A
Hi,
Can we able to run .bat file remotely with different service account other than SQL Server Agent service account?
you are all over the place buddy. please focus ad answer the questions you are being asked?
what account is your sql server running as? is it a dedicated service account?
under what user is your SQL Job running as is it the ^ dedicated service account or someone other user?
here are
what account is your sql server running as? ==> service_account_A
is it a dedicated service account? ==> yes
under what user is your SQL Job running as is it the ^ dedicated service account or someone other user? ==>dedicated service account
T.I.A
1.does service_account_A have the necessary permissions in Server_B? ==> hmmm..not enough permission..might be the reason..
2.is the SQL Job running as service_account_A? ==> Yes.
T.I.A
there you go
since the SQL Job is running as service_account_A and calling stuff in Server_B, it will need the proper permissions n Server_B to do what you want it to do
For testing purposes ONLY, add service_account_A to the Administrators group in Server_B, apply changes and see if your process works.