?
Solved

Microsoft sql server "frequency report"

Posted on 2014-07-22
4
Medium Priority
?
412 Views
Last Modified: 2016-02-13
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
Comment
Question by:ttist25
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 2000 total points
ID: 40213970
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
 
LVL 1

Author Closing Comment

by:ttist25
ID: 40215248
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
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 40219805
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
 
LVL 1

Author Comment

by:ttist25
ID: 40220438
Thanks Barry!

Have a great weekend!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question