Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

DTS Package and its Compatibility

From Wiki

Jump to: navigation, search
                Data Transformation Package 


INDEX 1. What, Why & Where DTS? 2. Role of DTS in SQL 2000 2.1 DTS packages 2.2. DTS tools 2.2.1 DTS wizards 2.2.2 DTS Designer 2.2.3 DTS Query Designer 2.2.4 DTS Run Utility 3. Compatibility of SQL 2000 with SQL 2008? 4. Support for SQL Server 2000 DTS in SQL Server 2008 4.1 General Compatibility between SQL Server 2000 DTS and Integration Services 4.2 Managing SQL Server 2000 DTS Packages 4.3 Installing Run-time Support for SQL Server 2000 DTS Packages 4.4 Running SQL Server 2000 DTS Packages at the Command Prompt 4.5 Running SQL Server 2000 DTS Packages from SQL Server Agent Jobs 4.6 Running SQL Server 2000 DTS Packages from Integration Services Packages 4.7 Installing Design-time Support for SQL Server 2000 DTS Packages 4.8 Migrating SQL Server 2000 DTS Packages 4.9 Migrating Command Lines for Executing Packages 4.10 Support for ODBC Data Sources 4.11 Support for Meta Data Services (Repository) Packages 4.12 Support for SQL Server 2000 DTS Packages in SQL Server Express 5. How to: Install Support for Data Transformation Services Packages 5.1 To install all the features of DTS support 5.2 To install run-time support for Data Transformation Services packages 5.3 To install run-time support for Data Transformation Services packages 5.4 To install the 32-bit version of the Integration Services runtime and command prompt utilities on a 64-bit x86 computer 5.5 To install the 32-bit version of the Integration Services runtime and command prompt utilities on a 64-bit Itanium computer 5.6 To install design-time support for Data Transformation Services packages 5.7 To ensure that the DTS designer can be used in SQL Server Management Studio 5.8 To ensure that the DTS designer can be used in Business Intelligence Development Studio

                 --------------------------------------------------------------------------------------------------------







1. What, Why & Where DTS? DTS help us to: • Import, • Export and/or • Transform data from various sources to single or multiple locations in Sql Server. Basically when administering a database, one of our primary goals is to be the gatekeeper of data. Anything that has to do with how data is stored, saved, updated, and/or deleted will fall into our realm. In today’s business world, most companies store their data in different formats. As a result of this, along with the frequent and expected changes made in the average business, it is not unusual to have to move data from one location to another and/or reformat it on a regular basis.

A powerful set of tools called Data Transformation Services (DTS) can help us perform these tasks in Microsoft SQL Server 2000. DTS can help us import, export, and/or transform data from various sources to single or multiple locations. Data Transformation Services, or DTS, is a set of objects and utilities to allow the automation of extract transform and load operations to or from a database. The objects are DTS packages and their components, and the utilities are called DTS tools. DTS was included with earlier versions of Microsoft SQL Server, and was almost always used with SQL Server databases, although it could be used independently with other databases. DTS allows data to be transformed and loaded from heterogeneous sources using OLE DB, ODBC, or text-only files, into any supported database. DTS can also allow automation of data import or transformation on a scheduled basis, and can perform additional functions such as FTPing files and executing external programs. In addition, DTS provides an alternative method of version control and backup for packages when used in conjunction with a version control system, such as Microsoft Visual SourceSafe .


2. Role of DTS in SQL 2000

