Database Reference
In-Depth Information
SQL Server provides many processes for the ETL management, such as BCP (Bulk Copy), BI
(Bulk Insert), DTS (Data Transformation Services) and SSIS (SQL Server Integration Services).
Since SQL Server 2005 DTS has been transformed into SSIS, which is a step ahead in
terms of functionality providing more intuitive design and management for ETL operations,
henceforth DTS functionality has been discontinued. SQL Server 2008 and SQL Server
2008 R2 enhances the native .NET execution environment to continue the ETL process
with significant pipeline scalability enhancements such as persistent lookups, which is a
significant improvement for data warehousing environment. The SQL Server Agent service
manages many different objects on the server, each one responsible for its own function
in the larger automation infrastructure. Mastering these objects is critical to implement an
effective automation plan. For backward compatibility, SQL Server 2008 R2 supports the DTS
packages. Using the command-line arguments, we can initiate the Import and Export wizard.
In this recipe, we will look at the SQL Server 2008 R2 tools and agent service capabilities
to design an effective scheduled activity to handle critical ETL processes. SQL Server 2008
R2 consists of multiple windows services, for scheduling and automation activities. SQL
Server agent service is useful, as it contains various artifacts such as jobs, schedules,
operators, and alerts.
Getting ready
For ETL management within these job steps, the SQL Server Integration Service (SSIS)
package is the key. Since these SSIS packages contain the corresponding administrative
step, the entire package can be defined as a single-step to execute it within the job. Not only
can we create the job steps from the SQL Server agent, we can also create the ETL processes
using SQL Server Management Studio using SQL Server's import and export wizard. For
desktop-based environments, the ETL activities can be accomplished using the SQL Server
Express with the Advanced Services edition that consists of a basic installation of SQL Server
Management Studio.
Although, SQL Server 2008 R2 extends the support to run DTS packages and Integration
Services packages on the same server, the setup does not install the run-time support for DTS
packages. You have to install this run-time support during the setup on the Feature Selection
page by selecting Integration Services to install ActiveX script task and DTS package
migration wizard and Client Tools Backward Compatibility. However, these components are
not fully functional without the manual installation of additional, optional components that are
not available during Setup.
Further, on a 64-bit environment, there is no 64-bit run-time support for DTS packages and
Integration Services packages that run DTS packages, they can run only in 32-bit mode. To
run packages in 32-bit mode outside BI Development Studio on a 64-bit computer, during
Setup, select Business Intelligence Development Studio or Management Tools | Complete.
Search WWH ::
Custom Search