How to trigger a Windows Task scheduler to execute an SSIS package and Powershell script using Windows Service Account

I have created a task scheduler to execute a SSIS package using dtexec command from the action tab.
This SSIS package will in turn execute a powershell script from a batch file, to retrieve a zip file from SFTP server.

Now, when I try to execute this task using a windows user account with the following options selected,

1) Run only when user is logged on,
2) And not Run with highest privileges


I am able to execute the task without any error. However, my client wants to trigger this task through a "Service Account", so I modified the task to run using windows "Service Account" with the following options selected,

1) Run whether user is logged on or not,
2) Run with highest privileges


The task starts execution but hangs up while executing the powershell script.
The task neither ends nor throw an error.

I also tried with the below options selected, but it didn't help.

1) Run only when user is logged on,
2) And not Run with highest privileges


Note:- I have created this service account with local administrator permission.

Later, I gave read/write access to the service account for all the folders where I had kept my SSIS package, batch file, powershell script, powershell.exe file

But that didn't solve the problem.

Does any of you come across this error? if so please help me.

Kindly let me know if you need more information.

Regards,
Siva
LVL 1
jjozAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

McKnifeCommented:
Please start the powershell as that user via "run as different user" and see if manually executing your script from there works as expected.
0
jjozAuthor Commented:
Hello McKnife,

I tried to execute a powershell manually with my service account and yes, I was able to start a powershell process.

Start-Process Powershell -Credential "My Service Account"   --> I used this command to start the powershell manually.

Please let me know what could be an issue here.
0
McKnifeCommented:
Now set that task to run only when that user is logged on and logon as that user and see what happens.
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

jjozAuthor Commented:
hmm.. I selected the "Run only when user is logged on" option & unchecked the "Run with highest privileges" option and started the task. But the task failed with an error "Task Start Failed  Launch Failure" Error Value: 2147943645.

Just to let you know again, I am trying to run this job with "Windows Service Account".

When I run this job as normal Windows User Account, I am able to run it without any error.
0
McKnifeCommented:
So it's a real service account that you cannot use to login interactively?
0
jjozAuthor Commented:
Sorry, now I logged in with the service account and tried to run the task. The task ran successfully. However, the requirement is to run the task, even when the service account is signed off.

any clue?
0
McKnifeCommented:
Did you have to "support" the task while running, or did it run completely unattended? Did it need elevation?
0
jjozAuthor Commented:
The job ran on its own and stopped after completion.
0
McKnifeCommented:
Ok, please try a simple job like directory creation using the same task setup.
cmd /c md c:\test

and see if that directory gets created. If not, look at the task history and report back.
0
jjozAuthor Commented:
Yes, as you said I created a task to create a directory. The task created the directory when I ran the task with both options

Option 1 - Run only when the user is logged in
Option 2 - Run whether user is logged on or not and Run with highest privileges

However, when I try to run my real task with Option 2, the task hangs and does not end or throw any error.
0
McKnifeCommented:
Then we need to look at your script.
0
jjozAuthor Commented:
Please find attached the batch files and powershell scripts that I use.

1) On my task -> action tab, I have written "C:\transforms\IncrementalLoad.bat" on Program/script: (textbox) and on Start in (optional): (textbox), I have provided the path "C:\transforms\" - This batch file executes an SSIS package.

2) From SSIS package, it executes another batch file "Transfer_in.bat" - This file executes a powershell script "transfer_in.ps1"

3) Finally, this powershell script login to psftp and copy a zip file to my local folder.

Please let me know if you need more details.
IncrementalLoad.txt
Transfer-in-bat.txt
transfer-in-PS1.txt
0
McKnifeCommented:
I didn't look at the code yet but I think it could be due to the "A calling B calling C form" of the setup you have. I'd try to reduce it to one script.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jjozAuthor Commented:
hmm.. OK. Please let me know your update - Thanks :)
0
McKnifeCommented:
My update will be waiting for your update. I cannot understand your code that quick without being familiar with it. I hope you can try to squeeze it into one script.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.