Are you using the same connection profile in all types of executions?(SP, SSIS, SP calling an SSIS)
Yes I am.
Aren't the SQL Procedures present in a different DB from the SSIS packages - which are normally in the 'SSISDB'. The account used in the connection profile for the SQL Stored Procedure needs to have permissions on all the required components (SQL stored Proc & the SSIS catalog). Not sure how you are able to see both the Store Procs & the SSIS packages from the same connection profile.
Did the DBA login to SSMS with the same account that is used in the connection profile for the SP?
Can you post the error from the job's output & the jobs log?
This is from our DBA...
The account has dbo permissions to the database in both DEV as well as PROD which allows full access within the scope of the database.
As well, the account has permissions to all SSIS packages pertaining to that database.
Yes – the DBA logged in via SSMS and was able to run all processes successfully under that account.
We are using SQL 2012.
On DEV, we don't get an error (it shows successful but nothing happens in SQL).
2 of 2 people found this helpful
In that case my suggestion would be to do the same testing - but modify only 1 item. Comment out the code in the Stored Proc that makes the connection to the SSIS catalog & triggers the SSIS. Just that part. And then run the same job to execute the same procedure. See if the result is any different.
One more test you can do is, take a SP that is successfully executed through Control-M. Add code to the SP to trigger an SSIS & rerun the Control-M job.
That should help in confirming if the SSIS trigger is causing the issue.
Thank you for the follow up. We did what you suggested and were able to discover that SSIS encryption was causing the issue. We resolved the issue. Thank you so much for your help!
Glad that you were able to figure out the root cause. Thanks for sharing the details.