Search results matching tag 'CodePlex'

Power Pivot SQL Agent Job Monitor

manowar — Mon, 07 Jan 2013 21:23:29 GMT

In the last days I needed a quick and efficient way to monitor the performance of a job and of each step over time. There’s a lot of interesting data in MSDB database and the various sysjob tables, but the stored data is not really easy to use outside the Management Studio. Date and Time are not stored as date/time data type, there is no easy way to correlate the performance of a step with the related job execution (for example is not that easy to return all the steps of a job executed, say, yesterday at 10:00).

So I decided to write some views to make the querying easier and then the next move was to create a Tabular Model with Power Pivot on Excel to make also analysis easier.

Since this is a somehow recurring task that I find myself doing during my job – not often, but when I have to do it now I have something much more powerful in my hands – I decided to put it also con CodePlex so that that little work can be shared with the community.

You can find the scripts with the objects (3 UDFs and 4 Views) that must be created inside MSDB and the Power Pivot Excel 2010 model here:

http://jobmonitor.codeplex.com/

Enjoy!

SSIS MSBuild task now included in MSBuild Extension Pack

jamiet — Tue, 14 Sep 2010 21:11:55 GMT

The information in this blog post may be of interest to anyone out there that is using Continuous Integration with msbuild in order to build/deploy a project that includes SSIS packages.

The MSBuild Extension Pack at http://msbuildextensionpack.codeplex.com/ now includes an MSBuild task that will build an SSIS project, the output from which is a .deploymentmanifest file that can be used in concert with the SSIS deployment utility.

Building a SSIS project using the SSIS MSBuild Task essentially has the same effect as right-clicking on a SSIS project and selecting Build with CreateDeploymentUtility=True (i.e. produces a .deploymentmanifest file):

The advantage of using the MSBuild task is of course that you can now do it in an MSBuild script, none of this manual mouse-clicking required thank you very much!

The SSIS MSBuild task was previously available in the SSIS community samples project at http://sqlsrvintegrationsrv.codeplex.com but having it as part of the MSBuild Extension Pack is definitely advantageous as this is a very widely used set of utilities. The source code has been available in the MSBuild Extension pack since changeset 54481 which was checked-in on 26th August 2010 and was included in the August 2010 Release.

Note that in order to deploy your packages after using the SSIS MSBuild task you will need to call the SSIS deployment utility and pass in the outputted .deploymentmanifest file. Information on doing that is decidedly thin on the ground so I’ll try and elucidate. The SSIS Deployment Utility is an executable that gets installed with SQL Server Integration Services workstation tools and is called dtsinstall.exe. You can call it on the command-line and pass in the path to a .deploymentmanifest file but as far as I can determine there is no way to do an unattended install (i.e. there is no way to tell dtsinstall.exe where to install the packages to, it simply opens up a GUI and prompts you for this information). This is a horribly antiquated method of deployment and I’m hunting around to see if there’s anything I’ve missed here – if you know any better please let me know.

Hope this helps!

@Jamiet

AutoAudit 1.10c

Paul Nielsen — Thu, 18 Mar 2010 03:16:00 GMT

AutoAudit is a free SQL Server (2005, 2008) Code-Gen utility that creates Audit Trail Triggers with:

· Created, Modified, and RowVersion (incrementing INT) columns to table

· Creates View to reconstruct deleted rows

· Creates UDF to reconstruct Row History

· Schema Audit Trigger to track schema changes

· Re-code-gens triggers when Alter Table changes the table

Version 1.10c Adds:

· Createdby and ModifiedBy columns. Pass the user to the column and AutoAudit records that username instead of the Suser_Sname value – useful for apps that manage their own users and connect using a common service account.

· Records user’s batch that caused the change event.

· Handles various data types better.

· Includes several small bug fixes.

CodePlex link: http://autoaudit.codeplex.com/

initial 2007 AutoAudit blog post:
http://sqlblog.com/blogs/paul_nielsen/archive/2007/01/15/codegen-to-create-fixed-audit-trail-triggers.aspx

Tweetpoll and RESTful Northwind go bye-bye

jamiet — Sat, 23 Jan 2010 10:25:23 GMT

On 31st January 2010 Windows Azure and SQL Azure will transition to becoming services that you have to pay for which means that my three small demos that are hosted up there are going to disappear hence I thought now would be a good time to review before they are digitally ground into dust.

Tweetpoll

Tweetpoll was a demo that I wrote back in April 2009 and is hosted on Windows Azure at http://tweetpoll.cloudapp.net/. Here’s what it does:

periodically polls Twitter’s public timeline and gets returned a list of the latest tweets
pushes each returned tweet onto a Windows Azure queue as a message
picks each message off the queue, measures the length of the tweet, and increments a counter (in Windows Azure table store) for each length
Displays the distribution of tweet lengths on a graph at http://tweetpoll.cloudapp.net/ (using some questionable HTML and Javascript :)
Counts the number of hits on http://tweetpoll.cloudapp.net/ and maintains that count in Windows Azure table storage too
Displays the current contents of the Windows Azure queue

