Click here to Skip to main content
15,867,997 members
Articles / Database Development / SQL Server

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

Rate me:
Please Sign up or sign in to vote.
4.60/5 (7 votes)
1 Sep 2011CPOL11 min read 27.3K   665   10   1
In this article we will explore on the new features that Denali CTP3 has offer us from SSIS perspective

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

Table of Content

  1. Introduction
  2. Background
  3. SSIS New Features And Enhancements
    1. SSIS Expression Task
    2. DQS Cleansing Component
    3. Load files with variable row formats
    4. Displaying Success and failures in different styles
    5. Sort packages by name
    6. Shared Connection Managers
    7. New expression language functions(Left,Token,TokenCount)
    8. Package Parameter Tab
    9. Removal of Data Sources and Data Source Views folders
    10. Solution Explorer now have Project Parameters node
  4. Conclusion

Introduction

After the release of Denali CTP 1 on 8th November, 2010, the next big hit was on 12th July 2011 when Denali CTP 3 has been launched. CTP3 is available both in 32-bit and 64-bit versions. As usual, 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 that has been included in this CTP.

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 CTP 3 has already offer us from SSIS perspective.Earlier we have seen the enhancements made in Denali CTP1 . The subsequent articles will focus on the enhancements made in the other areas.

You can read my other articles on Denali as given under

  1. An Introduction to Sql 11 (Code Name Denali) –Part I (SSMS Features in CTP 1)
  2. An Introduction to Sql 11 (Code Name Denali) –Part II (T-Sql Features in CTP 1)
  3. An Introduction to Sql 11 (Code Name Denali) –Part III (SSIS Features in CTP 1)
  4. An Introduction to Sql 11 (Code Name Denali) -Part IV (Contained Database in CTP 1)

SSIS New Features

I.SSIS Expression Task

We have a new Task component in the toolbox by the name Expression Task. It's purpose is to assign the result of a SSIS expression to a variable at runtime.

Let us see this into action.We will form an expression that will give the age of a person.

Open bids and in the toolbox of the Control Flow panel, we will find the Expression Task

1.jpg

Now let us create a package level variable.First let us right click on the designer and choose Variables

2.jpg

Then in the variable window that appears, create a variable by the name Age of type Int32 and it's scope will be Package

3.jpg>

Drag and drop an Expression Task. Double click and the Expression Builder will open where we will find the package variable created

4.jpg

In the expression text box, let us write the below expression

@[User::Age] = DATEDIFF("YY", (DT_DBTIMESTAMP)"01/01/1999",GetDate())

and click on the Evaluate Expression button.

5.jpg

Click OK.Next drag and drop a Script Task component.

6.jpg

Click OK button. Then in the ReadOnlyVariable properties, let us put the variable that we created

7.jpg

Next, let us click the Edit Script button and add the below code

public void Main()
{
	
 string displayMessage = "Your age is {0}";
 MessageBox.Show(string.Format(displayMessage,Dts.Variables[0].Value.ToString())); 
 Dts.TaskResult = (int)ScriptResults.Success;
}

Run the application and we will find the result

8.jpg

N.B.~We should take care of the fact that while creating the expression, we should not give any space;else it will complain error as shown under

9.jpg

II.DQS Cleansing Component

Another new component has been added as part of the transformation componet. It is the Data Quality Services (DQS) Cleansing transformation component. It helps us to cleanse our data based on the knowledge base we will build. This article will not focus on DQS as it is a seperate topic altogether and will be published soon. In this we will focus on creation of our own knowledge base and then validating the record based on that.

We need to create our own Knowledge base.

Our intension is that, our knowledge base will have Country domain values and the rules will be such that the country length should be more than or equal to 5 and should not be abbreviated. Also if the user record has "England" as the value , it will be corrected to "United Kingdom".

For doing so, let us follow the below steps

Connect to the DQS server

10_new.jpg

Once connected properly, next click on the New Knowledge base button that comes under the Knowledge Base Management category

11_new.jpg

Once done, we can now create the new knowledge base

12_new.jpg

Let us enter a Domain name, give some description and click on the create button.Next create a domain.

13_new.jpg

After clicking on the Create a domain button, we will get the Create Domain popup window.We need to enter a domain name and leave the reset as it is. Click on OK button.

14_new.jpg

To the right hand side we can see our options for setting the Data Quality rules that apply to the domain in question

15_new.jpg

Next we should create some rules that will be applied. For that, let us click on the Domain Rules tab

16_new.jpg

Next we need to click on the Add a new domain rule button for creating a new domain rule.Once clicked,we will get the below screen

17_new.jpg

Enter Name,Description and from the Build a rule drop down choose "Length greater than or equal to" and set the value to 5 as shown under

18_new.jpg

Along with this, we also want to apply that no country name should be abbreviated.For that, let us click on the Adds a new condition to the selected clause button

19_new.jpg

From the dropdown , let us choose “Value does not contain” and set the value to "."(dot)

20_new.jpg

After that click on Apply All Rules button.

21_new.jpg

Click YES and we will receive the success message stating that the domain rule has been applied successfully

22.jpg

Once we have set up the domain rule, the next step will be to add the domain values.So let us visit the Domain Values tab and click on the Add new domain value button

23_new.jpg

Now let us add some domain values.Observe that, if anyone gives the value of England, the output will be corrected to United Kingdom.

24_new.jpg

Once done, click Finish button.

As a last step , we need to click no the Publish button and the KB(Knowledge base) will be available publickly

25.jpg

And we can make out that “OurTestDomain” has been created

26.jpg

For more information about DQS Knowledge Bases and Domains, we can refer to this article

So, our knowledge base is now prepared. Next we need to prepare our source data. In this example we will use Excel as our data source.The excel data source is as under

27.jpg

