An Introduction to Sql 11 (Code Name Denali) –Part III (SSIS Features in CTP 1)
Table of Content
- Introduction
- Background
- SSIS New Features And Enhancements
- SSIS Toolbox Improvements
- Introduction of Favorites and Common Category
- Ability to organize components according to the user preference
- Information Window
- Addition of SSIS toolbox button
- Addition of Parameter and Variable button
- Undo and Redo
- Zooming Option in the Package Designer
- Multiline Annotations
- Collapsible Grouping in the Data Flow Designer
- Simplified Data Viewer Configuration
- Source and Destination Assistant
- Resolve References
- Legacy Deployment Model Conversion
- SSIS DB
- SSIS Catalog
- Environments, Environment Variables, and Environment References
- Conclusion
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.
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.
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.
The below figure shows the introduction of the Favorites and Common categories for the Control flow tab.
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.
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.
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.
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
Option 2: Click on the Refresh Toolbox Defaults to restore the same.
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.
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.
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.
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.
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.
IV.Undo and Redo
The undo(Ctrl+Z) and redo(Ctyrl + Y) options are now available where the actions can be undone or redone.
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%.
VI.Multiline Annotations
Another small addition to the SSIS cap. Consider the below
Upon clicking on the Add Annotation, we can add our annotations
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 :
- Space conservation in the workflow designer.
- Cleaner package design
- Easy to understand the operational flow
- Reduce the complexity of deciphering large and complicated packages.
Let us see how it works. Consider the below diagram
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
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
Now, let us group the Flat file destinations by following the same define procedure and the final look of the package will be
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
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
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.
Once done, the data viewer appears as below in the data flow path
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.
The General table gives the general information about the current data viewer.In the Metadata tab, we can view the column metadata.
Now let us view the Data Viewer tab which is as under
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
Which is the result of our sorting operation.
Data Viewers can be disabled, by clicking on the Disable Data Viewer option
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
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
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
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
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
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
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
Click OK button.The OLEDB source component will appear in the data flow designer
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).
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.
Click OK.Next drag and drop a Destination Assistant into the data flow designer
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.
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
Now let us build and Run the package. The data viewer displays the below
Click on the button and in the designer we will find that all the components are marked as green which indicates that the operation has become successful
Now let us go back to the tbl_Destination and the result is as under
X.Resolve References
Consider the below example
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
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
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.
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.
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
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.
Alternatively we can choose from the dropdown list of the Source and Destinations of the Mapped columns
If we click on the Preview Changes button, the Preview Resolve References editor will open as under
where the Column names has been resolved.Click on OK to return to the designer and the references has been resolved
The Sort transformation editor now shows that the column names are now same
The package will now run properly.
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
