SQL Server Integrated Services Programming basics

If you've ever moved data into or out of SQL Server, you can now use Microsoft's DTS or Microsoft's Data Transfer Service tool. DTS is a tool for ETL (the abbreviation for extraction, transmission, and loading) that allows database administrators to create a series of steps to describe how data is transmitted. These "packages", like their names, can be run by a number of different programming languages or directly on the SQL Server itself.

This idea has always been a good idea, but in any case it is limited-if it is convenient to use the version of the block-copy system of an earlier version of SQL Server, it will continue to be glorious. On the SQL Server2005, Microsoft decided to turn DTS into a new ETL tool--sql Server Integrated Services (SSIS). Moving data from one database to another, or exporting the database in another format, is the most basic use of SSIS that you can use. Here are some other reasons to use SSIS:

Data cleaning

If you have a dataset that is not "clean", for example, it is not standardized, SSIS has a set of toolset that can be used to clean and standardize data. These include functions similar to "blur" matching and grouping, so that those that appear to be the same data (just through different imports and exports) can be filtered out and standardized. A good example of this is the different kinds of data from different geographic regions: the same ideas can be expressed in different ways, and they need to be standardized before being processed.

Connecting to the Analysis Services cube

You can use SSIS to connect data directly to a cube through a pipeline for data mining. In addition to importing this data into the local database and then digging there, the SSIS package itself can be used for the data source.

Integration with SQL Server Reporting Services

For analysis cubes, SSIS packages can also be used directly for data sources to create reports.

It's totally. NET Report

This may be hypothetical, but SSIS programming can indeed be done locally or through a COM object interface in any of the. NET languages-or, in fact, any language that supports COM objects.

The end result is the system where you import the dataset, and the packages you create can be more like objects that are turned into, rather than static data. It is this idea that allows users to program in advance in the way they report services, analysts say.

Services, data, and the database itself, will talk to each other. For example, archived legacy data can continue to be used for form analysts or data mining, even if they are technically already offline. Another example is the integration of data from multiple static collections, such as the one I mentioned earlier, from multiple geographic locations.

Because SSIS is programmable, its functionality can be invoked on a large scale by the environment. For example, a asp.net-driven Web site can be a management front-end that runs SSIS packages, or it can be a stand-alone application that connects to SSIS, extracting and refining data for its own use. The idea of being able to programmatically drive SQL Server has been in place for a long time, but SSIS makes it possible to talk to all things, including what SQL Server uses-once, and in a unified way. A programmer can also create custom SSIS extensions, such as customer data transfers or counters. You can use them again in another application or in a whole other environment.

Packages in SSIS can be written in the Business Intelligence Design Suite (Business Intelligence), which is sold with SQL Server2005. Most of the resulting files are in XML format, so they can be viewed or manually modified if needed. Note that some of the features in SSIS-Data mining connections and fuzzy groupings and lookups-are available only in the Enterprise version of SQL Server2005. SSIS is not available in all SQL Server Express Edition, so if you create SSIS packages, you can use them only in the full version of SQL Server. Finally, when you use SSIS to create programmatic solutions, they need to be designed to work in other environments that fully support SSIS.