Floating FB popout byF5debug

SQL Server Integration Services (SSIS) – Part 63 – Percentage Sampling Transformation (Selected and Un-Selected Output) in SSIS

 

This is part 63 of the series of article on SSIS

Introduction:

In this article we are going to see how to use Percentage Sampling transformation (Selected and Un-Selected Output) both at a same time in SSIS Packaging. Percentage sampling transformation is used to split the dataset into separate outputs based on the percent and send it to different transformations for processing the dataset. This task is specifically used for data mining; we can divide the data and send it across as per our requirement. Let’s jump start to see this sample how to set the properties of the control.

You can look into my series of article on SSIS at the url - http://f5debug.net/tutorial/ssis.aspx

[more]

Steps:

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see how to use the Percentage sampling to see the flow. Now once the projects is opened drag and drop a source and a Percentage sampling task as shown in the screen below

clip_image001

We can see some red marks on each task which indicates that the tasks are not configured. We need to configure each task so that while execution we can have a smooth process.

Now let’s configure each and every task to execute the package. First let us start with the OLEDB Source as shown in the screen below

clip_image002

Now go to the mappings tab and see the list of columns in the source table which are mapped correctly as shown in the screen below

clip_image003

Now we are done with the source, we need to configure the percentage sampling task now. To do that double click on the task will open the window as shown in the screen below

clip_image004

Here we need to specify the percentage of rows to be affected in this transformation and to proceed further. In our sample we are going to select as 40 as shown in the screen below

clip_image005

Now we are done with the Percentage sampling task, we need to configure the destination section where the results are expected. To do that drag and drop the green arrow to the destination task which we created earlier. It will open a configuration window to select the output name from the percentage sampling task as shown in the screen below. Since we have 2 destinations in our package now we will send across the Selected Output and the unselected output based on our requirement as shown in the screen below

clip_image006

Now we need to select out of the 2 properties which one exactly we require based on our requirement. Here we are going to select as shown in the screen below

clip_image007

clip_image008

Now our screen will have both the Selected and the unselected output as shown in the screen below

clip_image009

Now we need to configure the destination excel as shown in the screens below which is self-explanatory.

clip_image010

clip_image011

Since we have one more destination which is the Flat File destination we need to configure that as well as shown in the below screens

clip_image012

clip_image013

Now we are ready with our package. We need to build and execute it to see the desired result. So our screen will look like below.

clip_image014

Now to build and execute press F5 and we can see the result window as shown in the screen below with the amount of rows affected for each destination’s.

clip_image015

We can see the number of rows affected and used across. To see the result in the excel navigate to the path where we configured our destination and open the excel and the notepad, we can see the result as shown in the screen below

clip_image016

clip_image017

Conclusion:

So in this article we have seen how to use the Percentage Sampling to execute dataset and split based on the percent and uses it across the requirement.

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Advertisement

Random Posts

Most Viewed

BlogStatistic

Total posts: 617This year: 4This month: 2This week: 0Comments: 302

Month List

URL Shortner

Create your own short urls!!!

Site Counter

free counters

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter