Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

Microsoft sql server "frequency report"

Hello there!

I've been doing a lot of "initial analysis" on data files we receive from external entities.  Part of what I do with that data is to look at it and see what we have and describe it.  

I like to work in SSMS but find myself often using other tools to run "frequencies" (e.g., SPSS or AgileDataSoftware FileQuery").  Is there a function in SSMS that would allow me to run frequencies on data without having to define each field etc?  Something I could quickly run against a table that would tell me for each field in the table the count of distinct values, count of "empty" values, min and max length, types of characters (letters, digits, whitespace, special characters), and then 10 or 20 sample values?  

The output would be best in a report format.  

I feel like I'm looking for a plugin or something but I'm not really sure.  

TIA!
0
ttist25
Asked:
ttist25
  • 2
  • 2
1 Solution
 
Barry CunneyCommented:
Hi
In SSIS there is a Data Profiler Task.
You can add this task to an SSIS worflow and execute it on a specified table.

It will produce an XML file which you can open with the Data Profile Viewer tool which is in the Integration Services folder in the SQL Server program group.

It can display column length, pattern, null ratio statistics and does so in a 'dashboard' type view

Please let me know if you would like more detail on this SSIS Data Profiler Task
0
 
ttist25Author Commented:
Hi Barry!

Thanks for the response.  Although I don't have time to figure out how to do it at the moment, it sounds like this is what I'm looking for.  I'm not sure when I'll have the time to look into workflows and how to add tasks but I didn't want to leave you hanging while I figured that out so, I awarded the points.  

If you have time and could do a quick list of steps that'd be great but definitely not necessary as it's outside the scope of the original question.  

THanks again!
0
 
Barry CunneyCommented:
Hi
Please see print screen attached which depicts and existing such task in SSIS.
I would advise maybe doing a few basic SSIS tutorials first if you are not familiar with SSIS.

You add the 'Data Profiling Task' from the toolbox to the main pane and add 2 connection managers, one for the DB/table that contains your data and the other for the profiling results XML file. You can then right-click on the  'Data Profiling Task' and choose Execute
SSIS-DataProfiler.png
0
 
ttist25Author Commented:
Thanks Barry!

Have a great weekend!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now