March 23, 2011 at 2:14 pm
Hi,
Some SSIS packages were upgraded to visual studio 2008, without making backups.
When I run them in visual studio, everything is fine. When a SQL Server Agent runs them, I get a version error. My research leads me to believe that this is because we are using SQL Server 2005 and these are 2008 items.
My question is: Is there a way to build the SSIS packages so that the SQL Server Agent can run them or is there another way around this?
Thanks
🙂
March 23, 2011 at 2:56 pm
To the best of my knowledge, a 2008 SSIS package can not be run on a 2005 instance of SSIS. I'm assuming the problem is you can't open them back up in BIDS 2005 to save them back as the old format and redeploy them. I've heard that if a 2008 pacakge doesn't have any of the "new" features in it, you can open it in BIDS 2005. So if you can remove any items that were updated during the conversion from 2005 to 2008... you may be able to open the package in BIDS 2005 and add back what you removed.
To remove those updated items, you can open the package with a text editor (i like notepad++) and delete items directly from the XML. The trick will be determining which items contain the "new" features. I bet with some googling you could find that out though. search for what components changed between the versions.
Obviously before you delete anything out of the XML, make a backup copy. Its very easy to make a mistake. Not too sure if this will work, but may save you some time in redeveloping the entire package.
March 24, 2011 at 10:34 am
Thanks for your help.
March 25, 2011 at 5:50 am
I don't think that you will be able to open the packages with BIDS 2005 that easily. One of the changes that will have been made is the XML schema for the SSIS package. It will most likely have the 2008 schema.
If there is no SSIS 2008 functionality being used, you MIGHT be able to edit the XML for the package and change it to the equivalent 2005 schema name (you can get this from a working 2005 package - it is near the start of the package).
Make sure you do this on a copy of the package - directly editting the DTSX file is not supported and it is very easy to get it wrong. Getting it wrong will corrupt the package and it may not be all that easy to put things right.
March 25, 2011 at 9:37 am
GBimberg (3/23/2011)
Hi,Some SSIS packages were upgraded to visual studio 2008, without making backups.
When I run them in visual studio, everything is fine. When a SQL Server Agent runs them, I get a version error. My research leads me to believe that this is because we are using SQL Server 2005 and these are 2008 items.
It only took this happening to me once to pound home the value of keeping SSIS packages in source control. I had to recreate a package because I couldn't revert it to 2005 version.
Greg
March 25, 2011 at 9:50 am
I just got done creating a backup of every SSIS package, the powers that be here just found out about source control. :w00t:
March 25, 2011 at 11:41 am
happycat59 (3/25/2011)
I don't think that you will be able to open the packages with BIDS 2005 that easily.
So true. When you save a package in BIDS 2008, even the act of saving it changes the XML or the package metadata. It takes a lot more work than notepad to clean it up and you can seriously screw up the package by doing it this way.
If you have multiple versions of SQL Server, it's always best to work with a copy of the oldest version (not the original) so that if you have to revert, you can.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply