Category: SQL 2008

Bret Stateham is giving this talk.  He is a great presenter.  Very animated and speaks clearly.  He also explains complicated details very easily in a friendly manner.  If you have a chance to hear this guy speak definitely take advantage of that!

Boy I wish I had a recording of this.  I’d love my two DBA colleagues to see this.  All but two of our SQL servers are 2000, simply because that’s the latest available when the systems were installed.  It works great but you don’t have access to the great tools, such as SSIS, in 2005 or 2008.  I’ve pinged Lynn Langit and Daniel Egan about possibly being a resource for recording these sessions in the future.  You can read about the initial details in my blog post here.  <update>OK, here’s an update.  I’m 45 minutes into the session and Bret is hilarious!  He definitely makes this topic a lot of fun by his passion and animated style.  Lots o’ fun!</update>

This is really an intro to SSIS, which is exactly what I need.  His Data Flow Task talk is the next session. 

One data source we’re looking to need is an SFTP site.  Currently this isn’t in SSIS but we do have a vendor’s connector from n Software.  This will be fun to try out.  If for some reason it doesn’t work out Bret said we can create one through the Script Task or even a formal task developed in C#.  I may end up doing something like this to hit our GIS server using REST.  I did see a Web Task and it’s quite possible that it can consume REST already.  This means that we’d have to consume XML and possibly convert this to some other usable format if necessary.

Bret just gave a great tip that may have frustrated me for a while.  The variables you create a scoped.  If you have a task selected and you create a variable, that variable will be scoped to that task, meaning it will only be available to that task and will disappear after that task is finished.  Chances are you want your variable to be accessible by other tasks in your package.  In this case make sure you click an empty area of your design surface prior to creating the variable.  That will scope it to the package itself.

In SSIS 2005 you could only write Scripts in VB .Net, however, in 2008 you have the ability to use C#.

Man!  Debugging in SSIS is awesome!  I can’t way to dive into this stuff at work.

Loops, Sequence Containers and Groups:

Loops are exactly as they sound.  The allow you to take a set of tasks and loop through them, much like a subroutine.

Sequence Containers do not loop.  They do, however, offer scope and environment properties.  You can define variables that are only available the that sequence.  You can also set transaction properties on that individual container.  Maybe you have several different containers each with their own transaction level.  Pretty neat.

Groups are simply a design feature.  They are simply there for organizing the items on your design surface but have no properties and no impact on the package at all.

The For Each Loop allows you to specify the collection your picking items from.  The possibilities include XML objects (for each node) and even SMO (SQL Management Objects) for things like “for each server do this”.  That’s pretty cool.

Bret showed a great example where he needs to process files every day that may not have the same filename.  For instance this may be an AS400 dumping csv files that were time stamped.  He generates what the filename should be in a Script Task, saves that into a variable, and then his File Task uses that variable as it’s connection string.  Sweet.  We need to do this as well and this would really help.

This was a great talk.  SSIS is definitely something I’ve been looking to get into ever since SQL 2005.  It looks like this is something I can dive into right away.  For the more complex things Bret recommended the book Professional SQL Server 2005 Integration Services from Wrox.

Unfortunately I can’t attend Bret’s next session since I’m attending Mike Roth’s Asterisk session.  Mike and I are working on a side project together using Asterisk, so I should give audience support and also learn what the heck we’ll be using! 🙂

 

This is given by Ben Aminnia. 

He’s really putting an emphasis on planning, which is great.  Lynn Langit said her new book has the first 5 chapters devoted to what needs to be done before you even open BIDS.

Ben has an interesting presentation style, at least for this talk.  He’s giving it like his company has an existing Analysis Services project that we are interviewing to possibly take over.  I’m not quite sure yet we are on the driving end (we are really interviewing Ben to see if we want to take the job) or on the other end (he is interviewing us to see if we’re qualified to take the job).  I hope more of the former since I’m at the talk because I don’t know anything about SSAS.

While he is doing and interview style here at Code Camp he is assuming we don’t know about SSAS so that’s a good strategy to take.  He just gave a really good 4 minute background on Analysis in general and what a Cube is (since I raised my hand because I didn’t know! :))

During this talk Ben is using the example of a system that monitors disk space availability (among other data).  He actually has a great real world system for doing just this. It is based on the cover story of the February 2008 issue of SQL Server Magazine. You can find the online version here.  It’s a very complex SSIS package but it allowed Ben to do this without having to spend months developing it.  For him it works great and is easy to maintain. Ben has even made his own additions and discussed these changes with the original author. If you contact Ben directly (ben at sql.la) he can give you his additions.

Great talk!

Lynn Langit is presenting this one.  Her main career focus is Business Intelligence and has a book coming out.  I’ll have to pick this up.

This is an area that has always been of fascination to me yet I don’t know anything about it other than simple data reporting.  Working at the Val Verde Unified School District we have vast amounts of data and could really take advantage of BI.  Hopefully I’ll be able to use some of this with our new SQL 2008 install and start introducing it to various departments.  Maybe we could even use it in IT to analyze our support trends.  That would be sweet!

Excel has a great add-in to act as a client to BI data.  Excel is one of the primary tools used at our organization, especially by our business office, so this would be a great sell.

Wow!  Lynn just showed how she took her Excel spreadsheet of local data and analyzed it using Microsoft’s own services.  She asked Excel to analyze it, it reported she wasn’t connected to SSAS, she said she wanted to use Microsoft’s data services in the Cloud, and then it sent the results back.  This took only a few seconds.  Yes you are sending your data to Microsoft and yes this service is only free during beta testing, but this is amazing.  Could really open doors for cheap introduction to SSAS and BI in general.

Check out SQL Server Data Mining in the Cloud for details on how to do this.

At VVUSD we have the mentality that we’d rather pay up front had host our own services, but using this might be a great way to sell research in this area in the beginning.

Lynn gave a great explanation of SQL Data Services.  It is SQL Server that has been optimized for performance and high-availability.  Thus many features have been removed and interaction has been restricted.  There are schemaless containers via Authorities, Containers and Entities (think property bags).  Also, the only query language currently is LINQ.  This all may change but that’s the current state.

Wow!  Once you have a validated and fairly good model Lynn showed how this can be used real-time.  You can hit your model real-time, such as from a form your sales guy is using, and get instant prediction about your current state.  For instance, if you’re selling bikes, and in the first 3 questions your sales guy finds that this potential customer is in the bottom 10% of likelihood to buy a bike, they can thank the customer and hang up right there.  Instantly, in real time, they have found out the likely results of their work.  We could use this in our school and, based on grades, get instant feedback as to how successful a student might be.  Education organizations spend millions in this area so I think our work would be cut out for us! :)  In the IT dept, for example, we could possibly instantly predict the support costs we’ll incur from a department (or specific user) and hardware (laptops, phones, etc) based off of previous support calls.  So if a user who travels a lot and is particularly rough with their equipment asks for a new laptop we may find that it’s more cost effective to buy a better warranty or a tougher laptop (or deny the request all together).

This is definitely a large area (full careers in themselves) but something that would definitely be worth spending some time on if possible. 

OK, I’m definitely going to have to pick up a book.  This is all so new to me that most of this is over my head, but the demos are absolutely amazing.