Archives

Introduction to the SQL Server Integration Services (SSIS) Development Environment

2010-09-01 13:50
This article is a bare bones introduction to SQL Server Integration Services (SSIS), with an emphasis on the SSIS development environment. SSIS is Microsoft s full feature extract-transform-load (ETL) tool in SQL Server 2005, 2008, and R2.В SSIS is used to extract data from any of a myriad of sources, such as SQL Server databases, flat files, Excel files, Oracle and DB2 databases, etc. ItВ performs data manipulation and transformation on the data and delivers it to one or more data destinations. Exclusive Free SQL Server 2008 R2 Guidebook for Petri Readers Hot off the presses at Microsoft, this book is for anyone who has an interest in SQL Server 2008 R2 and wants to understand its capabilities.Access custom scripts, installation and upgrade guides, management tips, high availability strategies, database consolidation techniques, and more...Download Your Free Copy Here While SSIS is part of the SQL Server suite of tools, and is very adept at handling SQL Server-to-SQL Server ETL operations, it easily handles many other source and destination you have a library for. Potential applications include scrubbing and exporting Excel data for output to network directories, or third party database ETL transformations (e.g., Oracle-to-Oracle data loads). Let s create a new SSIS package and take a brief look at the major components and features of the SSIS development environment. Visual Studio vs. BIDS The SSIS development environment can be installed during a SQL Server installation or independently, i.e., you don t have to have the SQL Server engine installed to develop SSIS packages. The SSIS development environment consists of the Business Intelligence Development Studio (BIDS), a full-fledged Visual Studio installation with components specific to SSIS and other elements comprising SQL Server business intelligence. If you already have Visual Studio 2008 installed on your computer, the SSIS installation will install a separate BIDS application and add components to your existing Visual Studio. You can develop in either environment, as they are identical. Important Panes Like all files in Visual Studio, all SSIS packages are part of a VS project, which is in turn part of a solution. To create a new project and a new SSIS package: Start BIDS or Visual Studio Select File New Project Select Integration Services Project from Business Intelligence Projects Name the project and select the location. Click OK. (Above: New Project Window in Visual Studio/BIDS) The new project will display a new, blank package. Your most valuable windows are: Toolbox - Contains the elements you can add to your package. The contents of the Toolbox change depending on what main window tab you re in. Main Window - The development window. Each tab shows a different level or focus of the development. Today we are only interested in two of those tabs. Control Flow tab - Contains control flow elements. Control flow is anything that moves in the package - anything that causes you to go...
Read More...