Home > Articles > SQL Server Articles > Data Profiling in SSIS

Data Profiling in SSIS

by Rahul Pyarelal   on Jan 24, 2017   Category: SQL Server  | Level: Beginner  |  Views: 1109    |  Points: 100   
Like this article? Bookmark and Share:
In this article we will explain Data Profiling in SSIS.

It is a Control Flow Task component. This is visual only control flow task. Data profiling task can only profile data which is only SQL Server. He can’t profile data directly on CSV, excel file etc.

First right click on the SSIS Package & Create the “New SSIS Package” in to the application then drag “Data Profiling Task” in to the “Control Flow” as it shown in the below screen short


Then configure “Data Profile Task” in order to do that right click on the “Data Profile Task” and Edit. First configure the “General” as see the following screen short…


Then click on the “Destination” and select the “New File connection”. When you click on the “New File connection” it opens the new window as the following screen short…


When you click on the “New File Connection” it opens the new window and selects the “Usage type” then you change the type of USAGE property Create File or Existing File. When you have not existing file. Then select on the “Create file”. Click on the Browse as see the below screen short…


Than say open in the “Select File” and “OK” in the “File Connection Manager Editer”.

Whenever run the Data profiling task if you want to do always “Overwrite” than Select the “True” in the “OverwriteDestination” you always gate new profile. See the following screen short…


Then configure the “Quick Profile…”. It quickly goes and tacks all the profile. After that go to the “ADO.NET Connection” and create a new connection as see the following screen short…


When you click on the “New” then new window will open. Then put the Server name and Database name then click on the “ok” as see the following screen short…


Now, give the “Table or View” name which in the SQL Server. Then check all the box and click to “ok”.


Then all the data will go into the “Profile Request” and click “ok”. He has taken all the profile request and add into the Data profile task. It sees the following screen short…


“Set as start up object” in the solution and run the application.

OUTPUT

Profile output is located, which is given is the “File Connection Manager Editor”.


See the output in the more user friendly way for that go to “Data Profiling Task Editor” and click on the “Open Profile Viewer”.


Candidate key Profile --> it is shows that unique record. It shows that which columns of data are unique so it is made as a primary key or candidate key.

Column Length Distribution profiler --> what is the length of columns.

Column null ration profile --> which of the file are null

Column Pattern profiles --> what is the pattern of the column. What kinds of data available in your column.

Column Value distribution profile --> you can know that what are the most occur value. How the value is distributed in the data.

With this Data Profiling can be performed through SSIS.

Want to learn MSBI from start with project based approach below is the starter video from that series: -



Like this article? Bookmark and Share:

Most viewed Articles

User Comments


No response found, be the first to review this article.

Submit feedback about this article

Please sign in to post feedback

Latest Posts