1

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:

  1. 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.
  2. 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.

2
  • Maybe the rowcounts are 0 because there is no data being inserted, updated, etc.? If it were a password issue, there would be an error with the decryption of the password for connections. If it were a permissions issue, there would be an error saying insufficient privileges or missing objects. Check for default values of 0 and silent catchs in your script task.
    – EzLo
    Jul 18, 2018 at 13:56
  • In this case I would expect an extracted rows count of 483 for today. I get that in debug but not in agent execution. I have truncated staging tables and the destination table to make sure there is data flow, and all is loaded ok.
    – DimUser
    Jul 18, 2018 at 14:09

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.