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..

 

 

Basics of SSIS (Sql Server Integration Services)

With the Release of the SQL Server 2005 a new and Advance form of the DTS has been launched and it is named as SQL Server Integration Service, Basically SSIS is a complete ETL (Extraction Transformation and Loading) tool.
It is very interesting to know that without writing even a single line of Code you can perform many complex tasks like :

• Data Bulk-Insert from Flat file to Sql Server Database.
• Email Notifications.
• Manipulation of the data.
• Data Transfer from Hetreogenous Data sources to SQL Server database.
• SQL Server Administrative task and much more..

There are major three main modules of the SSIS

1. Control Flow
2. Data Flow
3. Event handlers

Each of them play a vital role in the development of the ETL operations required for your organization.
Other Component of the SSIS are :

• Connection object. This is mainly for the setting connections to the Destination and Source Database.
• Transformation Objects:  These tasks objects helps you to convert the source data from one form to another in desired format.
• Source and Destination Databases/Flat Files: This is self explanatory.
• Notification Tasks:  Like Email Notification task etc.
• FTP task and much more..

This was just to give you the glimpse of the SSIS : A basic Introduction., In later blogs you will find detailed analysis of each and every component.

Putting Message in the MSMQ through the C#.net

Before I start with the Actual Code used to Write and Read a Message, I would like to give brief overview on the MSMQ

Microsoft Message Queuing or MSMQ is a Message Queue implementation developed by Microsoft and deployed in its Windows Server operating systems since Windows NT 4 and Windows 95. The latest Windows 7 also includes this component. In addition to its mainstream server platform support, MSMQ has been incorporated into Microsoft Embedded platforms since 1999 and the release of Windows CE 3.0.
MSMQ is essentially a messaging protocol that allows applications running on separate servers/processes to communicate in a failsafe manner. A queue is a temporary storage location from which messages can be sent and received reliably, as and when conditions permit. This enables communication across heterogeneous networks and between computers which may not always be connected. By contrast, sockets and other network protocols assume that direct connections always exist.


MSMQ has been available to developers on Microsoft platforms since 1997,[2] and has commonly been used in enterprise software built with Visual Studio, both in the native pre-.NET incarnation (version 5 and 6), and in Visual Studio .NET. Microsoft also has incorporated MSMQ in its messaging technology framework, Windows Communication Foundation (WCF). Under WCF, MSMQ can be used to provide secure, reliable transport with a unified programming model compatible with other communications standards.

MSMQ is responsible for reliably delivering messages between applications inside and outside the enterprise. MSMQ ensures reliable delivery by placing messages that fail to reach their intended destination in a queue and then resending them once the destination is reachable. It also supports security and priority based messaging. Dead letter queues can be created for looking at messages which timed out or failed for other reasons.

Following is the Code:
public class MSMQ_rw