Solved

Microsoft sql server "frequency report"

Posted on 2014-07-22
4
404 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 500 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

691 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