SSIS Package Validation Error when Executed From Windows Task Scheduler

Question

I have deployed my SSIS package using the project deployment model to the SSIS catalog and experience a strange validation error when I try to execute the package. I believe this is due to a privileges issue or I may have configured our data import process wrong.

Let me give you an overview on the process:

• Windows Task Scheduler is being used
• Scheduled Task executes a long-running file download process (using Powershell)
• Scheduled Task triggers execution of the SSIS package (using Powershell and SMO)
• Scheduled Task is configured to run as a domain account (see privileges below)

Note: all these steps, including account creation, are automated using guidance from here.

Scheduled Task Account Privileges:

• Windows: Users Group
• SQL Catalog: Read on Catalog Folder, Read on Environment, Read and Execute on Project
• Destination Data Database: db_datareader, db_datawriter
• Source Data Database: db_datareader, db_datawriter

Steps To Reproduce The Error:

• Right-click scheduled task, choose 'Run'
• Package execution is triggered successfully
• Open SSMS, view Execution logs. Package execution immediately failed with a validation error "Cannot create task from XML for task "[taskname]", type "SSIS.ReplacementTask" due to error 0x80070057 "The parameter is incorrect."

While diagnosing this issue, I wanted to rule out privileges and ensure the account could see the SSIS project in the catalog, so I executed SQL Server Management Studio as my Scheduled Task account. The Package validated and ran to completion successfully.

Here's the bit that is very confusing.! After performing the above step (login to SSMS as Task Scheduler account), I could successfully trigger, validate and execute the scheduled task from the Task Scheduler!

I am now able to reliably replicate these steps:

1. Execution Fails
2. Login to SSMS As Task Scheduler Account
3. Execution Succeeds

Theory: the 'first time configuration' that occurs when launching SSMS as the Task Scheduler account is configuring user specific registry keys. I have confirmed that when I do not log in as my custom account, when the package tried to execute it complains that user specific registry keys are not found via Process Monitor.

My Questions:

• Should we be using the Task Scheduler instead of SQL Server Agent? I orignally chose Task Scheduler because the powershell download task was long running and needs access to network shares
• Can I trigger a package to execute from a Scheduled Task, but have it execute as a different account?

Thanks for any help you can offer - I'm quite confused!

Answer 1

I could not resolve this issue and changed my approach to utilize a SQL Job instead of running the package directly from a powershell script.

Summary of solution:

• SQL Job with one Job Step
• The Job Step executes the SSIS package

To run a SQL Job with a custom you must also create:

• an instance-level Credential
• a Proxy Account which should be mapped to the Credential

I still wonder what's different about this approach versus the 'interactive' approach listed above. Both approaches run with the exact same identity and privileges!

For an in-depth description of the steps required to configure a SQL Job, look at this example code for Automated Deployment of a SQL Job and Job Step.

Hope this helps someone else in the same situation -