The cost of a SQL Server license is a tremendous bargain when you consider all the different products included with the database engine itself. With other enterprise database platforms, features like Reporting Services, Analysis Services, Integration Services, Service Broker, etc, all have to be purchased separately.
Most SQL Server owners do not make full use of all the features they have paid for. Often it is because they do not need them, but sometimes it is because owners are not aware of how these technologies might be of use to them.
In recent issues we have explained some of the other technologies in terms of how they might be useful to you, our readers. This month we look at SQL Server Integration Services (SSIS) from the same point of view.
What is SSIS?
SSIS is an ETL tool. ETL is an acronym derived from the three principal functions the tool performs: Extracting data from a source, Transforming the data as necessary and Loading the data into a destination database.
Although SSIS is a SQL Server technology, neither the data source nor the destination needs to be a SQL Server database. You can import and export data to and from Oracle, DB2, text files, Microsoft Access, Excel or just about anything that holds structured information. Many organizations that use other database platforms like Oracle, MySQL or DB2 also own a copy of SQL Server Standard addition just so they can use SSIS to move and transform their data.
How could SSIS help me?
Here is one common scenario:
Many organizations depend on frequent loading of data from external sources. Sometimes they use an internally developed ETL program dating from the early days of the organization. While this program might have worked adequately then for the smaller amounts of data and fewer concurrent users, we sometimes find that the increase in data volume over time has affected import performance.
Internally developed import applications often have the import and transformation logic embedded in a way that makes it difficult to document or to change when requirements change. It is not unusual to find that there are only one or two people in the organization who understand and can maintain the application.
If this is the case in your organization, you might want to take a good look at SSIS to speed up your imports and exports.
SSIS is a well-known and well-supported architecture. There are thousands of competent SSIS developers who can look at an SSIS package and very quickly find out how it works and the business logic it implements. This insulates an organization against the common risk that the only person who really understands the mission-critical data import might leave the company.
Creating SSIS packages requires some effort to understand the programming model, but it is not rocket science. Simple imports and exports can be done using the Data Import/Export Wizard by someone with little or no programming experience.
The wizard is a built-in SSIS package that appears as a separate program in your SQL Server program group. You should be able to use it to click your way through simple imports or exports of SQL Server data that do not require complex data transformations.
In the real world, the source data does not often match the format or structure of the destination database. Data generally requires some degree of transformation and this is where the power of SSIS really shows.
Transforming data on its way from source to destination requires the creation of a custom SSIS package. Packages are created in Business Intelligence Development Studio (BIDS). BIDS is a subset of Microsoft Visual Studio that allows you to create SSIS packages as well as other types of packages. BIDS was installed with SQL Server if you selected either SSIS or Analysis Services to be installed with the database engine. If not, you have to add it to the installation from the SQL install DVD.
Simple transformations like changing the data type of a column or merging data from FirstName and LastName fields in the source data into a single EmployeeName field in the destination can usually be handled with a few clicks. But no matter how complicated the transformation logic is, it can be applied in an SSIS package.
What you can do with your data in an SSIS package is limited only by the skill of the package developer. A novice can create some surprisingly sophisticated packages just through point and click, dragging and dropping pre-defined tasks into a flow chart. However more complex packages will require stronger programming skills.
If you have a problem with data import, transformation, or loading, SSIS can probably fix it for you. If you are unfamiliar with the technology, it will require some degree of effort to acquire the requisite skills.
If you are interested in acquiring or improving SSIS skills, I highly recommend Knight’s 24 Hour Trainer for SQL Integration Service from Wrox Publishing (isbn 978-0-470-49692-3). This trainer is a combination of text and video (on an accompanying DVD) that will get you up to speed very quickly.