September 14th, 2011 
When maintaining a data warehouse, it is import understand the size and disk requirements of your tables over time. My current client has asked that I generate a report showing table disk usage over time, so that we can predict table growth. I was looking around and found the procedure sp_spaceused. This returns the row [...]
Posts Tagged ‘SQL 2008’
Table Size History
Posted in SQL 
Tags: Dimesion, Disk Usage, Fact, sp_spaceused, SQL, SQL 2008, SQLServer, SQLServerPedia Syndication, SSIS, SSRS, T-SQL, Table 
No Comments »Table Auditing with XML
When working with meta data tables it can be very useful to track when people make changes to the data. How do you track any change against any number of columns for any table? I have implemented a fairly simple solution that uses triggers and stores all changes as XML into one change log table. [...]
Presenting @ Atlanta Microsoft BI user group
I will be presenting at the next Atlanta Microsoft BI user group. Monday, July 25th, 2011. http://atlantabi.sqlpass.org/ Topic: SSIS Logging and Auditing Framework Level: Beginer / Intermediate Meeting Place Matrix Resources 115 Perimeter Center Place Suite 250 (South Terraces Building) Atlanta, GA 30346 Overview: Proper logging in your SSIS packages can help save hours in [...]
Avoiding dups when querying sysssislog
Last year I blogged about Avoiding dups in Parent Packages, when using custom logging. Todd McDermind has a good post on how to query the sysssislog table, if you are using the built in SQL SSIS logging. http://toddmcdermid.blogspot.com/2011/04/transforming-ssis-sysssislog-entries.html
Parse XML in SSIS
One of the built in data sources in in SSIS is an XML document. I started writing a package to pull data from XML stored in a SQL table. I noticed that there were several little gotchas that I had to work around to get the data the way I wanted. The XML file can [...]
Posted in SQLServerPedia Syndication, SSIS Tags: Microsoft, SQL, SQL 2008, SQLServerPedia Syndication, SSIS, XML, XML Source No Comments »Looping through files on an FTP server
I have always know that that SSIS has an FTP control Flow task, but I have never tried to use. Turn out that the task is fairly limited and can be a pain to work with. I am attempting to get a list of all files, in a folder, on an FTP server. The FTP [...]
Posted in SQLServerPedia Syndication, SSIS Tags: FTP, Microsoft, SQL, SQL 2008, SQLServerPedia Syndication, SSIS 1 Comment »Advance Search – SQL Server
As a BI Consultant, I am frequently going into new databases and writing queries against unfamiliar data sources. I have created a stored procedure that goes against the system views to help quickly find a key word in a Table, View, Columns or Stored Procedure. Once the procedure has been created you can execute it [...]
Posted in SQLServerPedia Syndication, T-SQL Tags: SQL, SQL 2008, SQLServerPedia Syndication, T-SQL No Comments »SQL Custom Keyboard Shortcuts
Keyboard shortcuts play an important role to increase the productivity, in any development environment . There are tons of shortcuts that are beneficial in SQL Server Management Studio. SSMS 2005/2008 also allow you to easily add your own keyboard shortcuts. The two shortcuts that I always create are the SELECT TOP 1000 * FROM and [...]
Posted in General, SQL, SQLServerPedia Syndication Tags: Management Studio, SQL 2008, SQLServer, SQLServerPedia Syndication 2 Comments »Customize SSIS Logging – Avoid dups in Parent Packages
I have been using a basic set of custom logging for my SSIS packages for a while now. I based my auditing framework on Project REAL and I have expanded it from there. I use event handlers for error, control flow tasks and variable value changes. One problem that I have come across is that [...]
Posted in SSIS Tags: logging, Microsoft, parent package, SourceParentGUID, SQL 2008, SSIS, variable Comments Off