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:
- Execution Fails
- Login to SSMS As Task Scheduler Account
- 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.
- 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!