2

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?

|improve this question
3

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"
|improve this answer
  • Yeah, I was afraid of this, I didn't see any versioning options in the deployment wizard either. Thanks man, I'm not going down the command line road with this, it's just too error prone. I think for the moment I will just continue deploying from SSDT 2012 and look to get an SSMS 2012 install on this machine soon. Thanks for your time. – saarrrr Apr 12 '18 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.