DTS was first released with SQL Server 7.0 and has been vastly improved with the release of SQL Server 2000. Some of the new features include: • Integration with Windows 2000 security • The ability of packages to run asynchronously • HTML Web page source • New FTP, Execute Package, Dynamic Properties, and Message Queue tasks • The ability to save packages as Visual Basic files • Enhanced logging and a new multiphase data pump In SQL Server versions 6.5 and earlier, Database administrators (DBAs) used SQL Server Transfer Manager and Bulk Copy Program, included with SQL Server, to transfer data. These tools had significant shortcomings, and many DBAs used third-party tools such as Pervasive Data Integrator to transfer data more flexibly and easily. When SQL Server 7 was released, "Data Transformation Services" was packaged with it to replace all these tools. SQL Server 2000 expanded DTS functionality in several ways. Many new types of tasks were made, including the ability to FTP files, move databases or database components, and add messages into Microsoft Message Queue. DTS packages can be saved as a Visual Basic file in SQL Server 2000, and this can be expanded to save into any COM-compliant language. Packages were also integrated into Windows 2000 security, DTS tools were made more user-friendly, and tasks can accept input and output parameters. DTS comes with all editions of SQL Server 7 and 2000, but was superseded by SQL Server Integration Services in the Microsoft SQL Server 2005 release. 2.1 DTS packages The DTS package is the fundamental logical component of DTS; every DTS object is a child component of the package. Packages are used whenever one modifies data using DTS. All the metadata about the data transformation is contained within the package. Packages can be saved directly in a SQL Server, or can be saved in the Microsoft Repository or in COM files. SQL Server 2000 also allows a programmer to save packages in a Visual Basic or other language file (when stored to a VB file, the package is actually scripted -- that is, a VB script is executed to dynamically create the package objects and its component objects). A package can contain any number of connection objects, but does not have to contain any. These allow the package to read data from any OLE DB-compliant data source, and can be expanded to handle other sorts of data. The functionality of a package is organized into tasks and steps. A DTS Task is a discrete set of functionalities executed as a single step in a DTS package. Each task defines a work item to be performed as part of the data movement and data transformation process or as a job to be executed. Data Transformation Services supplies a number of tasks that are part of the DTS object model and that can be accessed graphically through the DTS Designer or accessed programmatically. These tasks, which can be configured individually, cover a wide variety of data copying, data transformation and notification situations. For example, the following types of tasks represent some actions that you can perform by using DTS: executing a single SQL statement, sending an email, and transferring a file with FTP. A step within a DTS package describes the order in which tasks are run and the precedence constraints that describe what to do in the case of failure. These steps can be executed sequentially or in parallel. Packages can also contain global variables which can be used throughout the package. SQL Server 2000 allows input and output parameters for tasks, greatly expanding the usefulness of global variables. DTS packages can be edited, password protected, scheduled for execution, and retrieved by version. 2.2. DTS tools DTS tools packaged with SQL Server include the • DTS wizards, • DTS Designer and • DTS Programming Interfaces. 2.2.1 DTS wizards The DTS wizards can be used to perform simple or common DTS tasks. These include the Import/Export Wizard and the Copy of Database Wizard. They provide the simplest method of copying data between OLE DB data sources. There is a great deal of functionality that is not available by merely using a wizard. However, a package created with a wizard can be saved and later altered with one of the other DTS tools. A Create Publishing Wizard is also available to schedule packages to run at certain times. This only works if SQL Server Agent is running; otherwise the package will be scheduled, but will not be executed. a) Using the Import/Export Wizard Let's look at an example of how to use the Import/Export Wizard. (An example of test environment contains a default instance of SQL 2000 called STEVENW and a named instance called SQL2K).

In this example, we will import data from the STEVENW/pubs database into the SQL2K/DTS database, to export the data to SQL2K/DTS, We must first create that database. To do this, I open the SQL Enterprise Manager console, right-click on Databases under STEVENW\SQL2K, and select New Database

Next, I right-click on the database I want to Import/Export (STEVENW/pubs) and choose All Tasks | Export Data, as shown in Figure B. This brings up the DTS Import/Export Wizard Welcome screen.

My next step is to choose a data source that matches the format of my source data (Figure C). I also need to enter my source server name (STEVENW), my authentication method, and the database I want to export. The Advanced button offers more advanced OLE DB options for customizing connection settings.

Configuring the destination database options is the same as choosing a data source, but it's important to select the correct destination server and database. Once I finish configuring the destination options, I click Next to bring up the screen shown in Figure D. Here, I can specify a Table Copy or Query. For this example, I will copy objects and data between SQL Server Databases.


Now that I have chosen my options, I can select the objects to copy.

In this example, I will accept the defaults. Clicking next brings up the Save, Schedule, And Replicate Package screen. I will run the package immediately and save it to SQL Server (Figure F), although I also have the option of using replication with this package or scheduling it to run at a later date.


When I click Next, I advance to a screen where I can configure a password and give the package a friendly name. I then move on to the Summary screen to review my information and click Finish. Since I specified the package to run immediately, it begins to copy the data over to the new database.

b) The Copy Database Wizard The Copy Database Wizard helps you move or copy a database to another server. To access this wizard, I choose Tools | Wizards | Management | Copy Database Wizard. My next step is to choose the source server and authentication type (similar to what we saw in Figure C). After I make my selections, I click Next and choose my destination server and authentication type. Now I can select the databases I want to move or copy When I click next, the Database File Location window appears (Figure H). I can also choose the Modify button to change the databases' default location.


