You are migrating your SSIS Packages from an old version SQL Server to a new SQL Server version, it worked perfectly fine before but once it was deployed on the new environment you get an error similar to this one.
Message Executed as user: YourDomainYourUser. Microsoft (R) SQL Server Execute Package Utility Version 13.0.1601.5 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 11:00:00 a.m. Error: 2017-01-26 11:00:00.00 Code: 0x00000001 Source: YourSSISTaskName Description: Cannot load script for execution. End Error Error: 2017-01-26 11:00:00.00 Code: 0x00000001 Source: YourSSISTaskName Description: Cannot load script for execution. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:00:00 a.m. Finished: 11:00:00 a.m. Elapsed: 0.800 seconds. The package execution failed. The step failed.
Thats the exact issue I had yesterday and I can’t find a solution online but I figured out the way to do it so here I am sharing it to you as you might encounter this in the future. At least the error gives me where it threw an exception so I know where to it might came from. The culprit was from a script task, from there I checked if any code was deprecated, I then checked the references if it is existing on the server, then I found out it was due to version differences. On my development environment the reference to Microsoft.SqlServer.ManagedDTS was pointing to
and on the server this is not existing, it only have
You can point to that reference manually by deleting the old one and replacing it with new one but the best and proper way to do it is change your project property under General Configuration Properties to target your version of SQL Server
It will then warn you that you need the extensions for the SQL Server version you intend.
Click yes, compile then deploy.
Your solution will now run again like it did on your old server.