Microsoft Office Tutorials and References
In Depth Information
The important elements of SSIS packages are control flows, data flows, connection managers, and event handlers.
(See Figure 1-15 .) Let’s look at some of the features of SSIS in detail, and we’ll demonstrate how simple it is to import
information from a source and export it to a destination.
Figure 1-15. SSIS project package creation
Because you will be working with the AdventureWorks database here, let’s pick some its tables, extract the data,
and then import the data back to another database or a file system.
Open SQL Server Data Tools. From the File menu, choose New, and then select New Project. From the installed
templates, choose Integration Services under Business Intelligence templates and select the Integration Services
Project template. Provide the necessary details (such as Name, Location, and Solution Name), and click OK.
Once you create the new project, you’ll land on the Control Flow screen shown in Figure 1-15 . When you create
an SSIS package, you get a lot of tools in the toolbox pane, which is categorized by context based on the selected
design window or views. There are four views: Control Flow, Data Flow, Event Handlers, and Package Explorer. Here
we will discuss two main views, the Data Flow and the Control Flow:
Data Flow Sources (for example, ADO.NET Source, to extract data from a database using
a .NET provider; Excel Source, to extract data from an Excel workbook; Flat File Source, to
extract data from flat files; and so on).
Data Flow Transformations (for example, Aggregate, to aggregate values in the dataset;
Data Conversion, to convert columns to different data types and add columns to the
dataset; Merge, to merge two sorted datasets; Merge Join, to merge two datasets using
join; Multicast, to create copies of the dataset; and so on).
Data Flow Destinations (for example, ADO.NET destination, to write into a database using
an ADO.NET provider; Excel Destination, to load data into a Excel workbook; SQL Server
Destination, to load data into SQL Server database; and so on).
Based on the selected data flow tasks, event handlers can be built and executed in the
Event Handlers view.
Search JabSto ::