Clicking Next advances me to the Select Related Objects window (Figure I), where I can specify which SQL Server objects I want to move.


The final two screens let me schedule and review the DTS package I have created prior to kicking it off. Once everything is complete, I can view the log details (Figure J).


Walking through the Import/Export Wizard and the Copy Database Wizard is a good way to get familiar with what DTS has to offer. 2.2.2 DTS Designer The DTS Designer is a graphical tool used to build complex DTS Packages with workflows and event-driven logic. DTS Designer can also be used to edit and customize DTS Packages created with the DTS wizard. Each connection and task in DTS Designer is shown with a specific icon. These icons are joined with precedence constraints, which specify the order and requirements for tasks to be run. One task may run, for instance, only if another task succeeds (or fails). Other tasks may run concurrently. The DTS Designer has been criticized for having unusual quirks and limitations, such as the inability to visually copy and paste multiple tasks at one time. Many of these shortcomings have been overcome in SQL Server Integration Services, DTS's successor. 2.2.3 DTS Query Designer A graphical tool used to build queries in DTS. 2.2.4 DTS Run Utility DTS Packages can be run from the command line using the DTSRUN Utility. The utility is invoked using the following syntax: dtsrun /S server_name[\instance_name]

       { {/[~]U user_name [/[~]P password]} | /E }
   ]
   {    
       {/[~]N package_name }
       | {/[~]G package_guid_string}
       | {/[~]V package_version_guid_string}
   }
   [/[~]M package_password]
   [/[~]F filename]
   [/[~]R repository_database_name]
   [/A global_variable_name:typeid=value] 
   [/L log_file_name]
   [/W NT_event_log_completion_status]
   [/Z] [/!X] [/!D] [/!Y] [/!C]

]

When passing in parameters which are mapped to Global Variables, you are required to include the typeid. This is rather difficult to find on the Microsoft site. Below are the TypeIds used in passing in these values. Type typeid Boolean 11 Currency 6 Date 7 Decimal 14 HRESULT 25 Int 22 Integer (1-byte) 16 Integer (8-byte) 20 Integer (small) 2 LPWSTR 31 Pointer 26 Real (4-byte) 4 Real (8-byte) 5 String 8 Unsigned int (1-byte) 17 Unsigned int (2-byte) 18 Unsigned int (4-byte) 19 Unsigned int (1-byte) 21 Unsigned int 23


3. Compatibility of SQL 2000 with SQL 2008? DTS has been superseded by SQL Server Integration Services (SSIS) in later releases of Microsoft SQL Server though there was some backwards compatibility and ability to run DTS packages in the new SSIS for a time. SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. First released with Microsoft SQL Server 2005, SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0. Unlike DTS, which was included in all versions, SSIS is only available in the "Standard" and "Enterprise" editions. The SSIS Import/Export Wizard lets the user create packages that move data from a single data source to a destination with no transformations. The Wizard can quickly move data from a variety of source types to a variety of destination types, including text files and other SQL Server instances. Developers tasked with creating or maintaining SSIS packages use a visual development tool based on Microsoft Visual Studio called the SQL Server Business Intelligence Development Studio (BIDS). It allows users to edit SSIS packages using a drag-and-drop user interface. A scripting environment in which to write programming code is also available in the tool. A package holds a variety of elements that define a workflow. Upon package execution, the tool provides color-coded, real-time monitoring. Connections A connection includes the information necessary to connect to a particular data source. Tasks can reference the connection by its name, allowing the details of the connection to be changed or configured at run time. Tasks A task is an atomic work unit that performs some action. There are a couple of dozen tasks that ship in the box, ranging from the file system task (which can copy or move files) to the data transformation task. The data transformation task actually copies data; it implements the ETL features of the product Precedence constraints Tasks are linked by precedence constraints. The precedence constraint preceding a particular task must be met before that task executes. The run time supports executing tasks in parallel if their precedence constraints so allow. Constraints may otherwise allow different paths of execution depending on the success or failure of other tasks. Together with the tasks, precedence constraints comprise the workflow of the package. Event handlers A workflow can be designed for a number of events in the different scopes where they might occur. In this way, tasks may be executed in response to happenings within the package —such as cleaning up after errors. Variables Tasks may reference variables to store results, make decisions, or affect their configuration. Parameters (SQL Server 2012 Integration Services) Parameters allow you to assign values to properties within packages at the time of package execution. You can project parameters and package parameters. In general, if you are deploying a package using the package deployment model, you should use configurations instead of parameters. A package may be saved to a file or to a store with a hierarchical namespace within a SQL Server instance. In either case, the package content is persisted in XML. Once completed, the designer also allows the user to start the package's execution. Once started, the package may be readily debugged or monitored. SSIS provides the following built-in transformations: • Conditional Split • Multicast • Union-All, Merge, and Merge Join • Sort • Fuzzy Grouping • Lookup and Fuzzy Lookup • Percentage Sampling and Row Sampling Transformation. • Copy/Map, Data Conversion, and Derived Column • Aggregation • Data Mining Model Training, Data Mining Query, Partition Processing, and Dimension Processing • Pivot and Unpivot • Slowly Changing Dimension • Script Component • Audit • Cache Transform • Export and Import Column • OLE DB Command • Row Count • Term Extraction • Term Lookup • Row Sampling • Foreach Loop Container • For loop Container The Conditional Split transformation is used to conditionally route rows to other transformation objects based on a particular condition. It is similar to the "if..else" construct in the C language.

