Floating FB popout byF5debug

SQL Server Integration Services (SSIS) – Part 65 – Row Sampling (Un-Selected Output) Transformation in SSIS

 

This is part 65 of the series of article on SSIS

Introduction:

In this article we are going to see how to use Row Sampling transformation in SSIS Packaging. Row sampling is used to randomly select some rows and move it as output to the required process as and when required to divide the rows. Example of using this transformation is say example if we want to select some randomly 10 users of a community for a random prize then we can use this transformation. In this process we are going to see an example on how to use this process for the unselected output values.  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

Now we can see the red mark on to the control which indicates that the controls are yet to be configured. Now let us configure the source (refer to my previous articles on how to configure OLEDB source) Now your output will look like before

clip_image002

Now we need to configure the Row Sampling task, double click the task will open the screen as below

clip_image003

Here we can specify the number of random rows can be selected and the name for the Sample output selected and unselected. Since in our example we are going to see the Unselected Output we will see on how to use it. Now after specifying the number of rows just clicks on OK button. Now drag and drop the Flat File Destination as shown in the below screen

clip_image004

Now drag the green line from the Row Sampling to the Flat File destination task. It will open a window as shown in the below screen

clip_image005

Here we have selected the Unselected output as the Output to get the data as we can see in the screen above. Now click on OK to do the further configuration of the destination as shown in the below screen

clip_image006

Once the configuration is done we can see the screen as below

clip_image007

Now our package is ready to execute. Press F5 and execute the project we can see the screen as below which indicates that the package is executed successfully

clip_image008

We can see only 81 rows are randomly selected and passed to the destination. Which means out of the 10 selected rows remaining 81 rows which are not selected have been processed. We can see the output by opening the file which we gave in the destination configuration section. You can see the result as shown in the screen below

clip_image009

Conclusion:

So in this article we have seen how to use the Row Sampling (Unselected Output) to execute dataset and split based on the number of rows 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

Random Posts

Most Viewed

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