Microsoft Download Centre Download Page
An updated for BOL also accompanied SP1, SQL Server 2005 Books Online - April 2006
A useful list of updated BOL topics, SSIS only of course, can be found on Simon's blog
The traditional method for checking the version of SQL Server installed is to query the value of the @@VERSION function, but if you have only have SSIS installed, you need a SSIS specific method. A very simple method is to check the File Version of the SSIS Service, C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.exe. Right-click the file and select Properties. On the Version tab check the File Version property and compare with the table below. RTM shows 9.00.1399.00, SP1 shows 9.00.2047.00
Strictly speaking the MsDtsSrvr.exe may not always be updated with a service pack or patch, so this version may reflect an older level. An alternative method is to check the registry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS\Setup, values Version and PatchLevel which give the service pack and hotfix level respectively. Current values are given in the table below.
| Version | @@VERSION | MsDtsSrvr.exe File Version | Registry Version | Registry Patch Level |
| Release to Manufacturing | 9.00.1399.00 | 9.0.1399.0 | 9.0.1399.06 | 9.0.1399.06 |
| Service Pack 1 | 9.00.2047.00 | 9.0.2047.0 | 9.1.2047.00 | 9.1.2047.00 |
| Post SP1 Cumulative Hotfix | 9.0.2153.00 | 9.0.2153.0 | 9.1.2047.00 | 9.1.2153 |
| Service Pack 2 CTP | 9.00.3027.00 | 9.0.3027.0 | 9.2.3027.00 | 9.2.3027.00 |
| Service Pack 2 | 9.00.3042.00 | 9.0.3042.0 | 9.2.3042.00 | 9.2.3042 |
| Service Pack 2 Rollup 3 | 9.00.3186.0 | 9.00.3186.00 | 9.2.3042.00 | 9.2.3186 |
Another place you can check the version of SSIS is through the designer, Visual Studio aka BIDS. See the BIDS page for an example, notice the version is shown in the Product Details box. This raises the point of workstation machines, do not forget to patch you clients. It is no good having a great server with all the fixes on if your tools and designers are out of date.
Microsoft have also recently published a KB article on finding the SSIS version which can be found here http://support.microsoft.com/default.aspx?scid=kb;en-us;942177&sd=rss&spid=2855
There is an issue with saved variables, see DateConversions for more information.
Component changes are not backwardly compatible. See below.
SSIS service not starting, "The SQL Server Integration Services service failed to start.... For more details, see KB918644 . Jamie has written about this in a blog post and you can also read the original newsgroup thread
The following components have been changed between the RTM release and SP1. Changes in these cases are an updated component Version number, and new properties.
| Component | RTM Version | SP1 Version | Change |
| DataReader Source | 1 | 2 | New CommandTimeout property. |
| SQL Server Destination | 3 | 4 | New MaxInsertCommitSize property. |
In practice this means that packages using these components edited on a SP1 machine will not work on previous releases, failing with errors such as those shown below.
Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException:
The version of component "DR_SRC Data" (14722) is not compatible with this version of the DataFlow.
[[The version or pipeline version or both for the specified component is higher than the current
version. This package was probably created on a new version of DTS or the component than is
installed on the current PC.]]
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(
IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
The component metadata for "component "DR_SRC Data" (14722)" could not be upgraded to the newer
version of the component. The PerformUpgrade method failed.
Component with name "DataReader Source" and class ID "{BF01D463-7089-41EE-8F05-0A6DC17CE633}" could
not be created because an error was encountered during its upgrade to the current version.
Contact information:
Extracts data from a relational database by using a .NET provider.;Microsoft Corporation;
Microsoft SqlServer v9; © 2004 Microsoft Corporation; All Rights Reserved;
http://www.microsoft.com/sql/support;2
There is a simple answer if you find yourself in this position, just rollback the change by hand within the package XML file.
To rollback a DataReader Source update there are two changes required, change the version and remove the property. An upgraded component can be seen below. Change version="2" back to version="1", and delete the CommandTimeout property, <property id="22912" name="CommandTimeout" ...>30</property> completely.
<component id="14733" name="DR_SRC LossVolume" componentClassID="{BF01D463-7089-41EE-8F05-0A6DC17CE633}"
description="Extracts data from a relational database by using a .NET provider." localeId="-1"
usesDispositions="true" validateExternalMetadata="True" version="2" pipelineVersion="0"
contactInfo="Extracts data...">
<properties>
<property id="14739" name="SqlCommand" dataType="System.String" state="default" isArray="false"
description="Specifies the SQL statement used by the component to extract data."
typeConverter="" UITypeEditor="Microsoft..." containsID="false" expressionType="Notify">DR_DST LossVolume</property>
<property id="14743" name="UserComponentTypeName" dataType="System.String" state="default" isArray="false"
description="" typeConverter="" UITypeEditor="" containsID="false" expressionType="None">...</property>
<property id="22912" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false"
description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out."
typeConverter="" UITypeEditor="" containsID="false" expressionType="None">30</property>
</properties>
...
</component>