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.
![]()
Learning has never been so easy!
Jun 15, 2012 Hi Team, How many ways to open SSIS package in SQL Server 2008 R2 Tx subu Hi Subu, Since you chose to save the package in SQL Server - The Package will be saved in the MSDB Database in SQL Server. You can access the package from SQL Server Management Studio. 1) Select 'Integration Services' in the connection option instead of 'Database Engine'. A DTS encoded audio file is integrated with support for multichannel audio functionalities, and these files are stored in the DTS format. Affixed with the.dts extension, the content of a DTS file is encoded and compressed using DTS (Digital Theater Systems) specifications and standards for storing multichannel audio data.
This how-to covers the practical process for upgrading old DTS packages to new, shiny SSIS packages.
16 Steps totalStep 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.
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.
Method 3: From the command prompt.
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 upgradedStep 11: If prompted, provide passwords for password protected DTS packagesStep 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.
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
![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2022
Categories |