2

We have very strange scenario, in fact don't understand how the SSIS execution flow is triggering the package tasks

well to summerize the issue, we have create a SSIS package to push data to Dynamics CRM instance, and we created a SQL job which inturn triggers the SSIS package (asumming will have a controlled environment). it's all works perfectly fine when we trigger the SQL Job from the SSMS environment. where by we do have functionality to trigger whenever user initiates the trigger action.

To perform the initial action, we use the following traditional command

EXEC MSDB.DBO.SP_START_JBO '{0}'

{0} token to supply the Jobname as configured from the app.config

When we execute this above code from the SSMS console, it does trigger and perform all the tasks inside the package without any hassle.

But, when trigger the job from a web page. SQL job triggers sucessfully yet we found only 3 tasks were completed out of 5 (in total). It skips the task which pushes the data to CRM using a utility (Kingsway Soft - a 3rd party plugin to push data to CRM)

We indeed checked all the permissions and app pool account executions and impersonation and yet the program fails to execute thoese 2 tasks when we trigger from a web page...

Any idea on this issue? can be really helpful for us to move forward.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Murali Ramakrishnan
  • 856
  • 7
  • 7
  • 1
    Do you have conditional or error flows that might skip the last 2 steps? If its a permission issue, the error should still be logged. – EzLo May 16 '18 at 11:35
  • Considering that you're running this under an agent task, then the steps themselves likely won't be run under the permissions of the Login that called the agent task; it'll be run under context of your SQL Server Agent Service Account. if that works in SSMS, and the job does start from the web page, I doubt it's permission issues. This means something else is going wrong. @EzLo is right, you need to check the logs and see what they say. I'd guess, like they have, you have different flow for when an error occurs; which is missing steps or stopping the job on error. – Thom A May 16 '18 at 11:40
  • @EzLo The reason i posted this issue in stack overflow, is because there is no error produced by the SSIS package execution all cleared the fence yet the 2 task were skiped from the package... but i mentioned eariler (if you run the same package from the SSMS studio then not issues at all, this issue only happens if this job is been triggered from the web interface using the SQL command i shared) – Murali Ramakrishnan May 31 '18 at 03:04

1 Answers1

0

Did you find any error message in the SQL Agent Job's History? Can you please check what the "On failure action" is in those three steps? Are they currently set to "Go to next step"?

In order to load and run a SSIS package remotely, please refer to the link here.

KingswaySoft
  • 852
  • 4
  • 10
  • No errors were produced by the SSIS package execution all cleared the fence. But, seems like the execution pipeline from the package is skipping these CRM task steps. – Murali Ramakrishnan May 31 '18 at 03:10
  • thanks for the link, but additionaly we also checked the following resolution as well https://support.microsoft.com/en-us/help/918760/ssis-package-does-not-run-when-called-from-a-sql-server-agent-job-step – Murali Ramakrishnan May 31 '18 at 03:13
  • Just to address this is not a permission issue, we did manage to replaced the KingswaySoft task (Dynamics CRM task) with a SSIS Out-of-Box (OLE DB Destination) task. Then if you trigger from the web interface it's all working fine (all the steps were executes)... – Murali Ramakrishnan May 31 '18 at 03:19