*************************************************************************************
SQL Server SSIS Historical OverviewIn SQL Server 7.0, Microsoft had a small team of developers work on a much understated feature of SQLServer called Data Transformation Services (DTS). DTS was the backbone of the Import/Export Wizard,and the DTS ’ s primary purpose was to transform data from almost any OLE DB – compliant Data Sourceto another destination. It also had the ability to execute programs and run scripts, making workflow aminor feature.By the time that SQL Server 2000 was released, DTS had a strong following of DBAs and maybe a fewdevelopers. Microsoft included in the release new features like the Dynamic Properties Task that enabledyou to alter the package dynamically at runtime. Even though DTS utilized extensive logging along withsimple and complex multiphase data pumps, usability studies still showed that developers had to createelaborate scripts to extend DTS to get what they wanted done. A typical use case was enabling DTS toload data conditionally based on the existence of a file. To accomplish this in DTS, you would have hadto use the ActiveX Script Task to code a solution using the file system object in VBScript. The problemhere was that DTS simply lacked some of the common components to support typical ETL processes.Although it was powerful if you knew how to write scripting code, most DBAs just didn ’ t have this typeof scripting experience (or time).After five years, Microsoft released the much touted SQL Server 2005, and SSIS, which is no longer anunderstated feature like DTS. With the 2008 release, SSIS is now one of the main business intelligence(BI) platform foundations. SSIS has moved so far up in importance that it now has its own service alongwith the new name. This is entirely appropriate because so much has been added to SSIS. Microsoftmade a huge investment in usability, adding the first set of ETL tool - based components and upping theante again with this latest version. If what you need to do isn ’ t readily available, you now have the full.NET library with VB and C# programming languages to add your own custom coding to message dataor manage the ETL process. However, you ’ ll be surprised how rarely you ’ ll need to drop into a codingenvironment. In fact, as you dig into the toolset, you ’ ll find that things you may have needed to hand -code in a Script Task are simply part of the out - of - the - box components.What ’ s New in SSISSSIS is now in its second edition. If you are brand new to SSIS, everything will be new, but even if youare already using SSIS each version just keeps getting better. This latest version of SSIS includesenhancements for performance and scalability, upgrades to handle new TSQL capabilities, and theaddition of new components, including the long - awaited ability to use C# in the scripting tasks. We ’ ll hitthe highlights here.Chapter 1: Welcome to SQL Server Integration Services3The data pipeline has been overhauled so that it scales to better use the multiple, dual, and quad - coreprocessor improvements. The Lookup Component that performs foreign key resolutions has also beenredesigned to allow for persistence of lookup cache that screams when you tune them for dimensiontables. Underneath SSIS now allows new TSQL extensions for multiple data manipulation language(DML) operations like the MERGE statement.If you are looking for the latest toys, this version of SSIS has added new workflow components to controlthe cache window maintenance, to generate TSQL traces, or reset row count variables. In the Data Flows,there are new ADO Sources and Destinations to add to the OLE Sources and Destinations that were partof the first version.Lastly, there has been a major improvement to the development environment from the previous versionswith the removal of the cobbled - together Visual Basic for Applications (VBA) implementation. The VBAenvironment was only intended as a temporary implementation to allow custom scripting within yourETL processes, evidenced by the clunky integration and that you were limited to VB.NET only. Now theScript Tasks and Components use an embedded version of the Microsoft Visual Studio 2008 Tools forApplications (VSTA) environment that supports both VB and C# .NET programming languages. Inaddition, you can now add web references to your ETL processes without having to code your ownwrappers to web services to make use of existing business logic or data sources. We ’ ll touch on all ofthese improvements as you read through this book and explore the examples, but first let ’ s get started.Getting StartedMost of this book will assume that you know nothing about the past releases of SQL Server DTS andwill start with a fresh look at SQL Server SSIS. After all, when you dive into the new features, you ’ llrealize how little knowing anything about the old release actually helps you when learning this one.However, if you don ’ t come from the world of DTS, it ’ s hard for us not to throw in a few analogies hereand there to get these folks also up to speed on SSIS. The learning curve can be considered steep at first,but once you figure out the basics, you ’ ll be creating what would have been complex packages in DTS inno time. To get an idea of how easy SSIS is to use, look at a tool that is a staple in the ETL world, theImport and Export Wizard.Import and Export WizardIf you need to move data quickly from almost any OLE DB – compliant Data Source to a destination, youcan use the SSIS Import and Export Wizard (shown in Figure 1 - 1). In fact, many SSIS packages are bornthis way. The wizard is a quick way to move the data, perform very light transformations of data, and allversions except Express allow you to persist the logic of the data movement into a package. The basicconcept of an import/export wizard has not changed substantially from the days of DTS. You still havethe option of checking all the tables you ’ d like to transfer. However, you also get the option now ofencapsulating the entire transfer of data into a single transaction.
บทความ
