.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Percentage Sampling Transformation (Selected and UnSelected Output) in SSIS - Part 63

Posted By:Karthikeyan Anbarasan       Posted Date: May 18, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

In this article we are going to see how to use Percentage Sampling transformation (Selected and UnSelected Output) both at a same time in SQL Server Integration Services (SSIS) Packaging.
 

Introduction:

In this article we are going to see how to use a Percentage Sampling Transformation (Un-Selected Output) in SSIS Packaging. A 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 of how to set the properties of the control.

You can also read my other articles on SSIS from Here


Steps:

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


USelOutin-SSIS1.gif

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 during execution we 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:


USelOutin-SSIS2.gif

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: 

USelOutin-SSIS3.gif

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


USelOutin-SSIS4.gif

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:


USelOutin-SSIS5.gif

Now we are done with the Percentage Sampling task; next 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:


USelOutin-SSIS6.gif

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:


USelOutin-SSIS7.gif

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


USelOutin-SSIS8.gif

USelOutin-SSIS9.gif

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.


USelOutin-SSIS10.gif

Now to build and execute press F5 and we can see the result window as shown in the screen below:


USelOutin-SSIS11.gif

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


USelOutin-SSIS12.gif

Conclusion:

So in this article we have seen how to use the Percentage Sampling (Un-Selected Output) to execute a dataset and split based on the percent and use it across the requirement. 


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend