Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Deploying SSIS package from SQL 2008 in SQL 2005

Posted on 2011-05-04
Medium Priority
Last Modified: 2013-11-10
Potentially silly question, so please no laughing :)

Up to now, I have only built DTS packages and have not ventured into the brave new world of SSIS.  That is about to change.

My question is, if I build an SSIS package using SQL2008, can I deploy that package on SQL2005, or will the package be incompatible?

If the answer is, "yes, they are compatible, but...", then please outline for me what the pifalls are, such as features that would not be supported, or extra steps I would need to take to ensure that backward compatibility.


Question by:Patrick Matthews
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 1000 total points
ID: 35689127
No, you can not host package to SQL Server 2005 those package are developed using SQL Server 2008.
LVL 61

Accepted Solution

Kevin Cross earned 1000 total points
ID: 35691730
Patrick, that is my understanding also.  Once a SSIS package has been upgraded to SQL 2008, it is a permanent upgrade; therefore, the supported answer is "NO".  If you like to live a little dangerously and try out some cool things, check out http://ssisdowngrade.codeplex.com/.  There are some caveats as noted by Simon Sabin.

There is an associated discussion that went on at SQL Server Central where user successful used this CodePlex tool, but had to edit the XML of the package directly to remove references in the package to the variable named "System::ProductVersion" and update the OLEDB provider to "SQLNCLI.1" versus the native client version for SQL 2008 which is "SQLNCLI10.1".
Ref.: http://www.sqlservercentral.com/Forums/Topic1046316-148-1.aspx

Hope that helps.
LVL 93

Author Comment

by:Patrick Matthews
ID: 35691953
Seeing as I will ultimately be deploying this to a customer, I think I'll choose not to live dangerously :)

Thanks all!
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 93

Author Closing Comment

by:Patrick Matthews
ID: 35691961
Great job gang!
LVL 61

Expert Comment

by:Kevin Cross
ID: 35692086
Glad we could help, sir!  I should have mentioned earlier, the alternative is to build the package in SQL 2005.  You can run those in SQL 2008 and it will do a temporary upgrade in order to execute the package.  This should allow you to have one package you can use on both your SQL 2005 and SQL 2008 server.
LVL 51

Expert Comment

by:Mark Wills
ID: 35695571
Depends somewhat on how you build your SSIS package.

If you need to do things on both then keep the SSIS stuff very very basic and simple and do the hard work by coding in TSQL / Stored Procedures and such like. Effectively use SSIS more like a scheduler and messenger. The recode the very simple part in SQL2005.

But what are you trying to do ?

Maybe write the TSQL to create the procedure, then to create a job, then use SQL Agent for scheduling and such like. There might be more than one way, not just the SSIS way.

But if it must be SSIS, then no, not without some effort.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question