sp_dts_getfolder isn't in my MSDB database, any ideas why?

Question

We're currently moving our applications from a SQL 2005 to a SQL 2008 R2 database and everything has gone well(ish) but now one of our applications calls a stored proc called sp_dts_getfolder which is meant to be in the MSDB database.

But it doesn't seem to be there at all, did we not install something or is this no longer a valid stored procedure in 2008 R2? What replaced it?

Answer 1

Why I think it can be something about the maintenance plans is because on msdn it states:

If the account that the SQL Server Agent runs under on the target server (TSX) is not a member of the sysadmin fixed server role on the master server (MSX), to import a distributed maintenance plan, the account must be granted the following permission on the master server.

.......

If this permission is not granted, the following error will appear in the SQL Server Agent log on the target server when the multiserver SQL Server Agent Job that runs the maintenance plan is refreshed by the target server:

"Failed to import Maintenance Plan from < NameOfMasterServer>: Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_dts_getfolder', database 'msdb', schema 'dbo'.)"

After these permissions are changed, each existing distributed maintenance plan must be reloaded on the target server. Make a small change to each plan and save. This will force the target server to reload the maintenance plan from the master server. By default, the target server will update jobs from the master server every 60 seconds. There will be a minimum delay of 120 seconds after you save the maintenance plan before it appears and can be run on the target server.

Here is the link to read about it

You should also looked at this kb. This is a fix from Microsoft regarding this problem.

After some investigation I found that going from sql 2005 to sql 2008 there are some changes in the system store procedures

Now the twist in the story is, since SSIS 2005 has grown up from DTS, the system tables and system stored procedures use a naming convention like "dts" in its name as you can see in first column of table below. With SSIS 2008, the SSIS team has standardize the naming convention and uses "ssis" in its name as you can see in the second column of the table below. So if you are using these system tables or system stored procedure in your code and upgrading to SSIS 2008, your code will break unless you change your code to accommodate this new naming convention.

So the [msdb].[dbo].[sp_dts_getfolder] is actually changed to [msdb].[dbo].[sp_ssis_getfolder].

You can find all of the other procedure changed and some more information in the article Best Practice #17 - Names of system tables and procedures have changed between SSIS 2005 and SSIS 2008