{"id":1359,"date":"2010-12-04T16:17:49","date_gmt":"2010-12-04T10:47:49","guid":{"rendered":"http:\/\/JitendraZaa.com\/blog\/?p=1359"},"modified":"2010-12-04T16:17:49","modified_gmt":"2010-12-04T10:47:49","slug":"etl-dts-and-ssis-introduction","status":"publish","type":"post","link":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserverintegrationservices\/etl-dts-and-ssis-introduction\/","title":{"rendered":"ETL , DTS and SSIS Introduction"},"content":{"rendered":"

ETL (Extraction, Transformation and Loading):<\/span><\/strong><\/p>\n

ETL is the process in database usage, specially in Dataware house that\u00a0evolves getting data from different sources (Extract<\/strong>)\u00a0, performing manipulation operations as per business need (Transformation<\/strong>) and saving on destination database (loading<\/strong>).<\/p>\n

\"What<\/a>
Extraction Transformation and Loading<\/figcaption><\/figure>\n

<\/p>\n

DTS \u00a0( Data Transformation Services):<\/span><\/strong><\/p>\n

<\/strong>In SQL Server versions 6.5 and earlier, Database administrators (DBAs) used SQL Server Transfer Manager<\/strong> and Bulk Copy Program<\/strong>, included with SQL Server, to transfer data. These tools had significant shortcomings, and many DBAs used third-party tools. When SQL Server 7 was released, “Data Transformation Services”<\/strong> was packaged with it to replace all these tools.
\nSQL 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 (MSMQ)<\/strong>.<\/p>\n

SSIS (SQL server Integration Services):<\/span><\/strong><\/p>\n

One disadvantage of DTS was that, for\u00a0development\u00a0of package, one must be connected to SQL Server. This is overcome in SSIS.<\/p>\n

SSIS is replacement of DTS in SQL Server 2005<\/strong>. \u00a0SSIS introduced “Business\u00a0Intelligence (BI) tool<\/strong>“, which is\u00a0development\u00a0\u00a0IDE and installed automatically by SQL Server 2005 in Visual Studio 2005. Thus BI tool gives the advantage of Visual Studio development tools for DTS.<\/p>\n

Advantages of SSIS over DTS:<\/span><\/strong><\/p>\n

<\/strong>SSIS gives you many new ways to control the flow of your package that could only be done before by writing code. One of the coolest control features is the ability to set up looping within the package. Two tasks, the For Loop Container and Foreach Loop Container<\/strong>, are available for this purpose.<\/p>\n

The precedence constraints<\/strong> used to connect one task to the next have been enhanced as well.<\/p>\n

There are many tasks and transforms that will reduce the need for scripting. Luckily, when you must write some code, the Script Task uses the Microsoft Visual Studio <\/strong>for Applications environment complete with Intellisense to help you navigate the SSIS object model. Variables, as long as they are in scope, may be accessed by the script. To those of you who prefer C#, sorry, only Visual Basic.Net is allowed. Don’t get this task confused with the old ActiveX Script Task still available for converted DTS packages.<\/p>\n

New Features of SSIS 2008:<\/span><\/strong><\/p>\n