Joost Rossum - Extending SSIS with .NET Scripting

Extending SSIS with .NET Scripting — read online for free the complete book (whole text) full work

Below is the text of the book, divided by pages. System saving the place of the last page read, allows you to conveniently read the book "Extending SSIS with .NET Scripting" online for free, without having to search again every time where you left off. Put a bookmark, and you can go to the page where you finished reading at any time.

Light

Font size:

↓

↑

Georgia Tahoma Arial Verdana

Reset

Interval:

↓

↑

Bookmark:

Make

1234567...92

Part I
Getting Started

Joost van Rossum and Rgis Baccaro 2015

Joost van Rossum and Rgis Baccaro Extending SSIS with .NET Scripting 10.1007/978-1-4842-0638-6_1

1. Getting Started with SSIS and Scripting

Joost van Rossum 1 and Rgis Baccaro 1

(1)

XE Baarn, The Netherlands

Electronic supplementary material

The online version of this chapter (doi: 10.1007/978-1-4842-0638-6_1 ) contains supplementary material, which is available to authorized users.

This chapter walks you through a simple scenario where you will use .NET scripting to achieve the required functionality. The hope is that you learn how simple and how powerful it is to implement scenarios with .NET scripts. For each .NET version, new capabilities are added. You will also learn how these versions are correlated to the versions of SSIS.

In this chapter you will look at how to perform basic tasks using SSIS and how to use scripting to make these basic tasks easier.

Performing a Basic Action with SSIS Built-in Components

Learning by doing is often the easiest way to understand functionalities. So lets have a look at how it is possible to build a basic action with .NET scripting. But before you get started, lets set the scene and provide a formal introduction to SSIS.

What Is SSIS?

SQL Server Integration Services (SSIS) is an SQL Server service. The story started in SQL Server 2000 with DTS (Data Transformation Services). SSIS was first released with SQL Server 2005, and it has been enhanced for every version of SQL Server ever since2008, 2008 R2, 2012, and 2014 at the time this book is being written. SSIS is a BI (business intelligence) developers preferred tool for ETL (Extract, Transform, and Load). SQL developers use it for data transformation and DBAs use it for automation of some administrative tasks. Programmers use it to avoid having to do a huge amount of coding.

An SSIS project contains one or more packages. An SSIS project is developed in Business Intelligence Development Studio (BIDS) and on the later versions on SQL Server Data Tools (SSDT) with the BI templates installed. It is a bit confusing as to what tools are needed to develop SSIS packages. So to make it completely clear, here are the facts about SSDT, so that you can get started and create your first SSIS package:

• SSDT is available as a free component of the SQL Server platform and is available for all SQL Server users. It targets SQL Server 2005, 2008, 2008 R2, 2012, 2014, and Azure.
• SSDT full versions and updates are available online at https://msdn.microsoft.com/en-us/library/mt204009.aspx .
• SSDT can be installed stand-alone or together with Visual Studio.

To work with SSIS packages you need to download the BI templates that match your version of SSDT.

• SSDT 2012: https://www.microsoft.com/en-us/download/details.aspx?id=36843
• SSDT 2014: https://www.microsoft.com/en-us/download/details.aspx?id=42313

The following is a list of useful links for downloading the different versions of BIDS and SSDT. This book focuses on SSDT 2013.

• BIDS 2005: http://www.microsoft.com/en-us/download/details.aspx?id=19413 . The Express Edition of SQL Server 2005 does not include BIDS.
• BIDS 2008 and 2008 R2 require Visual Studio 2010.

This web page at https://msdn.microsoft.com/en-us/data/hh297027 sums up the several download possibilities, but the versions listed are a bit confusing.

Development Tool

Figure shows the different elements of SSDT (BIDS has the same default layout but uses different colors).

Figure 1-1.

SSDT development studio

The callouts in Figure highlight the following areas and aspects of the interface:

