SQL Server Integration Services programming basics

The idea was and still is a good one, but ultimately too limited -- it remained a glorified if convenient version of the bulk-copy system used in earlier versions of SQL Server. With SQL Server 2005, Microsoft decided to turn DTS into a whole new kind of ETL tool — SQL Server Integration Services (SSIS). Moving data from one database to another or exporting a database in another format are only the most basic things you can do with SSIS. Here area additional reasons to use SSIS:

Data cleansing.
...

RELATED CONTENT Microsoft SQL Server Integration Services (SSIS) Saving time with text data sources in SSIS Using the Pivot transformation in SQL Server Integration Services Data Transformation Services vs. SSIS: The key differences Using package configurations in SQL Server Integration Services (SSIS) Microsoft SQL Server Integration Services primer A short history of SQL Server Integration Services New data profiling tools in SQL Server 2008 Tutorial: SQL Server Integration Services (SSIS) best practices SQL Server 2008 Integration Services delivers new features SSIS error message due to installation problem on SQL Server 2005 Microsoft SQL Server Performance Monitoring and Tuning SQL sprawl: Why is SQL Server Express installed everywhere? Top 10 SQL Server tips of 2009 Top 5 SQL Server DBA tasks that are a waste of time SQL Server Mailbag: Asymmetric encryption, log shipping issues Using traces in SQL Server Profiler SQL Server Mailbag: CALs, witnesses and unwanted changes SQL Server Mailbag: Data restoration and DB property management Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Troubleshooting Distributed Transaction Coordinator errors in SQL Server Data Warehousing and Business Intelligence Adding charts to reports with Report Builder 3.0 Creating bar charts with Report Builder 3.0 for SQL Server Generating column and line charts with Report Builder 3.0 Utilizing shape charts with Report Builder 3.0 Using range charts for visualization with Report Builder 3.0 Adding gauges to reports with Report Builder 3.0 for SQL Server Configuring pointers, scales and ranges with Report Builder 3.0 Putting the final touches on gauges with Report Builder 3.0 Making sense of Parallel Data Warehouse for SQL Server 2008 R2 Programming report generation with SQL Server Reporting Services 2008

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary contiguity  (SearchSQLServer.com) contiguous  (SearchSQLServer.com) drilldown  (SearchSQLServer.com) hashing  (SearchSQLServer.com) hybrid online analytical processing  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

If you have a data set that isn't "clean," i.e., it hasn't been normalized, SSIS has a broad tool set for cleaning and normalizing data. It includes functions like "fuzzy" matching and grouping, so data that is meant to be the same (but has been input or expressed differently) can be filtered and normalized. One good example of this would be data from different geographic regions that has different numeric formats: The same ideas that are expressed differently, need to be normalized before they can be crunched.

Connectivity to Analysis Services cubes.
You can use SSIS to pipe data directly into a cube for data mining. Instead of importing the data into a native database and then mining it there, an SSIS package itself can be used as the data source.

Integration with SQL Server Reporting Services.
As with analysis cubes, SSIS packages can be used as direct data sources for creating reports.

Full .NET support.
This is probably a given, but SSIS programming can be done either natively or through COM object interfaces in any of the .NET languages — or, for that matter, any language that supports COM objects.

The end result is a system where the data sets you import and the packages you create can work more like programmatic objects than static data. This is precisely the idea, and it allows the user to programmatically integrate the way the reporting services, the analysis services, the data and the database itself all talk to each other. For instance, archived legacy data can continue to be used for trend analysis or data mining even if it's technically offline. Another example is live integration of data from multiple static sets, such as the case I mentioned earlier about data from multiple geographic locations.

Because SSIS is programmable, its functions can be invoked in a broad range of circumstances. For instance, an ASP.NET-driven Web site could be a management front end to run SSIS packages, or one could write a standalone application to connect to SSIS and obtain and refine data for its own use. The idea of being able to drive SQL Server programmatically has been around for a long time, but SSIS makes it possible to talk to everything else that SQL Server uses as well -- all at once and in a unified way. A programmer can also create custom SSIS extensions such as custom data transforms or enumerators. You can reuse them in other applications or other environments entirely.

Packages in SSIS can be written using the Business Intelligence Design Studio, which ships with SQL Server 2005. The resulting files are mostly XML, so they can be inspected and edited by hand if need be. Note that some of the functions in SSIS — data mining connectivity and fuzzy grouping and lookup, to name two — are only available in the Enterprise Edition of SQL Server 2005. SSIS isn't available at all in SQL Server 2005 Express Edition, so if you create SSIS packages, you can only use them in the full edition of SQL Server. To that end, when you're creating programmatic solutions in SSIS, they're really designed to work in other environments that fully support SSIS as well.

ABOUT THE AUTHOR:   

Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
Copyright 2006 TechTarget

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. Submit a Tip DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.