Wednesday, January 20, 2016

How to use data profiling task in ssis

Data profiling Task is used to find potential issues and data quality in the SQL Server table data. This task will not work on other data sources or third party applications data to run this task you must have table creation permissions on the temp db.
Configuring the Data profiling task:-
Go to connection manager right click a create a connection to target server.
And select target database and click ok

Drag data profiling task from SSIS Tool box to control flow and double click on it.
Go to general and destination type select file connection
In destination select create new connection.
Select usage type as create file. Select destination give name to it and click ok
Click on Quick profile
Select ado.net connection and select your connection string and select the table which you want perform profiling. Select the profile which you want to profile and click ok go to profile requests this is another way configuring profiling tasks click ok.
Output xml file will be generated in destination folder. Open the data profiler task and click on open profile viewer. You will see below window which shows statistics of table