Other Include Tools Aside from the Import/Export Wizard and the designer, the product includes a few other notable tools. DTEXEC executes a package from the command line wherever it may be stored. Before running the package, the tool may be instructed to apply configuration information, which will allow the same package to be reused with slightly different parameters, including different connection strings for its endpoints. DTUTIL provides the ability to manage packages from the command prompt. The tool can copy or move a package from a file into the server store, or back out again. Among a few other sundry functions, It can be used to delete, rename, encrypt, or decrypt packages. Extensibility and Programmability Users may write code to define their own connection objects, log providers, transforms, and tasks. SSIS features a programmable object model that allows developers to write their own hosts for package execution. Such a host can respond to events, start and stop packages, and so on. The object model also allows developers to create, store, and load packages, as well as create, destroy, and modify any of the contained objects. Within limits, SSIS packages can load and call CLI assembly DLLs, providing access to virtually any kind of operation permissible by the .NET CLR. SSIS can be used on all SQL Server 2005, 2008, 2008 R2, and 2012 editions except Express and Workgroup. DTSX stands for Microsoft SQL Server Integration Services package file. At runtime the SSIS execution engine reads the definition of what it should do from .dtsx file, and then goes ahead and does it. The .dtsx file is not accessed again, once execution has begun. 4. Support for SQL Server 2000 DTS in SQL Server 2008

Although SQL Server 2000 Data Transformation Services (DTS) is deprecated, SQL Server 2008 provides optional 32-bit management, run-time, and design-time support for packages that were created by using the DTS tools and object model.

The features described in this topic, including run-time support for DTS packages, require the manual installation of additional, optional components that are not available during setup. Be aware that not all DTS functionality is supported in all circumstances. The following sections describe exactly what DTS functionality is supported in SQL Server 2008. Limited SQL Server 2000 DTS Functionality on 64-bit Operating Systems SQL Server 2008 does not include support for DTS in the following circumstances: • There is no 64-bit design-time or run-time support for DTS packages. On a 64-bit computer, DTS packages, and Integration Services packages that run DTS packages, can run only in 32-bit mode. • There is also no 32-bit design-time or run-time support for DTS packages on Itanium-based operating systems. Therefore, you cannot create, view, modify, or run DTS packages on Itanium-based operating systems. SQL Server 2008 includes support for the following DTS features: • The DTS runtime, the object model that it exposes, and the dtsrun.exe command prompt utility. • The Execute DTS 2000 Package task, for executing DTS packages within Integration Services packages. • The ActiveX Script task, for backward compatibility only. • The DTS Package Migration Wizard, for migrating DTS packages to the Integration Services package format. • The Upgrade Advisor rules for DTS packages, for identifying potential issues that may be encountered when migrating packages.

