Within an SSIS project I have a number of usp's that I execute to store row counters, like extracted row count, inserted row count, etc., during an ETL process.
My SSIS project is setup to run via an agent job.
The package runs ok, without any errors, but logs 0 for all my row counts.
When I run the project in debug mode everything logs ok, and the counters are correct.
I therefor presumed it was a permissions issue for the agent job, but everything is being executed ok and the counter logs are being written to, they just aren't being updated.
I ran a trace (forgive me, it was quicker) against the agent job execution and can see that the sp calls are being execute with 0's being passed in. So the agent job, sp's and ssis project are doing what is being asked of them.
Two key points to make:
- my row count variables bubble up to a parent package via a script task, and the parent package executes the sp's to log the row counts. But as this works in debug mode, I think this part is ok.
- I have set the property ProtectionLevel to EncryptSensitiveWithPassword. This is the first time I've used this property value, and I think it may be stopping the code in the script task from running, when the project is being executed from the agent job.
Can anybody offer some advice on this? Why would the agent job execution not log the row counts, but debug mode would? If this property setting is preventing the script task from running, how do I enable this?
Thanks in advance for any help you can offer.
EDIT:
I don't know if this is of any relevance but this project was built on a dev server and deployed across to the live server, where the SSISDB is and the agent job executes.
Best Answer
What this appeared to boil down to was the TargetServerVersion property for the project. What I hadn't realised was, where SSDT had been upgraded, the TargetServerVersion defaults to 'SQL Server 2017', whereas my target instance is 2016.
I originally tried to "downgrade" the target server version in the original project, but was still getting the same problem. I was also getting some informational messages about the script task in the child package, saying that something hadn't gone as planned when "downgrading". I eventually recreated the entire project with the TargetServerVersion set to 2016 from the beginning. This has done the trick, as all is working as it should.
The only other thing I changed was that I deployed the project using SQL Server 2016 Deployment Wizard, instead of 2017 as before. However, I'm not too sure this would have been the problem.
Moral of the story, always check your TargetServerVersion.
I have provided this as the answer, in case other people experience such an issue. If, in future, any evidence presents itself to indicate that the above was not the cause of the issue, I will gladly change the answer.