Click here to Skip to main content
15,860,859 members
Articles / Database Development / SQL Server / SQL Server 2008

An Introduction to Sql 11 (Code Name Denali) –Part III (SSIS Features in CTP 1)

Rate me:
Please Sign up or sign in to vote.
4.89/5 (10 votes)
9 May 2011CPOL14 min read 26K   5   2
In this article we will explore on the new features that Denali has offer us from SSIS perspective

An Introduction to Sql 11 (Code Name Denali) –Part III (SSIS Features in CTP 1)

Table of Content

  1. Introduction
  2. Background
  3. SSIS New Features And Enhancements
    1. SSIS Toolbox Improvements
      1. Introduction of Favorites and Common Category
      2. Ability to organize components according to the user preference
      3. Information Window
    2. Addition of SSIS toolbox button
    3. Addition of Parameter and Variable button
    4. Undo and Redo
    5. Zooming Option in the Package Designer
    6. Multiline Annotations
    7. Collapsible Grouping in the Data Flow Designer
    8. Simplified Data Viewer Configuration
    9. Source and Destination Assistant
    10. Resolve References
    11. Legacy Deployment Model Conversion
    12. SSIS DB
    13. SSIS Catalog
    14. Environments, Environment Variables, and Environment References
  4. Conclusion

Introduction

One of the hottest and awesome developments by Microsoft in the technology field was come into picture on 8th November, 2010 when they released the Community Technology Preview 1 (CTP 1) version of Sql Server 2011(Code name Denali). The CTP1 is available both in 32-bit and 64-bit versions. As expected, Denali has brought some new features for Sql lovers may that be developers, Administrators or Business Intelligence (BI) professionals. In this series we will explore on some of the new features of SSIS. In Part I we have seen some of the enhancements and new features of SSMS.Part II has given us the details of the enhancements and new features of TSql.About the rest of the features will look into the subsequent series.

Background

In the last few years, Microsoft has brought many technologies under the developers’ hood. A drastic change has been made in the Sql Server jargon with the advent of Sql Server 2005(code name Yukon) and in the subsequent releases like Sql Server 2008(code name Katmai) and Sql 11(code name Denali), the same pace has been kept with introduction to new features and enhancements as well as improvements.In this article we will explore some of the new features that Denali has already offer us from SSIS perspective. The subsequent articles will focus on the enhancements made in the other areas.

SSIS New Features

I.SSIS Toolbox Improvements

In Sql 11(code name: Denali), the new SSIS toolbox has been organized in a much meaningful fashion.

a.Introduction of Favorites and Common Category

The first thing that comes into the notice is the introduction of the two new categories viz Favorites and Common which are already pre-loaded items inside the toolbox. This is applicable for the Control flow, Data flow, Event handlers and Package Explorer.

The below picture shows the Favorites and Common categories for the control flow tab.

1.jpg

The below figure shows the introduction of the Favorites and Common categories for the Control flow tab.

2.jpg

As can be figured out that, we have some new additions to the toolbox like Destination and Source assistant (found in the Favorites Category) and Data Correction(found in the Other category). Description about these components will be given later.

In the Package Explorer section, however, these categories are disable.

3.jpg

And that is rather correct. But my personal opinion is that there is no need for these categories to be present over here as they does not make any sense. Anyways, let us proceed with the other stuffs.

b.Ability to organize components according to the user preference

Now we have the ability to move components back and forth among the categories. In the Favorites category, if we select any component and then right click on it, the following popup comes up stating that the Favorites components can be moved either to the Common or Other Destinations Category.

4.jpg

Like wise, the Destination category items can be moved to Favorites and Other Transform categories. The Other Transform ,Other Sources and Other Destination category items can be moved to Favorites and Common categories.

Suppose we want to move the Excel Destination from the Other Destination to the Favorites Category.So, let us choose the Excel Destination in the Other Destination Category, right click and from the popup menu that appears, let's click on the Favorites. The Excel Destination will disappear from the Other Destination Category and will sit in the Favorites section.

5.jpg

However, if we want to reset it back, then there are two options

Option 1:   Click on the Excel Destination and then right click on it and from the popup that appears, click on the Move to Other Destination

6.jpg

Option 2:   Click on the Refresh Toolbox Defaults to restore the same.

7.jpg

c.Information Window

This is again a new addition, which gives the information about the components. If no item is selected , then the associated help button will be disabled.

8.jpg

Suppose we want to know about Destination Assistant. For this, we need to select the item and the information about the same will appear in the information window. Also the help button will be enable.

9.jpg

Clicking on the help button will open the below window and information about the component can be found either from online MSDN or from local.

10.jpg

II.Addition of SSIS toolbox button