Open bids and drag and drop a Data flow task onto the Control Flow designer.In the dataflow designer, first of all make an Excel Source and give the appropriate File name and path and the needed configuration.Next let us drag and drop a DQS Cleansing component.

28.jpg

Double click on that to open DQS Cleansing Transformation editor.

29.jpg

We need to give the Connection manager. So click on the New Button and the connection manager window opens.

30.jpg

Specify the server name and then click on the test connection to get the successful message.

31_new.jpg

Next we need to specify the Data quality Knowledge base. We will choose “OurTestDomain” which we just created. And the available domain will be listed here.

32_new.jpg

Next let's visit to the Mapping column

33.jpg

Check the checkbox next to Country Input column.

34.jpg

From the Domain dropdown,choose Country. Give some name to Corrected and Status Output alias.Click OK.Add a Row sampling and a data viewer.

35.jpg

Run the package.The output in the DataViewer is as under

36.jpg

The first 4 rows is easy to understand as it has acted in the way we have defined in the rule. Country lengthmust be >=5 and hence USA Country status is Invalid. It cannot have any (.) hence, U.K. is invalid. The name England must be changed to United Kingdom which is being reflected. Since there is no enter for Vatican City, hence the Country status is “Unknown”.And it is a success

37.jpg

N.B.~This operation is an asynchronous one as can be reveal from the below image

38.jpg

III.Load files with variable row formats

It was not at all a smooth approach to load file contents where the row formats are variable.Consider the below example

39.jpg

In this example,we can figure out that we have some parent-child content in the text file.The headers contains the information about the managers while the detail section about the employees that works under the managers

In the Pre-Denali CTP 3 approach, in the Flat File source component, we could have previwed the content as under

40.jpg

As can be figured out that, the record set has been messed up as highlighted in red color.One of the way to handle the same in PreDenali CTP 3 approach is to change the source as under

41.jpg

And then the preview for the Flat file source component will be as under

42.jpg

Suppose, now we want to segregate the data as Manager records and ManagerEmployee Records, so we can go ahead with by adding a conditional split componet and specifying the below condition

43.jpg

Then adding RowSampling as under, we can get the records as

44.jpg

But the case in different in Denali CTP 3. When we load such a file, the preview looks as under

45.jpg

Running the package with the same design, yields the below result

46.jpg

IV.Displaying Success and failures in different styles

In Pre-Denali CTP 3 approach, the way of representing a success / failure is as under

47.jpg

But Denali CTP 3 has changed that to

48.jpg

V.Sort packages by name

Another small feature is that, we can now sort packages by their name as shown under

Before sorting package name

49.jpg

After sorting package name

50.jpg

VI.Shared Connection Managers

In earlier version , it is not possible to share connection among the packages. For example ,in Package1, we are using a Flat file source component.

51.jpg

As can be seen that we have a Connection Manager by the name TxtFileConnManager in Package1.dtsx.Now let us create another package by the name Package2.dtsx

52.jpg

As can be figure out that there is no option to reuse the existing connection. We need to create a new connection.

But in Denali CTP 3, we have the Connection Manager by which we can share the connection

53.jpg

Clicking on the Connection Manager, opens up the SSIS Connection Manager window

54.jpg

Choose FLATFILE and click on ADD button and give the Connection Manager Name.

55.jpg

Click OK.

56.jpg

As can be seen that our new connection manager has been added. Since it is shared so we can access it in any package.Let us open Package1.dtsx and we will find the Connection Manager at the down

57.jpg

The same applies for Package2.dtsx.

VII.New expression language functions(Left,Token,TokenCount)

We have three new functions in the expression language viz. Left , Token and TokenCount.

58.jpg

Let us analyze these functions one by one

Left:Returns the left part of a character string with the specified number of characters.This function is already there in TSql for a long time. Also it's counter part Right was already available in SSIS for a long time.

So if we run the below code in the Expression Task, we will get the following output

@[User::Result1] =  LEFT("Test for Left function", 4)

   //output : Test

59.jpg

The left function in this case strips out the first 4 character from the supplied string

Token:Returns the specified occurrence of a token in a string

So if we run the below code in the Expression Task, we will get the following output

@[User::Result1] =  TOKEN("Test for Token Function"," ",2)

//output : for

60.jpg

Because for appeared in the second place

TokenCount:Returns the number of tokens in a string

So if we run the below code in the Expression Task, we will get the following output

@[User::Result2] =  TOKENCOUNT("Test for TokenCount function"," ")

//output : 4

61.jpg

Because there are 4 tokens

We can even combine Token and TokenCount as under

@[User::Result1] =  TOKEN("Test for Token Function"," ",  TOKENCOUNT("Test for TokenCount function"," "))

//output : Function

Because, TokenCount function yields 4 and in the fourth place , we have "Function"

VIII.Package Parameter Tab

We now have a dedicated tabe for Package-scoped Parameters within the designer

62.jpg

IX.Removal of Data Sources and Data Source Views folders

In SSIS 2008 R2

63.jpg

In Denali CTP 3

64.jpg

X.Solution Explorer now have Project Parameters node

In Denali CTP1, Project Parameters were visible upon right clicking in the Solution Explorer.But in Denali CTP 3 , they have their own seperate node

65.jpg

Conclusion

In the current series we have seen some of the cool enhancements made in SSIS Denali CTP 3 like Expression Task,DQS Cleansing Component,Load files with variable row formats , New expression language functions(Left,Token,TokenCount) etc.But that is not all.There are many more to explore.I will keep on adding those topics as and when I will explore them. So stay tune and share your opinion about the article.

License

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



Comments and Discussions

 
QuestionDenali C# Parameter syntaxis Pin
ReneCat4-Oct-11 7:28
ReneCat4-Oct-11 7:28 

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.