The Execute DTS 2000 Package task, the ActiveX Script task, and the DTS Package Migration Wizard are installed when you select Integration Services for installation. However, these components are not fully functional without the manual installation of additional, optional components that are not available during Setup. 4.1 General Compatibility between SQL Server 2000 DTS and Integration Services DTS and Integration Services are completely different products. Integration Services is not a version upgrade from DTS. Therefore, there are no software conflicts between DTS and Integration Services. You can do the following procedures: • Install both DTS and Integration Services on the same server. • Run both DTS and Integration Services packages on the same server. Even if the SQL Server 2000 tools are not present on the server, you can run DTS packages by using the updated version of the DTS runtime that is installed with Integration Services. • Save DTS packages in the msdb database of an instance of SQL Server 2000, or an instance that is running SQL Server 2005 or a later version. However, you can save SQL Server 2008 Integration Services packages only on an instance that is running SQL Server 2008. • Use an optional download of the DTS Designer to modify DTS packages even if the SQL Server 2000 tools are not present on the computer. However, you cannot open or modify DTS packages directly in Business Intelligence Development Studio.


When you upgrade an existing instance of SQL Server to SQL Server 2008, the system tables (for example, sysdtspackages) that DTS uses in the msdb database remain intact. DTS packages are not automatically migrated. 4.2 Managing SQL Server 2000 DTS Packages After you install the optional run-time support for DTS ,you can manage DTS packages from SQL Server Management Studio. These DTS packages can be stored in an instance of SQL Server 2000, or imported to and stored in an instance that is running SQL Server 2005 or a later version.


To view DTS packages in Management Studio, first connect to an instance of SQL Server. (Do not connect to an instance of Integration Services.) Then, in the Object Browser under the Management\Legacy\Data Transformation Services node, you will be able to perform the following tasks: • When you select the Data Transformation Services node in Management Studio, you can view and open packages, import them to SQL Server from structured storage files, or start the DTS Package Migration Wizard. • When you select an individual DTS package in Management Studio, you can open the package to modify it, migrate it, export it to a structured storage file, or delete it.

To open or modify DTS packages, a separate download of the DTS designer components is required. DTS packages cannot be opened or modified directly in BI Development Studio. The Integration Services database-level roles grant rights on the Integration Services system tables in the msdb database, but not on the DTS system tables, such as sysdtspackages in the msdb database. You have to grant users rights on the DTS system tables before they can perform certain management tasks, such as importing a DTS package in Management Studio

4.3 Installing Run-time Support for SQL Server 2000 DTS Packages Although you can run DTS packages and Integration Services packages on the same computer, SQL Server 2008 does not install run-time support for DTS packages. You have to install this run-time support before you can run and manage DTS packages. The backward compatibility files that provide run-time support for DTS packages also include run-time support for the SQL Server 2000 Analysis Services Processing task and its dependency, Decision Support Objects (DSO). However, the backward compatibility files do not include design-time support for modifying DTS packages that contain the SQL Server 2000 Analysis Services Processing task. You can modify packages that contain this task only when you have SQL Server 2000 Analysis Services installed on the same computer. The backward compatibility files that provide run-time support for DTS packages do not include support for the SQL Server 2000 Data Mining Prediction Query task. 4.4 Running SQL Server 2000 DTS Packages at the Command Prompt You can run DTS packages at the command prompt. Integration Services installs the dtsrun.exe utility when it installs DTS run-time support. 4.5 Running SQL Server 2000 DTS Packages from SQL Server Agent Jobs To run DTS packages from a SQL Server Agent job, you must set the job step to Operating system (CmdExec) and use the dtsrun utility (dtsrun.exe) to run the package. You cannot set the job step to SQL Server Integration Services Package 4.6 Running SQL Server 2000 DTS Packages from Integration Services Packages You can also run DTS packages as part of an Integration Services package. Integration Services provides the Execute DTS 2000 Package task for running DTS packages within an Integration Services package. 4.7 Installing Design-time Support for SQL Server 2000 DTS Packages DTS packages cannot be opened or modified directly in Business Intelligence Development Studio. In addition, neither SQL Server 2008, nor SQL Server 2005, installs the DTS package designer for modifying DTS packages. However, you can download and install the DTS package designer, and use it to modify DTS packages. After installing this download, you can view and modify DTS packages from Management Studio, or from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio.

4.8 Migrating SQL Server 2000 DTS Packages SQL Server provides the DTS Package Migration Wizard for migrating DTS packages to the Integration Services package format. When you select Integration Services for installation, Setup also installs the DTS Package Migration Wizard. You can optionally use the DTS Package Migration Wizard to upgrade DTS packages to the Integration Services format. During migration, the wizard copies the DTS packages and then re-creates them in the Integration Services format. The source packages are left intact and are not modified.

