Package that runs perfect on SSIS, but fails on SQL - server job activity

0
votes

I wonder if you can help me, I am having a problem which I can't figure out for the last few days... I get these errors when I run a package, that runs perfect on SSIS, on SQL - server job activity:

Error: There were errors during task validation

Error: .. failed validation and returned error code 0xC0208449

Error: One or more component failed validation

ADO NET source has failed to acquire the connection {...} with the following error message "exception from HRESULT: 0x80131937

Some additional info:

  • I am using project deployment mode

  • my Sql server version is 2014

  • My protection level in SSIS is DontSaveSensitives both in project and packages, but I think that it doesn't matter when I deploy anyway.

  • The package I get this error about run on SSIS but not on SQL-server job activity

1
sql-serverssisetl
It's hard to tell without knowing what this package does/which resources it tries to access. – Filburt
Are you running the SQL job in the context of the SQL Agent user? Most likely your Task user doesn't have the privileges to access the connection/resource you are trying to use. – Filburt
The package resource is MySQL. How can I check the Task user? – Liat Ferman
This is often caused by the session. In SSIS debug mode, you launch the package with your Windows account. But when the package is deployed to the SQL Server Agent, then it will be executed using the owner account of the job. So you need to make sure that the connection is still validated for that job owner. – Mincong Huang
The Task User is selected on the Job Step Properties dialog windows - the input is labeled "Run as:" (3rd input from top). – Filburt

1 Answers

1
votes

When runnin within Sql Agent Job, your package is failing to acquire connections. First review your package/job connections, and make sure they have deployed properly.

Then rule out access permissions issues by testing the package using a Sql Server user/password account. If it works this way then most likely you need to use a Sql Server Proxy account.

If you are still unable to access then check connectivity between the machine you are running the job and the one(s) where your data is located (addresses, ports and instances,..). Also make sure that all involved machines can use the same transport (named pipe, tcp,...) in Sql Server Configuration Manager.