Here’s a diagram that shows that a little better and demonstrates which parts use Azure web and worker roles. It was built at a time when one could only have one worker role per application therefore two threads are used to simulate the pushing onto and picking off the queue:

Its a very noddy application and the use of a queue is gratuitous but nevertheless its a useful demo of what can be achieved and enabled me to get some experience of building an app on Azure. The source code is available at http://tweetpoll.codeplex.com.

I wrote a blog entry back in May 2009 called Tweetpoll – My first Windows Azure application is live that introduced Tweetpoll. In that blog entry I provided a screenshot of the graph that gets displayed at http://tweetpoll.cloudapp.net and here it is again:

Here’s what it looked like in July 2009:

And finally today in January 2010:

I said at the time that it would “be interesting to see if the graph smoothes out over time” and sure enough that is what has happened. We still get a massive spike around 140 characters of course but its clear to see that the curve has smoothed out before that spike. I also find it interesting that there are more tweets in the 30-50 range than 90-120; something for academics to ponder over perhaps. Finally, if you are wondering why some tweets appear to be over 140 characters in length then take a read of my blog entry The Longest Tweet where I explain why that is.

Incidentally, by adding up the counter for each tweet length I can determine how many messages Tweetpoll has gotten off of the public timeline since April 2009 and I display that number at http://tweetpoll.cloudapp.net too:

Last but not least here’s how many hits the site has had:

Again, if you’re interested in looking under the bonnet of Tweetpoll then the source code is available at http://tweetpoll.codeplex.com.

RESTful Northwind

When SQL Azure entered public beta I wanted to build an app that would demonstrate its capabilities so I built http://northwindazure.cloudapp.net/Northwind.svc/. Its is, quite simply, a copy of the venerable Northwind database hosted on SQL Azure with a WCF Data Services (née Astoria) head on top of it which is hosted on Windows Azure. If you hit the WCF Data Services head then this is what you see (make sure you have feed reading turned on in your browser):

Some sample queries that you can issue:

Customer ALFKI
http://northwindazure.cloudapp.net/Northwind.svc/Customers('ALFKI')
Order items with a price > 250
http://northwindazure.cloudapp.net/Northwind.svc/Order_Details?$filter=UnitPrice gt 250
All orders by customer ANATR
http://northwindazure.cloudapp.net/Northwind.svc/Customers('ANATR')?$expand=Orders
All order details of all orders by customer ANATR
http://northwindazure.cloudapp.net/Northwind.svc/Customers('ANATR')?$expand=Orders/Order_Details
All order details for the first 3 customers
http://northwindazure.cloudapp.net/Northwind.svc/Customers?$top=3&$expand=Orders/Order_Details
All products in all orders for the first 3 customers
http://northwindazure.cloudapp.net/Northwind.svc/Customers?$top=3&$expand=Orders/Order_Details/Products

All the code is available on Codeplex at http://nwindazure.codeplex.com/.

If you want to read more about RESTful Northwind then go read my blog entry RESTful Northwind on SQL Azure where I also talk a little about how I attempted to use datadude (aka Visual Studio Team System for DB Pros aka whatever-its-called-today) against SQL Azure.

TwitterCache

This one is a lot simpler than Tweetpoll or RESTful Northwind, it is simply a SQL database hosted up on SQL Azure. In December 2009 Brent Ozar blogged about how he had been archiving tweets from various people that he follows into a SQL Server database and he made a backup of that database available for anyone that wanted to use it. I thought it would be cool to host that database up on SQL Azure both because it would demonstrate SQL Azure’s abilities and because it would save anyone else having to go through the rigmarole of restoring it themselves so I copied the schema up there and pumped the data in using SSIS.

Read more (including instructions on how to access it before it disappears) at TwitterCache now hosted on SQL Azure.

These apps/services will be disappearing in about one week from now so if you’re interested in using them go and do it now. Remember, the code for Tweetpoll and RESTful Northwind is up on Codeplex at:

Hope these have been useful to someone, they certainly have been for me!

@Jamiet

AutoAudit 1.09 posted to CodePlex

Paul Nielsen — Tue, 14 Oct 2008 00:05:00 GMT

AutoAudit is a code-gen utility that creates audit trail triggers, views to reconstruct deleted rows, and a table-valued UDF to reconstruct row history.

AutoAudiot 1.09 adds:
_RowHistory table-valued UDF
SchemaAudit triggers that keep the triggers in synch with ALTER TABLES, and audits database changes
compatability with SQL Server 2008 data types
several nit bug fixes

AutoAudit CodePlex site

ScreenCast demoing AutoAudit 1.09 (6:45)

please post issues and feature requests to CodePlex, Thanks, -Paul

AutoAudit on CodePlex - vote for your features

Paul Nielsen — Thu, 14 Aug 2008 23:43:00 GMT

In an effort to be more "open", I've moved AutoAudit (code-gen utility that creates audit tirggers) from my swebsite to CodePlex. Soemthing I like about CodePlex is the open submission and voting on issues and feature requests. I've moved every feature request from emails and this blog to CodePlex. I invite you to download, submit requests, and vote at: http://www.codeplex.com/AutoAudit/WorkItem/List.aspx