October 20, 2023 at 4:44 pm
Taking an existing Visual Studio SSIS Project that targets SQL Server 2014 to now target SQL Server 2022 through the suggested course of the "Upgrade All Packages" has been unsuccessful. The reason being was we were trying to avoid having to recreate the project and packages from scratch. We are using Visual Studio 2019 version 16.11.20 and version 4.5 of the SSIS project template.
The existing project packages have numerous Script Tasks and Script Components that use a custom C# assembly for commonly used tasks. The assembly was recreated in a new C# project that targets .NET Framework 4.7.2 to use for SQL Server 2022 and that was originally targeting the .NET Framework 4.5 for use against SQL Server 2014. The assembly was installed to the GAC to be able to be used within the SSIS packages. Also, these scripts use an ADO.NET connection manager to access a database for logging purposes. These packages are for use for servers in the field that are not directly accessible through the Internet at our location. They also do not have an SSIS catalog. Because of this the SSIS packages are placed in the file system on these servers. One package is a parent that calls a child package through an Execute Package task. The parent package is started by a SQL Server Agent job.
For the first attempt to upgrade the SSIS project, the project was copied and edited to target SQL 2022. For the second attempt, a new SSIS project was created that targeted SQL 2022 from the start. The packages from the old project were copied to the new project and the Visual Studio utility to "Upgrade All Packages" was run. The existing Script Tasks were updated to target .NET Framework 4.7.2 and recompiled. This includes Script Tasks within the Control Flow and ones in Event Handlers for the package.
At this point, it seems there is intermittent successful behavior when the parent package is calling the child package. It appears that sometimes when the child package is called from the parent package, it is hanging in either the PreExecute and/or the OnError event handler for the child package. Otherwise, if the job is simply stopped and started again, with no set number of times doing so, the parent can successfully call the child package.
These packages were originally created to target SQL Server 2012 instances. They have been through a successful upgrade process to target SQL Server 2014 after the servers in the field were migrated from 2012 to 2014. At no time in the past did the packages manifest the behavior described when targeting either 2012 or 2014. It has been only after the upgrade to 2022 that this started occurring.
October 20, 2023 at 8:45 pm
Why not create an SSISDB? Takes just a few minutes. Deploy the packages there and you get a whole load of error logging 'for free', which might help you diagnose the issue.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply