Creating a SSIS Solution and Understanding its Contents

As discussed in earlier post that SSIS (Sql Server Integratin Services) is a project driven,  hence we need to create a project for the same from BIDS (Business Intelligence Developement Studio),
for this Click on
START > Programs > Microsoft Sql Server 2008 > Sql Server Business Intelligence Developement Studio
Graphically it looks like :

 Now we have created a Project that Looks like :

Understanding the Contents of Integration Services Projects

Now we will discuss the same in details about hte Project explorer of SSIS package.
An Integration Services project manages the object definitions of data sources, data source views, and packages. Separate folders within an Integration Services project store the files for data sources, data source views, and packages

As you can see above there are four main folders:

1. Data Sources
A data source is a connection reference that you create outside a package. A data source represents a simple connection to a data store, which includes all tables and views in the data store.
The advantages of using data sources in Integration Services packages include the following:

1. A data source has project scope, which means that a data source created in an Integration Services project is available to all the packages in the project. A data source can be defined one time and then referenced by connection managers in multiple packages.
2. A data source offers synchronization between the data source object and its package references. If the data source and the packages that reference it reside in the same project, the connection string property of the data source references is automatically updated when the data source changes.

To add a data source reference

1. In Business Intelligence Development Studio, open the Integration Services project that contains the package you want.
2. In Solution Explorer, double-click the package to open it.
3. To make the Connection Managers area available, click the Control Flow, the Data Flow, or the Event Handlers tab in SSIS Designer.
4. Right-click within the Connection Managers area and then click New Connection From Data Source. The Select Data Source dialog box opens, listing the available data source objects.
5. Click the data source object you want to use.
6. Click OK.

2. Data Source Views

 A data source view is a document that describes the schema of an underlying data source. A data source view provides a named, browseable, persisted selection of database objects that can be used to define sources, destinations, and lookup tables for SQL Server Integration Services tasks, transformations, data sources, and destinations. In Integration Services, a data source view is a design-time object that makes it easier to implement the same data source in multiple packages.

 
3. SSIS Packages

 A package is an organized collection of connections, control flow elements, data flow elements, event handlers, variables, and configurations, that you assemble using either the graphical design tools that SQL Server Integration Services provides, or build programmatically. You then save the completed package to SQL Server, the SSIS Package Store, or the file system. The package is the unit of work that is retrieved, executed, and saved.

 

When you first create a package, it is an empty object that does nothing. To add functionality to a package, you add a control flow and, optionally, one or more data flows to the package.

The following diagram shows a simple package that contains a control flow with a Data Flow task, which in turn contains a data flow

4. Miscellaneous
Contains files other than source, data source view, or package files.

 

This is sufficient for this class and I will describe in detail about the Packages and its parts like Control Flow, Data Flow and Event Handlers... till then keep in touch and your remarks will highly be appreciated..