We can open the SSIS Toolbox by clicking on the SSIS Toolbox button which is found on the top-right corner of the package design surface.

11.jpg

III.Addition of Parameter and Variable button

Again a small addition is the new Parameter and Variable button just preceding the SSIS tool box button.

12.jpg

IV.Undo and Redo

The undo(Ctrl+Z) and redo(Ctyrl + Y) options are now available where the actions can be undone or redone.

13.jpg

V.Zooming Option in the Package Designer

The package designer has a zooming option by using which we can zoom in and zoom out the package view. The default being 100%.

14.jpg

VI.Multiline Annotations

Another small addition to the SSIS cap. Consider the below

15.jpg

Upon clicking on the Add Annotation, we can add our annotations

16.jpg

VII.Collapsible Grouping in the Data Flow Designer

In Denali, we have the option to group the components both in the control flow and data flow. The advantages are being listed as under :

  1. Space conservation in the workflow designer.
  2. Cleaner package design
  3. Easy to understand the operational flow
  4. Reduce the complexity of deciphering large and complicated packages.
Let us see how it works. Consider the below diagram

17.jpg

It is a very simple package where we have a OLEDB source and two flat File Destinations. First of all we are sorting the input record by using the Sort component present inside the Common category which is then passed onto the Conditional split component where the sorted records will be splited based on the conditions and then depending on the conditions these will be written into the flat files via the flat file destinations.

But if we closely look into the package design, it looks a bit clumsy (at least to me). Four flows are being depicted here. However, it can be organize in a better fashion by using grouping.

Let us select the OLE DB Source and Sort component by pressing CTRL and then right click to bring in the context menu. From the context menu, let us select Group as shown under

18.jpg

Click on the Group menu and then give a proper name to the group (here source). At this stage, the design should look as under

19.jpg

Now, let us group the Flat file destinations by following the same define procedure and the final look of the package will be

20.jpg

As can be make out that, the package now looks much cleaner than the one before and readable too.If we want to ungroup, and then select the particular group, right click to bring the context menu and choose Ungroup. The below figure will show it for the Source group

21.jpg

Grouping feature, though simple to invoke, is very handy in making a package design as it reduces the complexity of the flow to understand. Also we can create nested grouping .As it is a design time feature, so it hardly has any effect on the runtime package execution.

VIII.Simplified Data Viewer Configuration

In Denali, the data viewer has been simplified a lot. We now have the option to enable or disable the data viewer on the dataflow path. It supports only the Grid view and the earlier stuffs like histogram and scatter plot views has been removed.

Let us consider the previous example into action

20.jpg

Now let us select the data flow path between the Source and the conditional split and then right click on the data flow path to invoke the context menu from where we need to choose the Enable Data Viewer option as shown below.

22.jpg

Once done, the data viewer appears as below in the data flow path

23.jpg

Kindly note that, the same can be done by selecting the data flow path and pressing CTRL + F9.

Now, let us visit the Data Viewer Path Editor either by double clicking on the DataViewer icon or by Right clicking on that and clicking on the Edit… option.

24.jpg

The General table gives the general information about the current data viewer.In the Metadata tab, we can view the column metadata.

25.jpg

Now let us view the Data Viewer tab which is as under

26.jpg

The Unused columns area display the list of columns which are not yet been chosen to be displayed while the data viewer will show the data while the package will run.The displayed column area lists the columns that will participate while the data will be viewed from the Data Viewer at run time.Once the application is run, the data viewer will display the below

27.jpg

Which is the result of our sorting operation.

Data Viewers can be disabled, by clicking on the Disable Data Viewer option

28.jpg

IX.Source and Destination Assistant

These two are the new additions to SSIS Data flow component. Basically these two components are brought into place in order to make the common data flow sources and common data flow destinations a bit handy for the SSIS developers.

They make their presence in the favorites section

29.jpg

Let us observe them individually in detail.

Source Assistant

This component picks data from various sources. The assistant will help to easily choose a source (from the available data source list) and associate with a proper connection manger.

Let us drag a Source Assistant Component in the Data flow designer and the Add New Source dialog will appear

30.jpg

As can be make out that, initially only the installed components in the system are being listed. Let us uncheck the Show installed only checkbox will bring up all the available source components

31.jpg

Destination Assistant

This component sends data to various destinations. The assistant will help to easily choose a destination (from the available destination list) and associate with a proper connection manger.

Let us drag a Destination Assistant Component in the Data flow designer and the Add New Destination dialog will appear

32.jpg

As can be make out that, initially only the installed components in the system are being listed. Let us uncheck the Show installed only checkbox will bring up all the available destination components

33.jpg

A simple experiment with Source and Destination assistant

In this simple example, we will take Sql Server both as our source and destination component.

Our source table has two fields i.e. Id of type int and a Name of type varchar(50) column.