The DTS Package Migration Wizard is available in the following editions of SQL Server: Standard, Enterprise, and Developer. Many packages can be fully migrated to Integration Services, especially DTS packages that use tasks, connections, and other objects that have an equivalent in Integration Services. Other packages can be migrated successfully by encapsulating certain tasks or features that cannot be migrated in an Execute DTS 2000 Package task.

Before running the DTS Package Migration Wizard, you might want to install and run Upgrade Advisor to analyze your existing packages for migration issues. 

Upgrade Advisor cannot analyze, and the DTS Package Migration Wizard cannot migrate, DTS packages that are saved in the msdb database of an instance of SQL Server 7.0. However, the Upgrade Advisor can analyze, and the DTS Package Migration Wizard can migrate, SQL Server 7.0 DTS packages that are saved as structured storage files. Also, the Upgrade Advisor can analyze, and the DTS Package Migration Wizard can migrate, SQL Server 7.0 DTS packages that are saved in the msdb database of an instance of SQL Server 7.0 that has been upgraded to SQL Server 2005 or a later version. 4.9 Migrating Command Lines for Executing Packages To understand how the command-line options for the SQL Server 2000 DTS dtsrun utility (dtsrun.exe) map to the options for the Integration Services dtexec utility (dtexec.exe), 4.10 Support for ODBC Data Sources To load or save data to an ODBC data source, you can use an ADO.NET source or destination and select the .NET Framework Data Provider for ODBC. It is also possible to use the Script component to create an ad hoc ODBC source or destination for use in a single package. 4.11 Support for Meta Data Services (Repository) Packages SQL Server 2000 Meta Data Services, typically known as the Repository, is a deprecated component. SQL Server 2005 and later versions do not install or use the Repository. The DTS designer and the dtsrun utility continue to support DTS packages that were saved to the Repository. Integration Services supports the Repository only in the Upgrade Advisor and the DTS Package Migration Wizard, and only if the Repository redistributable files are installed on the local computer. (These files are normally present when the computer has either SQL Server 2000 or the SQL Server 2000 tools installed.) When the Repository redistributable files are present, the Upgrade Advisor can scan, and the DTS Package Migration Wizard can migrate, DTS packages that were saved to the Repository. When the Repository redistributable files are not present, the Upgrade Advisor and the DTS Package Migration Wizard can only be used on DTS packages that were saved to SQL Server or to structured storage files. The Integration Services Execute DTS 2000 Package task cannot run a DTS package that was saved to the Repository. However, the SQL Server 2000 DTS runtime can run such a package. Thus, to have an Integration Services package run a DTS package that is stored in the Repository, follow these steps: 1. Make sure that the Repository files are installed on the computer that is running the Integration Services package. To install the Repository files, install SQL Server 2000, the SQL Server 2000 tools, or the Repository redistributable files on the local computer 2. Create a new DTS package to act as a wrapper for the DTS package that is stored in the Repository. In this DTS wrapper package, use an Execute Package task to execute the package that is stored in the Repository. Save this wrapper package to SQL Server or as a structured storage file. 3. Use an Execute DTS 2000 Package task in the Integration Services package to run the new DTS wrapper package. 4.12 Support for SQL Server 2000 DTS Packages in SQL Server Express SQL Server Express does not include Integration Services nor does it support DTS packages. However, you can work with DTS packages on a server that is running SQL Server Express by using one of the following methods: F • To run existing DTS packages on a server that is running SQL Server Express, you need the DTS runtime on the server. Although SQL Server Express does not include the DTS runtime, you can install the DTS runtime on the server by either installing the SQL Server 2000 client tools or the DTS redistributable files. • To modify existing DTS packages on a server that is running SQL Server Express, you can use the SQL Server 2000 tools. You can also modify the packages remotely in SQL Server Management Studio on a server that is running one of these versions of SQL Server: Workgroup, Standard, Enterprise, or Developer. SQL Server Express does not include SQL Server Management Studio. • To migrate existing DTS packages to Integration Services, you need the DTS Package Migration Wizard that is installed with Integration Services. SQL Server Express does not include this wizard. To run the wizard, you can use one of these versions of SQL Server: Standard, Enterprise, or Developer. 5. How to: Install Support for Data Transformation Services Packages SQL Server 2000 Data Transformation Services (DTS) is deprecated, But you can continue to run DTS packages alongside Integration Services packages on the same computer. When planning to install support for DTS packages, know that SQL Server 2008 does not include support for DTS in the following circumstances: • There is no 64-bit design-time or run-time support for DTS packages. On a 64-bit computer, DTS packages, and Integration Services packages that use the Execute DTS 2000 Package task to run DTS packages, can run only in 32-bit mode. • There is no design-time or run-time support for DTS packages on Itanium-based operating systems. Therefore, you cannot create, view, modify, or run DTS packages on Itanium-based operating systems. DTS support in SQL Server 2008 consists of multiple components, Although Setup can install some of these components, those components are not fully functional until you install DTS run-time support. To install Integration Services together with all the features that support for DTS requires, you have to install multiple items as described in the following procedure. 5.1 To install all the features of DTS support 1. During Setup, on the Feature Selection page, make the following selections: 1. Select Integration Services. This option installs the ActiveX Script task and the DTS Package Migration Wizard. 2. Select Client Tools Backward Compatibility. This option installs the Execute DTS 2000 Package task. 2. After Setup, install the DTS runtime as described in the sections, "Installing Run-time Support for DTS Packages" and "Installing Additional 32-Bit Files Required to Run DTS Packages on a 64-bit Computer," later in this topic. 3. (Optional) After Setup, install the DTS designer as described in the section, "Installing Design-time Support for DTS Packages," later in this topic.

