![](/uploads/1/2/7/4/127405481/650447315.png)
Hi i am trying to edit DTS Package in sql server 2008 management studio,note i found that i cannot find this path Microsoft SQL Server'80ToolsBinnReso urces1033 ican see only Microsoft SQL Server'80ToolsBinn1033 whichmean Resources folder was missing, i tried to created it manually, but it didnt workedi was able to see it, but now i am getting another errorsql server 2000 dts designer components are required to edit dts packages. Install the special web download, 'SQL server 2000 DTS Designer component' to use this feature.i am using SQL management studio 2008 my local window is windows 8 64-bit. I download it the file, but still, also i tried to open the DTS using sql management studio 2005, but the problem with 2005 that i was not able to see the DTS under ManagementĀ i was not able to see these folders Legacy-Data TransformationServiceHi asosa,According to your description, the error occur when you edit DTS package in SQL Servermanagement Studio 2008 (SSMS).
Right-click the Data Transformation Services node Click All Tasks Click Open Package Now find the file and click Open The package will now be opened in the DTS Designer again. If you want to put this back into one of the server based stores, from the Package menu, select Save As and choose your new storage location. We need to open DTS packages but receive the error: 'SQL Server 2000 DTS Designer components are required to edit DTS Stack Exchange Network Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.
To get around this, you'll need to follow a couple of steps:. Install the Microsoft SQL Server 2005 Backward Compatibility Components ;. Install the Microsoft SQL Server 2000 DTS Designer Components( );. Restart SQL Server Service.
Ensure that the PATH Environment Variable lists the SQL 2000 directory. The correct version of sqlgui.dll will be used in C:Program Files (x86)Microsoft SQL Server80ToolsBinn. Restart SSMS.For more information, see:Regards,Sofiya LiSofiya LiTechNet Community Support. I download it the file, but still, also i tried to open the DTS using sql management studio 2005, but the problem with 2005 that i was not able to see the DTS under ManagementĀ i was not able to see these folders Legacy-Data TransformationServiceHi asosa,According to your description, the error occur when you edit DTS package in SQL Servermanagement Studio 2008 (SSMS). To get around this, you'll need to follow a couple of steps:. Install the Microsoft SQL Server 2005 Backward Compatibility Components ;.
Install the Microsoft SQL Server 2000 DTS Designer Components( );. Restart SQL Server Service. Ensure that the PATH Environment Variable lists the SQL 2000 directory.
The correct version of sqlgui.dll will be used in C:Program Files (x86)Microsoft SQL Server80ToolsBinn. Restart SSMS.For more information, see:Regards,Sofiya LiSofiya LiTechNet Community Support.
Learning has never been so easy!
One of the challenges with upgrading SQL Server from SQL Server 2000 or SQL Server 2005 to a more modern version is that DTS (Data Transformation Services) is no longer supported. It was support for backwards compatibility in SQL Server 2005 and SQL Server 2008/2008 R2. Beginning with SQL Server 2012, DTS packages are no longer supported at all and must be converted to SSIS (SQL Server Integration Services) packages.
This how-to covers the practical process for upgrading old DTS packages to new, shiny SSIS packages.
16 Steps total
Step 1: Run Upgrade Advisor to analyze exisitng DTS packags for upgrade issues (optional)
Upgrade Advisor (https://msdn.microsoft.com/en-us/library/ms144256(v=sql.105).aspx) can only check DTS packages stored as files. It cannot check packages stored in msdb. If the file version of the packages are not available, you can export them to files.
Step 2: If DTS Packages are in SQL Server 2000, set up a new SQL Server 2008 R2 server
You cannot go directly from SQL Server 2000 to SQL Server 2012 or newer. You have to go to an interim version first. This is true for databases as well as DTS Packages. Using SQL Server 2008 R2 as the interim step (the highest version you can use in this scenario) as it gives you the greatest possibilities for upgrade paths in the next phase of the upgrade.
NOTE: DTS is a 32 bit only process so install 32 bit version of SQL Server 2008 R2.
NOTE: Be sure ot include the client components in the SQL Server installation as you will need to use BI Development Studio (BIDS) later.
Step 3: If DTS is in SQL Server 2005, leave in SQL Server 2005
You can upgrade directly to SQL Server 2012 or SQL Server 2014 from SQL Server 2005. If you want to move to SQL Server 2016, then upgrade DTS in SQL Server 2005 and then migrate to SQL 2012 or 2014 as an interim upgrade step to SQL Server 2016.
Step 4: Install DTS run-time and designer support on the new SQL Server 2008 R2 instance
To see details on how to install these, look here: https://technet.microsoft.com/en-us/library/ms143755%28v=sql.105%29.aspx
Step 5: If using SQL Server 2008 R2 as an interim step, export all DTS packages to files and import into SQL Server 2008 R2 (optional)
Packages can be left stored in the file system instead of importing directly into SQL Server 2008 R2.
Step 6: Make any changes to the DTS packages that you want to make prior to migration
If Upgrade Advisor reported any potential conflicts (such as illegal characters in the file name) that you can address in the DTS package itself, open the DTS package in DTS Designer and make the changes
Step 7: Start the Package Migration Wizard
Method 1: From SQL Server Management Studio.
Connect to an instance of the SQL Server Database Engine, right-click the Data Transformation Services node in Object Explorer under ManagementLegacy, and select Migration Wizard.
Connect to an instance of the SQL Server Database Engine, right-click the Data Transformation Services node in Object Explorer under ManagementLegacy, and select Migration Wizard.
Method 2: From Business Intelligence Development Studio.
Create or open an Integration Services Project, right-click on the SSIS Packages node in Solution Explorer, and select Migrate DTS 2000 Package.
Create or open an Integration Services Project, right-click on the SSIS Packages node in Solution Explorer, and select Migrate DTS 2000 Package.
Method 3: From the command prompt.
Start DTSMigrationWizard.exe from the C:Program FilesMicrosoft SQL Server100DTSBinn folder.
Start DTSMigrationWizard.exe from the C:Program FilesMicrosoft SQL Server100DTSBinn folder.
Step 8: Select the package source
Can be packages stored in msdb or the file system. Migration wizard does not support packages stored as Visual Basic files or Metadata storage.
Step 9: Select the package destination
The destination can be the msdb database in SQL Server 2008 R2 or in the file system. If using the file system, the new packages will have the file extension .dtsx which is the file extension for SSIS packages.
Step 10: Select packages to be upgraded
Step 11: If prompted, provide passwords for password protected DTS packages
Step 12: Specify a log file to log migration errors
Set path and name of the log file you would like to use. Use a different log file for each set of migrations to avoid confusion if doing more than a single migration.
![How to open dts file in sql 2008 free How to open dts file in sql 2008 free](/uploads/1/2/7/4/127405481/641864457.png)
Step 13: Watch the migration process
If the migration encounters any failures, it will prompt you to choose to end the migration or skip the problem file. Choose skip for any failures.
Step 14: Open successfully migrated packages in BIDS and validate
Successfully migrated packages should be opened in Business Intelligence Development Studio (BIDS - installed with the SQL Server 2008 R2 client tools) and validate conversion complete. Make any changes needed.
Step 15: Review the migration log for any failed packages
If possible, make changes to the DTS packages to correct the issues identified in the migration log and re-migrate the fixed packages.
Step 16: Manually migrate any remaining DTS packages
If there were any packages that you couldn't migrate at all, you will have to recreate the package functionality using BIDS.
In my experience, about 60% of packages are able to migrate to SSIS cleanly with no changes (simple). 30% will require some post-migration modifications (medium). 10% will require manual migration (complex).
1 Comment
- HabaneroRobert for Microsoft Nov 24, 2015 at 03:47pmHere are some references to support this:
Support for SQL Server 2000 DTS in SQL Server 2008 R2: https://msdn.microsoft.com/en-us/library/bb500440(v=sql.105).aspx
How to: Install Support for Data Transformation Services Packages: https://technet.microsoft.com/en-us/library/ms143755%28v=sql.105%29.aspx
Migrating Data Transformation Services Packages: https://msdn.microsoft.com/en-us/library/ms143501%28v=sql.105%29.aspx
![](/uploads/1/2/7/4/127405481/650447315.png)