August 26, 2014 at 8:16 am
Will scripted SQL Server 2000 jobs execute in 2008 R2? I am attempting to test the execution of various SQL 2k DTS packages on what will become our production 2008 server. I have migrated a handful of DTS packages (using the Migration Wizard) from our 2k box to the 2008 box. I then scripted the existing 2K scheduled jobs associated with these packages, and created them on the 2008 box. The error message I'm getting is "Specified DTS Package does not exist."
The packages execute without error directly from the MSDB node in Management Studio. Likewise, I can create a IS solution, schedule a job and it executes. I was hoping I was going to be able to migrate the 2k jobs along with the DTS Packages. Any thoughts appreciated.
August 26, 2014 at 9:27 am
Do you mean you converted the DTS packages to SSIS packages on the 2008 server, or did you just copy them over as legacy dts packages to the 2008 server?
August 26, 2014 at 9:53 am
I used the Migration Wizard, so they now exist in MSDB on the 2008 server. If I create a new SSIS package, navigate to Project menu option and select Add Existing Package. I can see all of what was my 2k DTS Packages. Now, in the solution it appears as an SSIS package.
I have had success in running the IS packages directly. Either within an SSIS solution, or by right clicking on the package name in management studio under the MSDB node.
I am not having luck getting the associated scripted and recreated SQL Agent jobs to execute the migrated DTS packages.
August 26, 2014 at 10:10 am
SSIS packages are a new thing starting from 2005(they're not just a new name for DTS Packages), what you're seeing in the MSDB node are the SSIS packages created by the wizard when you migrated the dts packages from 2000. If you go to Management -> Legacy there should be section for DTS Packages although you might have to install support for it in 2008, http://technet.microsoft.com/en-us/library/bb500440(v=sql.105).aspx#running_agent
And definitely be aware that any packages you convert with the wizard you should check to make sure they are still doing what they are supposed to be doing even if the wizard said it created a SSIS package successfully.
August 26, 2014 at 10:26 am
I cannot see the packages under the Legacy node. I'm pretty sure we installed all the backward compatibility necessary.
The packages do run. I'm just concerned that I don't seem to be able to script and recreate all the associated jobs to execute these packages.
August 26, 2014 at 11:08 am
bd this is because DTS Packages are not SSIS Packages, the wizard doesn't copy the packages from your 2000 server to your 2008 server it tries to create them as SSIS packages on the new server, which is why you'll need to verify any package you migrate this way as the wizard is not perfect and not all the old DTS structures have perfect mappings to SSIS equivalents.
It's been awhile since I had to do this but you should be able to export the package files from your 2000 server to your file system then import those into your 2008 environment where they'll show up under the legacy data structures. From there the link I posted earlier should explain how to run them.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply