?
Solved

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

Posted on 2014-09-01
15
Medium Priority
?
2,536 Views
Last Modified: 2016-11-15
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
0
Comment
Question by:jjoz
  • 8
  • 7
15 Comments
 
LVL 58

Expert Comment

by:McKnife
ID: 40298001
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
 
LVL 1

Author Comment

by:jjoz
ID: 40299937
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
 
LVL 58

Expert Comment

by:McKnife
ID: 40300047
Now set that task to run only when that user is logged on and logon as that user and see what happens.
0
Who's Defending Your Organization from Threats?

Protecting against advanced threats requires an IT dream team – a well-oiled machine of people and solutions working together to defend your organization. Download our resource kit today to learn more about the tools you need to build you IT Dream Team!

 
LVL 1

Author Comment

by:jjoz
ID: 40300068
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
 
LVL 58

Expert Comment

by:McKnife
ID: 40300356
So it's a real service account that you cannot use to login interactively?
0
 
LVL 1

Author Comment

by:jjoz
ID: 40300370
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
 
LVL 58

Expert Comment

by:McKnife
ID: 40300465
Did you have to "support" the task while running, or did it run completely unattended? Did it need elevation?
0
 
LVL 1

Author Comment

by:jjoz
ID: 40302411
The job ran on its own and stopped after completion.
0
 
LVL 58

Expert Comment

by:McKnife
ID: 40302742
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
 
LVL 1

Author Comment

by:jjoz
ID: 40302766
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
 
LVL 58

Expert Comment

by:McKnife
ID: 40302807
Then we need to look at your script.
0
 
LVL 1

Author Comment

by:jjoz
ID: 40305003
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
 
LVL 58

Accepted Solution

by:
McKnife earned 2000 total points
ID: 40305228
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
 
LVL 1

Author Comment

by:jjoz
ID: 40305310
hmm.. OK. Please let me know your update - Thanks :)
0
 
LVL 58

Expert Comment

by:McKnife
ID: 40305328
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
This Micro Tutorial will give you a basic overview of Windows Live Photo Gallery and show you various editing filters and touches to photos you can apply. This will be demonstrated using Windows Live Photo Gallery on Windows 7 operating system.
Loops Section Overview
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question