How to force SSIS deployement manifest files to us

Question

I have an SSIS package developed in Business Intelligence Development Studio (BIDS) 2005 and the target deployment server is also SQL Server 2005.

However, on my development machine i also have full installations of both SQL Server 2008 and 2012 installed.

I have created an Integration Services Deployment manifest to install the package. The problem is, during the installation the package installation wizard is upgrading my package from PackageFormatVersion 2 to PackageFormatVersion 6 and version 6 is not supported on the production server.

My guess is that, the Integration Services Deployment manifest is using the DTSInstall.exe file for SQL Server 2012 instead of the DTSInstall.exe for SQL Server 2005.

If i open the .dtsx file in a text editor before running the package installation utility, i have below values

<DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property>

and then after running the package installation utility, 2 is changed to 6 as below

<DTS:Property DTS:Name="PackageFormatVersion">6</DTS:Property>

That change makes it fail to run on the target production server. Is there away i can ensure that the PackageFormatVersion remains 2 after running the package installation utility?

See the difference in the screenshoots below. In the first screenshot, the SSISDeploymentManifest file opens with specifically SQL Server 2005 integration services package installation utility while in screenshot 2 it opens with just generic SQL Server integration services package installation utility.

How can i specifically select sql server 2005 in screenshot 2 the way it is in screenshot 1.

Answer 1

The executable, DTSInstall.exe, is going to be associated to .ssisdeploymentmanifest There's nothing in that file to provide insight as to which installer should be used. Heck, MS even calls this out in the above

Depending on the version of the package that you are deploying, you might encounter an error if you have different versions of SQL Server installed side-by-side. This error can occur because the .SSISDeploymentManifest file name extension is the same for all versions of Integration Services. Double-clicking the file calls the installer (dtsinstall.exe) for the most recently installed version of Integration Services, which might not be the same version as the deployment utility file. To work around this problem, run the correct version of dtsinstall.exe from the command line, and provide the path of the deployment utility file.

From a command line, type where dtsinstall.exe

C:\Users\bfellows>where dtsinstall.exe
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\dtsinstall.exe
C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtsinstall.exe
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtsinstall.exe
C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtsinstall.exe
C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtsinstall.exe
C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtsinstall.exe
C:\Program Files\Microsoft SQL Server\120\DTS\Binn\dtsinstall.exe
C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\dtsinstall.exe

That will be all the gui installers in my network path. If I want to run a specific one, like you want the 2005 version, your options become one of the following

1. I'd need to launch it from the command line like "C:\Program Files\Microsoft SQL Server\90\DTS\Binn\dtsinstall.exe" "C:\path\to\project.ssisdeploymentmanifest"

2. Edit your environmental Path such that "SQL Server preferred version" shows up first

3. Use something else, like a PowerShell SSIS Deployment and Maintenance script