We will populate some records into the source table in any random fashion. The destination table will have the source schema as that of the source table but our aim is to insert the records into the destination table after sorting them by names in ascending order.The source table looks as under with the records after population

34.jpg

First drag and drop a Data flow task in the Control flow designer.In the Add New Source dialog, choose the Sql Server component and click on the OK button that will bring up the connection manager

35.jpg

Click OK button.The OLEDB source component will appear in the data flow designer

36.jpg

Rename this to Source and right click on the component,click Edit and that will bring up the OLEDB source Editor where we will specify our source table name (here tbl_Source).

37.jpg

Click OK.Next from the Common Section drag and drop a Sort component and sort it by Name. Also connect the OleDB source component with the Sort component.

38.jpg

Click OK.Next drag and drop a Destination Assistant into the data flow designer

39.jpg

As can be seen that our connection string is already available. Click OK. Connect the Sort Component with the OleDB destination component. Right click on the OleDB Destination component, choose the destination table name (tbl_Destination here) and map the columns.

40.jpg

Click OK.Also add a data viewer between the nodes of the Sort and the OleDB destination component.At this stage our package component will look as under

41.jpg

Now let us build and Run the package. The data viewer displays the below

42.jpg

Click on the 43.jpgbutton and in the designer we will find that all the components are marked as green which indicates that the operation has become successful

44.jpg

Now let us go back to the tbl_Destination and the result is as under

45.jpg

X.Resolve References

Consider the below example

46.jpg

We have a Flat File Source, a sort component that will sort the records as per the Name column and finally will write the results into a Flat File Destination. The source file (source.txt) is as under

Id	Name	Age	Sex
1	Niladri	25	M
2	Arina	22	F
3	Deepak	28	M
4	Sachin	27	M

If we run the package after properly mapping the columns and appropriate data path allocations, then the final result will be written into the destination file (destination.txt) whose result set will be as under

Id	Name	Age	Sex
2	Arina	22	F
3	Deepak	28	M
1	Niladri	25	M
4	Sachin	27	M

The Sort Transformation Editor looks as under

47.jpg

Indicating that the sort has happen based on the Name column in ascending order.

Now let us rename the "Name" column in the source file (source.txt) to "New Name" as under

Id	NewName	Age	Sex
1	Niladri	 25	M
2	Arina	 22	F
3	Deepak	 28	M
4	Sachin	 27	M

And let us add a new flat file source whose data source is the source.txt file. The designer will now look as under

48.jpg

As can be seen that a red (X) has appear in the data flow path between The Flat file source and the Sort component and the error message reads as "There are some mapping errors on this path".

At this stage if we open the Sort Transformation Editor, we will notice that there is a column name mismatch because of which we got the error.

49.jpg

In order to resolve this column reference error, the new Resolve Reference editor comes into play. It helps to map the unmapped output columns with unmapped input columns.

Let us see how this is done.

Right click on the data flow path between the Flat File Source and the Sort component and from the context menu click on the Resolve References.

50.jpg

Alternatively, we can double click on the Red(X) Cross to do the same.This will bring the Resolve Reference editor dialog as shown below

51.jpg

As can be figure out from the Resolve Reference Editor dialog that it has the following components

Component Name Marked in figure as Description
Unmapped Output Columns [Source] 1 The source columns that are to be mapped.
Mapped column(Source) 2.a The source columns
Mapped column(Destination) 2.b The destination columns
Unmapped Output Columns[Destination] 3 The destination columns that are not yet mapped

What we will do here is that we will drag the NewName from the Source component of Unmapped Output Columns into the Source of Mapped Columns.Similarly, we will drag the Name from the Destination component of Unmapped Input Columns into the Destination of Mapped Columns.

52.jpg

Alternatively we can choose from the dropdown list of the Source and Destinations of the Mapped columns

53.jpg

If we click on the Preview Changes button, the Preview Resolve References editor will open as under

54.jpg

where the Column names has been resolved.Click on OK to return to the designer and the references has been resolved

55.jpg

The Sort transformation editor now shows that the column names are now same

56.jpg

The package will now run properly.

Conclusion

In the current series we have seen some of the enhancements made in SSIS like the addition of Favourite and Common Categories, collapsible grouping, the introductin of source and destination assistant etc. But that is not all. There are more to explore like SSISDB and Catalog, Legacy deployment model conversion etc. I will keep on adding those topics as and when I will explore them.So stay tune and share your opinion about the article.

Thanks for reading

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
GeneralMy vote of 5 Pin
quochuy29810-May-11 3:38
quochuy29810-May-11 3:38 
GeneralMy vote of 5 Pin
Rhuros9-May-11 22:09
professionalRhuros9-May-11 22:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.