SSIS Toolbox : Has all available components and tasks.

Variable pane : Where you can create new variables and edit existing ones.

Main design pane : Where you add components and tasks, but also work with package parameters (SSIS 2012 and after) and define event handlers.

Connection Managers pane : For managing all connections to external resources for the current SSIS package.

Solution Explorer : For managing the solution, project, files, connection managers, and project parameters inside the project.

Properties pane : Lists and manages all properties for the selected object.

Control Flow Tasks

An SSIS package consists of one control flow and optionally one or several data flows. SSIS provides three types of control flow elements:

• Containers that provide structures in packages
• Tasks that provide functionality
• Precedence constraints that connect the executables

Figure shows an example of the control flow in the designer pane.

Figure 1-2.

Control Flow design area

The following are the callouts in Figure :

A container that provides the structure of the package and some functionality that manages control flow.

A task that implements the functionality.

A precedence constraint that defines the flow of execution.

Note

As new versions of SSIS are released, new Control Flow Tasks appear and their graphical layouts are slightly different.

Figure shows an example of the available Control Flow Tasks in the SSIS Toolbox.

Figure 1-3.

Control Flow Tasks

As mentioned, Control Flow Tasks contain three types of elements: containers for structure, precedence constraints for connecting the executables, and tasks that provide functionality. From the tasks that provide functionality, there are elements for working with databases, such as the Execute SQL Task, the Bulk Insert Task, and the Analysis Services Processing Task; elements that operate with external systems include the File System Task and the Web Service Task; and database administration tasks include the Backup Database Task and the Shrink Database Task. The Execute Package Task is a task to call other packages. The Data Flow Task manages data transfer between the source and destination with various transformations. The Script Task is available for writing .NET scripts (and is one of the main subjects of this book).

A list of all the available Control Flow Tasks is at http://msdn.microsoft.com/en-us/library/ms139892.aspx .

Also notable, there are some structural elements in the control flow that can be used for grouping and looping. More information about containers in SSIS can be found at https://msdn.microsoft.com/en-us/library/ms137728.aspx .

Data Flow Components

Data flow is probably the most important task in SSIS. Data flow is the task that defines the data transformation from one or more sources into one or more destinations, through various transformations. Data flow is the cornerstone of building ETL and data integration solutions. There is a fundamental difference between data flow and control flow. Whereas control flows use precedence constraints for structuring the flow, data flow components use a data path for flowing data from one element to another. This is illustrated in Figure .

Next page

1234567...92

Light

Font size:

↓

↑

Georgia Tahoma Arial Verdana

Reset

Interval:

↓

↑

Bookmark:

Make

Similar books «Extending SSIS with .NET Scripting»

Look at similar books to Extending SSIS with .NET Scripting. We have selected literature similar in name and meaning in the hope of providing readers with more options to find new, interesting, not yet read works.

Joost Langeveld

Flower Origami

JOOST MINNAAR

Corporate Rebels: Make Work More Fun

Ralph A Rossum

American Constitutional Law 8e, 2-Vol Set: 2-Volume Set

Joost R. Hiltermann

Behind the Intifada

Joost Fontein

Remaking Mutirikwi : landscape, water and belonging in Southern Zimbabwe

Christian Cote

ETL with Azure Cookbook: Practical recipes for building modern ETL solutions to load and transform data from any source

Joost Evertse

Mastering GitLab 12

Joost Augusteijn

Historical Perspectives on Democracies and their Adversaries

Joost Langeveld

Dino-Gami

Mikael Olsson

PHP Quick Scripting Reference

Brian Knight

Knights Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Reza Rad

Microsoft SQL Server 2012 Integration Services: An Expert Cookbook

Discussion, reviews of the book Extending SSIS with .NET Scripting and just readers' own opinions. Leave your comments, write what you think about the work, its meaning or the main characters. Specify what exactly you liked and what you didn't like, and why you think so.