CODEWITHSUNDEEP
sql-serverssissql-server-2008-r2sql-server-data-toolssql-server-2017
SKLTFZ
SKLTFZ

Reputation: 950

Why do i need to upgrade SSIS packages?

It seems the backward compatibility of SSIS covered from SQL server 2008R2 to SQL server 2017 (since an instance with SQL server 2017 installed can proceed SSIS package with PackageFormatVersion = 3)

The question is why do we need to update the SSIS package (.dtsx)

Is there any performance boost or other necessaries by updating the SSIS package?

Upvotes: 2

Views: 2031

Answers (2)

billinkc
billinkc

Reputation: 61269

The SSIS execution engine can run packages that are current version or older. That's been a feature since the second release.

Why then should we upgrade SSIS packages from an older version to the current version since the execution engine can run them "as is?" I can think of a few reasons why you would want to do this.

Execution performance

The SSIS engines reads the XML that is an SSIS package from disk. It identifies that the package version does not match the runtime engine version so before it can do anything else, it must first upgrade the in-memory representation of that package to the current version. Package execution can then commence. Package completes and then it throws away all the work it did to upgrade to current version. Maybe that takes a picosecond, maybe it takes a minute for that upgrade to occur. You'll be paying that penalty for each and every package execution.

With SSIS, the addition of a second or two and the ensuing CPU usage for upgrade may not factor much into the overall load on the server as package run times usually measure in minutes if not hours, but I always believed in being a good steward of my resources.

New Features

As Yahfoufi mentions, there's lots of new features packed into the varying releases. I find the leap from 2008 to 2012 especially compelling as the move from the Package Deployment model to Project Deployment model is exceptionally compelling as logging, configuration are automagic and execution from SSMS is easily accomplished.

Upvotes: 4

Yahfoufi
Yahfoufi

Reputation: 2542

It is highly recommended to update packages because each new release of SQL Server contains new features, bug fixes, performance improvement. There are many articles describing the features of each release as example:

  • in SQL Server 2016 package parts was founded which guarantee high re-usability
  • ODATA components were added later than 2008 R2

You can refer to the following pages for more information:

Also not that the support of SQL Server 2008 R2 will end soon (July 9, 2019)

Upvotes: 2

Related Questions