SQL Server Integration Services Features

SSIS is one of the powerful features in SQL Server 2005. Technically it is business intelligence feature and you can load data and do some tasks on it. Traditional DTS has been renamed to SSIS as a foundation to Business Intelligence.

This post briefly explains about the tools in SSIS

Import and Export Wizard

You can use SSIS Import and Export  Wizard to move data from any OLEDB data source to a destination. You encapsulate all data in a single transaction.

BIDS Business Intelligence Development Studio

It is the central tool used by the SQL server SSIS developers to design packages.

You can use SSIS as am ETL tool. A core component of SSIS is Package. Package is a collection of tasks that execute in orderly fashion. A package can be saved onto a SQL server in msdb  database. It can also saved as a .DTSX file which is an XML-structured file.

Tasks

  Task can be described as an individual unit of work in package. The following are the tasks available in SSIS

• ActiveX Script Task is to execute traditional DTS packages and ActiveX script in SSIS package.
• Analysis Services Execute DDL Task Excutes a DDL task in Analysis Services.
• Bulk Insert Task Loads data into a table by using the BULK INSERT SQL command.
• Data Flow Task Loads and transforms data into an OLEDB destination.
• Execute Package Task Allows you to execute a package from within a package.
• Execute SQL Task Executes a SQL statement or stored procedure.
• File System Task  can be used to handle directory and file operations.
• FTP Task Sends and Receives files from am FTP site.
• Message Queue Task Send and Receives messages from MSMQ
• Send Mail Task can send a mail message through SMTP.
• Web Service Task Executes a method on a Web Service.
• XML Task Parses an XML file

Data Source Views

It allows you to create logical views of your business data and they can be used un analysis services and report builder. This feature is useful in large complex data models. You can segment the large data into more bite-sized chunks. DSVs are deployed as a connection manager. DSVs allows you to define the connection logic and reuse it across your SSIS packages.

Precedence Constraints

This feature directs the tasks in SSIS package to execute in given order. It directs the workflow of your SSIS package based on given conditions.

Data Flow Elements

Data Flow handles the workflow and transformation of data. Transformations are key components to the Data flow that change the data to intended format. All transformations are done in in-memory.

Conclusion

The main purpose of SSIS is lifting data, transforming it and writing it to a destination. I am going to write few more posts which explains the features in detail.

Share this post :