SQL Server 2008 does not install run-time support for DTS packages. Before you can run and manage DTS packages, you have to install this run-time support as described in the following procedure. 5.2 To install run-time support for Data Transformation Services packages SQL Server 2008 does not install run-time support for DTS packages. Before you can run and manage DTS packages, you have to install this run-time support as described in the following procedure. 5.3 To install run-time support for Data Transformation Services packages SQL Server 2008 does not install run-time support for DTS packages. Before you can run and manage DTS packages, you have to install this run-time support as described in the following procedure. • In an Internet browser, open the Microsoft SQL Server 2008 Feature Pack page, and then download and install the Microsoft SQL Server 2005 Backward Compatibility Components.

There is no 64-bit run-time support for DTS packages. On a 64-bit computer, DTS packages, and Integration Services packages that run DTS packages, can run only in 32-bit mode. To run packages in 32-bit mode outside BI Development Studio on a 64-bit computer, you have to install the following components: • The 32-bit DTS runtime, as described in the previous section. • The 32-bit version of the Integration Services runtime and command prompt utilities. 5.4 To install the 32-bit version of the Integration Services runtime and command prompt utilities on a 64-bit x86 computer • During Setup, select Business Intelligence Development Studio or Management Tools - Complete. Selecting either of these options installs the 32-bit version of the Integration Services runtime, the dtexec utility (dtexec.exe), and other Integration Services tools. 5.5 To install the 32-bit version of the Integration Services runtime and command prompt utilities on a 64-bit Itanium computer • During Setup, select Management Tools - Complete. Selecting this option installs the 32-bit version of the Integration Services runtime, the dtexec utility (dtexec.exe), and other Integration Services tools.

SQL Server 2008 does not install design-time support for DTS packages. Before you can use the SQL Server 2008 tools to open and view DTS packages, you have to download and install this design-time support as described in the following procedure.

The DTS package designer that you can download works with the SQL Server 2008 tools, but the designer's features have not been updated for SQL Server 2008. 5.6 To install design-time support for Data Transformation Services packages By default, on a 32-bit computer, the binary files for SQL Server 2000 are in the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder, and the binary files for SQL Server 2008 are in the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn folder. These default locations might cause the path of the binary files for SQL Server 2000 to occur in the PATH environment variable before the path of the binary files for SQL Server 2008. If this happens, an error message can appear when you try to use the DTS Designer. To resolve this error, copy the files that the designer requires to a new location as described in the following procedures.

On a 64-bit computer, substitute %ProgramFiles(x86)% for %ProgramFiles% in the following procedures. 5.7 To ensure that the DTS designer can be used in SQL Server Management Studio 1. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE folder. 2. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id% folder. For example, for U.S. English, the lang_id subfolder will be "1033". 5.8 To ensure that the DTS designer can be used in Business Intelligence Development Studio 1. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE folder. 2. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources folder to the %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id% folder. For example, for U.S. English, the lang_id subfolder will be "1033".

845 Rating: 2.4/5 (32 votes cast)