I recently developed an SSIS package on my dev machine using the 2008 version. Later I foud the customer had Sql server 2005 and doesn't plan to upgrade anytime soon.
Is there a way to turn the 2008 package into a SSIS 2005 package, like a downgrade wizard?
Unfortunately, I do not think this is possible. I know for a fact that if you have any script tasks and/or components in your package, the DLLs will be incompatible when downgrading to SSIS 2005 since 2008 uses .NET 3.5 Framework and 2005 uses the 2.0 Framework.
There may be components and properties available on 2008 that are not available on 2005. This is what makes the conversion so difficult.
However if you package is simple and fairly standard then the following might help. It worked for an empty package. I created one on 2008 and then attepted to open it in 2005. I got errors (of course). So I then copied the 2008 version and opened it in a plain text editor.
I then manually edit the file
1. Change the following
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="SSIS.Package.2">
to
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="SSIS.Package.1">
2. Change the following
<DTS:Property DTS:Name="PackageFormatVersion">3</DTS:Property>
to
<DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property>
3. Remove the following:
<DTS:Property DTS:Name="LastModifiedProductVersion">10.0.1600.22</DTS:Property>
4. Change the following:
<DTS:Property DTS:Name="CreationName">SSIS.Package.2</DTS:Property>
to
<DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property>
This works for an empty package, but I can make no guarantees for any other. # Hope this helps,
James
In addition to what James did you need to find and replace all instances of Version=10.0.0.0 with Version=9.0.242.0 for sql tasks, SSIS.PipeLine.2 with DTS.Pipeline.1 for dataflow tasks, componentClassID="{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0} with componentClassID="{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0} for OLE DB Source tasks, componentClassID="{5A0B62E8-D91D-49F5-94A5-7BE58DE508F0}" with componentClassID="{E2568105-9550-4F71-A638-B7FE42E66922}" for OLE DB Destinations, componentClassID="{BD06A22E-BC69-4AF7-A69B-C44C2EF684BB}" with componentClassID="{C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}" for data conversion tasks and componentClassID="{70909A92-ECE9-486D-B17E-30EDE908849E}" with componentClassID="{7C0CACA3-B152-40B1-8379-E37AE8E1DD5A}" for slowly changing dimesion task. I did not manage to get lookup tasks to work
Hopes this helps someone, at least it is a reference to me for next time :-)
Ásgeir Gunnarsson
In addition to Asgeir:
The correct ClassID for OLE DB sources in 2008 version is {BCEFE59B-6819-47F7-A125-63753B33ABB7}. This has to be changed to {2C0A8BE5-1EDC-4353-A0EF-B778599C65A0} to be used in 2005.
For lookup tasks change {27648839-180F-45E6-838D-AFF53DF682D2} into {0FB4AABB-C027-4440-809A-1198049BF117}. Furthermore you have to change the term 'version="5"' into 'version="4"' in the line below the ClassID.
For conditional split task change {3AE878C6-0D6C-4F48-8128-40E00E9C1B7D} into {53A228EE-EBFA-48D6-A1AC-5269E5824A2C}.
These changes in addition to the ones already posted should do the trick for most users or at least make it possible to rebuild the package in 2005 version with less effort.
You'll also want to replace the following:
Even so, you'll have to open it in BIDS 2005 and resave it. You can install BIDS 2005 stand alone by going into your SQL Server 2005 CD tools\setup directory and running sqlRun_Tools.msi. If that errors out you can go up a level (tools dir) and run setup then choose just BIDS.