Deploying an SSIS Solution - Target Server Version 2016 vs 2019

  • chris.o.smith

    SSCommitted

    Points: 1670

    We have a development environment set up with SQL Server 2019, and SSIS projects that are currently built using SSDT 2015 with a target server version of SQL Server 2016.  As we're looking to begin testing on the SS2019 box, I noticed that SSDT 2015 doesn't have a "TargetServerVersion" of 2019 - we'd have to jump to Visual Studio 2017 or 2019 for that.  However, I can still deploy the SSIS solutions to the SS2019 box without issue.

    I've been trying to find documentation about what I'm losing by not having "TargetServerVersion" set to 2019.  Are there certain feature sets that will be disabled, unforeseen incompatibility issues, etc? Can anyone point me to some documentation on this?

    Thanks for your time.

    • This topic was modified 5 months, 1 week ago by  chris.o.smith.
    • This topic was modified 5 months, 1 week ago by  chris.o.smith.
  • Site Owners

    SSC Guru

    Points: 80376

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • netmikem

    SSCarpal Tunnel

    Points: 4000

    Hi Chris.

    We are facing the same situation so I am wondering if you were able to find out anything on this topic?

    Regards,

    • Mike
  • chris.o.smith

    SSCommitted

    Points: 1670

    Hi Mike,

    Unfortunately I wasn't able to find any clear documentation online about the consequences of not setting TargetServerVersion to 2019. We tried keeping the target server version at 2017 even though the box was 2019, but our SSIS solutions would fail with "unexpected termination" error messages.  And there wasn't any other error information outside of that.

    So, we tried upgrading to Visual Studio 2017 and 2019 but still had no luck.  We continued to get the "unexpected termination" errors.  Again, there wasn't any other error information outside of that message.  I started rooting through the XML of our packages after they went through the upgrade wizard and noticed some funky stuff.  One example I described here, but I didn't hear back on that topic either.  I suspect something is going wrong during the package upgrade.  After reading some of the comments on the visual studio 2019 SSIS extension, it seemed like maybe things weren't ready for prime-time yet.

    We ended up spending about 2 weeks wrestling with this but were never able to resolve the issues.  So, we put the upgrade on the back-burner and decided to loop back to it some other time.  If you have better luck, please let me know!

  • netmikem

    SSCarpal Tunnel

    Points: 4000

    Thanks for the reply.

    In our situation, we have developers using VS 2015 and VS 2017, and we're using a TargetServerVersion of 2016 in our SSIS packages.

    We're about to upgrade our SQL Servers from SQL Server 2016 to SQL Server 2019.

    In our testing so far (which includes using the 2019 compatibility level on our dbs), as long as we keep the TargetServerVersion = 2016, we are okay.

    I would like to see us move to 2019 but it sounds like waiting for a bit would be wise.

    Regards

  • a.adames

    SSC Enthusiast

    Points: 182

    Hi Everyone! In my experience the only way to target Sql Server 2019 is thru VS2019. I installed it and tried to run my SSIS project targeting 2019 without luck. I have a Data Mining Model Training control in one of my packages and it didn't work. A "No such interface" error came up. The problem is with the SSDT that comes with VS2019. Then, I decided to stay with SS2016 and VS2017.

    Again, Microsoft stuff.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply