Tuesday, August 18, 2015

Data Profiling Task

Data Profiling Task


---> A new task introduced with SQL Server 2008
---> This task can be used by SSIS developers to easily and effectively profile through source system to understand the quality of data before it can be finally loaded to a data warehouse using the ETL process. 
---> There are 8 inbuilt data profiles available within Data Profiling Task. 
---> Among the 8 profiles available, 5 can be used to analyze individual columns and the remaining 3 can be used to analyze multiple columns or relationships existing between columns and tables. 
---> It internally uses the ADO.Net connection to connect with SQL Server. 
---> It returns an XML output and can be viewed by Data Profiler Viewer utility which is available at “C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DataProfileViewer.exe.

EXAMPLE

1. Create File Connection Manager



2. Pull Data Profiling Task and set following properties


3. Click on Quick Profiler and have settings like this.


4. Execute the package and .xml file will be generated.
5. This file can be viewed by .exe mentioned above. It will look something like this.