February 12, 2014 at 3:36 pm
Hi I have a query which runs perfectly in SSMS but doesn't run in a SSIS package using a Execute SQL Task. In both environments I am using SQL Server Authentication and running under the same account to same Server. I am accessing the system DB msdb here is the query for reference:
SELECT
sja.job_id,
sj.name, sjs.step_name,
sja.run_requested_date,sja.last_executed_step_id
FROM [msdb].[dbo].[sysjobactivity] sja
INNER JOIN msdb.dbo.sysjobsteps sjs
ON sja.job_id = sjs.job_id AND sja.last_executed_step_id = sjs.step_id
INNER JOIN msdb.dbo.sysjobs sj
ON sj.job_id = sja.job_id
WHERE job_history_id IN (
--latest run of each job
SELECT MAX(instance_id) FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobhistory AS sjh
ON sj.job_id = sjh.job_id
WHERE sjh.step_id = 0
GROUP BY sj.job_id)
--name of the SSIS package that failed
AND sjs.step_name = 'Job_Name'
The error I am getting on the SSIS side is '[Execute SQL Task] Error: Failed to acquire connection "SERVER.msdb.UserAccount". Connection may not be configured correctly or you may not have the right permissions on this connection.
I have tried dropping and regenerating - same result. I have tried windows authentication - works fine - but this really doesn't help me as I have to run using the SQL Server account. I am puzzled because it works fine in SSMS, but with same permission levels it fails in SSIS. I have checked that I haven't inadvertently inserted configuration using different credentials. When I set up the OLE DB Connection and test connection it works fine. Any help would be greatly appreciated.
February 12, 2014 at 11:09 pm
I tried to reproduce this but was not able to do so. Though you have mentioned it but could you please confirm once again that it works in SSMS with SQL authentication under same user context. And also there isn't any expression used for the conn mgr and no config as well .
February 13, 2014 at 8:06 am
Thanks very much for your response, and yes it works in SQL Server Management Studio with SQL authentication under same user and context. I haven't used any expression or config file on the connection manager.
Just to clarify did you get it to work in both SSIS as well as SSMS using SQL authentication?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply