We all know that Microsoft has introduced new feature/component in SSIS 2008. They also renamed few system procedures for SSIS.

I was working on a SQL Server 2008 upgrade project that included SSIS, but the requirement was to hold our SSIS upgrade until the Production upgrade was completed. Therefore, it was important to provide the ability to developers to design and develop their SSIS packages using Visual Studio 2005 but store the packages in SSIS 2008 storage in order to

1. Finish the nonproduction servers upgrade
2. Do not let developers to use SSIS 2008 features until the production server was upgraded so that any package under development can go live (if needed) on the existing SQL Server 2005

I encountered bunch of errors while saving the package that I had opened in Visual Studio for some changes and later trying to save that package in SSIS 2008 storage. Below is the complete scenario that explains what error messages occurred and how to resolve them.

Scenario

An SSIS Package (SaveSSIS2005_on_2008.dtsx) is designed/developed using Visual Studio 2005. The package needs to be saved on a SQL Server 2008 Server / SSIS 2008 Package Store. Visual Studio 2008 can not be installed/provided due to project requirements.

In order to simulate above scenario, open package in Visual Studio 2005 and choose Save Copy of <packagename>.dtsx As to save on an SSIS Server.

Enter the Package Location, Server Name and then try accessing Package Path. You will get following error message:

The stored procedure in question (highlighed) has been renamed in SQL Server 2008. Now the challenge is to deliver a solution to the customer that should enable them to use Visual Studio 2005 while using SQL Server 2008 and the SSIS 2008 storage. The solution is included below.

Step 1:

Create following procedure on SQL Server 2008:

USE [msdb]
GO
/*************************************************************************

Summary : Visual Studio 2005 usage this procedure to enumerate SSIS packages
Author : Mohan Kumar
Created : 06/08/2009
Dependencies: msdb.dbo.sp_ssis_listpackages
Caution : This is just a workaround 
**************************************************************************/CREATE PROCEDURE [dbo].[sp_dts_listpackages]
 @folderid uniqueidentifier
AS
 exec sp_ssis_listpackages @folderid

Now the Save Dialog displays a different message as shown below:

Step 2:

Create following procedure in MSDB on SQL Server 2008:

CREATE PROCEDURE [dbo].[sp_dts_listfolders]
 @parentfolderid uniqueidentifier
AS
 exec sp_ssis_listfolders @parentfolderid

Now you will be able to see package path:

Click OK, following screen will appear:

Caution: Remove / and .dtsx from package path otherwise following error message will be prompted:

All right, don't be relaxed because few more error messages to be handled before you can relax. Because it will prompt following error message now when you will Click OK on "Save Copy of Package" dialog:

So, now.....

Step 3:

Create following procedure in MSDB on SQL Server 2008:

CREATE PROCEDURE [dbo].[sp_dts_checkexists]
 @name sysname,
 @folderid uniqueidentifier
AS
 exec sp_ssis_checkexists @name, @folderid

So, now package existence checking part handled. What next..... oops another error message:

If you are wondering, how many more error message you need to encounter then don't worry, Above procedure (highlighted in error message) is final step (internally) to save SSIS package. So, just follow one more step

Step 4:

Create following procedure in MSDB on SQL Server 2008:

CREATE PROCEDURE [dbo].[sp_dts_putpackage]
  @name sysname
 ,@id uniqueidentifier
 ,@description nvarchar
 ,@createdate datetime
 ,@folderid uniqueidentifier
 ,@packagedata image
 ,@packageformat int
 ,@packagetype int
 ,@vermajor int
 ,@verminor int
 ,@verbuild int
 ,@vercomments nvarchar
 ,@verid uniqueidentifier
AS
 exec sp_ssis_putpackage 
  @name 
 ,@id 
 ,@description 
 ,@createdate 
 ,@folderid 
 ,@packagedata 
 ,@packageformat 
 ,@packagetype 
 ,@vermajor 
 ,@verminor 
 ,@verbuild 
 ,@vercomments 
 ,@verid 

Now you are all set to use Visual Studio 2005 to save SSIS Package in SQL Server 2008 / SSIS 2008 Storage.

Package that I started in scenario, is now available in SSIS package store:

Does that mean I am all set to use Visual Studio 2005 for SQL Server 2008/SSIS 2008 Store? The answer is NO because there is still one more stored procedure missing that will help us in adding existing package from SSIS 2008 store to Visual Studio 2005 and i.e. sp_dts_getpackage

So, one more step for retrieving saved package:

Step 5:

Create following procedure in MSDB database:

CREATE PROCEDURE [dbo].[sp_dts_getpackage]
 @name sysname
 ,@folderid uniqueidentifier
AS
 exec sp_ssis_getpackage
 @name
 ,@folderid

That makes complete list of stored procedure available on SQL Server 2008 that is required by Visual Studio 2005 to save/retrieve SSIS package from SSIS 2008 package store.

Stored Procedure used for SSIS in Visual Studio

Summary

Visual Studio 2005 can be used to design SSIS packages for SQL Server 2008 if all stored procedures discussed above are manually created in MSDB on SQL Server 2008.