SSMS 2017 – Changing SSIS Package Version During Deployment

sql serversql-server-2012ssisssms

Steps taken, can be reproduced:

  • Develop SSIS package in SSDT 2012, project deployment model.
  • Deploy project to server A, SQL Server 2012. Package version
    internal.packages is 6.
  • Deploy project from server A to server B, SQL Server 2012. Package
    version internal.packages is 8 on server B.

If I deploy directly to both servers from SSDT 2012 there is no problem, but deploying from SSMS 2017 is bumping the version number.

Is there a way to stop that from happening during project deployment in SSMS 2017? Has anyone else encountered this?

Best Answer

I'm not surprised but haven't tested it myself.

Project deployment model packages are deployed using ISDeploymentWizard.exe and there is a version associated to each version of SQL Server (and bit-edness). For example, one laptop here has VS 2013/2015 installed and SSDT for 2012/2014/2016 installed. If I look for that executable, I see

  • C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\ISDeploymentWizard.exe
  • C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\ISDeploymentWizard.exe
  • C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\ISDeploymentWizard.exe
  • C:\Program Files\Microsoft SQL Server\120\DTS\Binn\ISDeploymentWizard.exe

My new machine only has bits for 2017 and looks like

  • C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\ISDeploymentWizard.exe
  • C:\Program Files\Microsoft SQL Server\140\DTS\Binn\ISDeploymentWizard.exe

The other thing to factor into all this is that using a newer version of the SSIS tooling (ISDeploymentWizard, dtexec, dtutil) will happily roll your version forward to match its version. Which is helpful as a consultant to upgrade things but the devil when you want to use new tools against old code.

My guess is that SSMS is defaulting to an ISDeploymentWizard that is not in the 120 folder. Unfortunately, I don't see a parameter in the deployment wizard to address version.

/Silent[+|-]                  Default value:'-' (short form /S)
/SourceType:{File|Server}     Default value:'File' (short form /ST)
/ModelType:{Project|Package}  Default value:'Project' (short form /MT)
/SourcePath:<string>          (short form /SP)
/SourceServer:<string>        (short form /SS)
/Packages:<string>            (short form /PK)
/ProjectPassword:<string>     (short form /PP)
/DestinationServer:<string>   (short form /DS)
/DestinationPath:<string>     (short form /DP)
/SkipSource[+|-]              Default value:'-'
@<file>                       Read response file for more options

Your best, yet icky, bets are to

  • Keep a copy of SSMS for SQL server 2012 lying about for deployment
  • Deploy from SSDT 2012
  • Deploy from the commandline explicitly defining the source exe

The last option is actually that bad. Use the GUI to click through the deployment but on this screen, stop

enter image description here

That highlighted box is selectable/copyable. DO THAT. The command line deploy has been built out for you (excluding the part that says "Command line:")

Thus you'd have something like

"C:\...\1XX\DTS\BINN\ISDeploymentWizard.exe" /SILENT /SourcePath:"C:\MyProject.ispac" /DestinationServer:"localhost" /DestinationPath:"/SSISDB/Folder/Project/MyProject"
Related Question