SQLServerPedia

In SQL Server Integration Services (SSIS), pretty much every task or transformation lets you set “expressions” up. Expressions are basically ways to set property values programmatically.

Here are two scenarios where you might use expressions (there are 100’s of uses, these are just two that are kind of related).

1. Multiple Database Select – You have multiple databases – same schema, let’s say you have 300 installs of a 3rd party product and they all need their own database. I know it might sound impossible, but trust me, it can happen. Now, you want to run the same query over all databases, and pull data from a table, and dump into a data warehouse, for example. You could write 300 queries, and keep adding/removing based on the databases, you could create some elaborate dynamic SQL proc using loops, or you might have some other way, or, you could use SSIS Expressions.

   Now, how would you go about doing this? It is pretty easy actually. First step, you need to set up a loop in SSIS. You would want to grab a recordset of database names using an Execute SQL Task, or however you’d like, and store in an object variable. Then you can loop through that list. Your only difference in your query would be database name, so what you would do is have a variable for your SELECT statement. Name it whatever, but what you want to do is click on the variable, the properties of it. You will see Expression. Open the expression box and then set it to something like this

   ”SELECT Col1,Col2,Col3 FROM “ +  @[User:CurrentDatabaseName] + ".dbo.MyTable"

   

   @[User:CurrentDatabaseName] is another variable to store the databasename that you would grab as you loop through your list of databasenames.

   Finally, in your dataflow, OLE DB source, you can change the Data Access Mode to “SQL Command From Variable”, and then it will let you choose your variable. As your for loop loops through your database names, and updates your SELECT variable, you can then select data from each database as you loop through them.

     

2. Non-Standard Database Select – Not sure how to label this one, but here is what I am talking about. I like to make all my queries as stored procedures in SSIS, at least as much as possible. This works great when you are doing SQL Server to SQL Server, but what happens if its Oracle to SQL Server, DB2 to SQL Server, etc? Yes I know you can create stored procs on those systems, but you might be in a place or position where you just can’t or don’t want to. In that case you would want to use just standard T-SQL select statements to get data. You can easily put in params if the source is an OLE DB source, but what if it is an ODBC Source? You have to use the DataReader source, and you can’t easily set params – like a WHERE statement. You HAVE to use Expressions in order to have a query with a dynamic WHERE statement or passing in a variable as WHERE statement filter.

   So, throw a DataFlow on your package, and inside that, throw a DataReader source, and then set the connection to your ODBC Connection (ADO.NET Connection) and set the command text. Good to go. But where to set the connection? Not very intuitive. Go back to your DataFlow and look at the expressions for it. You will see one for DataReaderSource.CommandText (where DataReaderSource is the name of your DataReaderSource). You can set the expression up there. Now you can change an Oracle SQL Statement or DB2 or whatever to something that takes params without the need for a stored proc on that other database server.

So, while there are hundreds if not thousands of uses for expressions in SSIS, these are just a couple of uses that can make your life easier when trying to do more dynamic type queries in your DataFlow. Happy ETL’ing!

Last week I started riffing a bit after viewing a Penny Arcade strip that fired a warning shot over the bow of Courtney Love.  That strip led to three memes on Twitter and beyond: #sqlservereditions and the blog post by Brent Ozar in regards to funny little asides on Fake SQL Server Editions/Versions, and the Rockband version of our old favorite Dead Rockstars game here on my site. 

In trying to keep up with a post-a-day mandate I’ve set for myself this week I thought I’d take a few minutes to perhaps meld the two together.

SQL Server Geddy Lee Edition - It’s not pretty, but it’s got the chops

SQL Server Lemmy Edition - Holy Shit!  What is that thing growing out of the side of the cluster?!?

SQL Server Def Leppard Edition - Only compatible with RAID 0 +1, spell check is disabled

SQL Server Ozzy Edition - The underlying code structure of Oz/SQL makes no sense whatsoever, yet somehow it still runs.

SQL Server Amy Winehouse - Runs on Jet. (Yes, I have no clue how it would do that either.)

SQL Server Led Zeppelin Edition - Hobts everywhere you look.

SQL Server Whitesnake Edition - Like SQL Server Led Zeppelin, but less poweful, more cheesy, and no Hobts.  Comes with a real nice, but dated hood ornament.

SQL Server Tempations Edition - so highly redundant that it still operates even after all the original components have been replaced

SQL Server Blues Traveler Edition - shrinks and autogrows are handled seamlessly.

SQL Server Elvis Edition - does not scale out well.  Dumps are not graceful.

SQL Server Luther Vandross Edition - Like the Blues Traveler Edition, but you are only permitted so many log autogrow cycles before the instance dies.

SQL Server Ted Nugent Edition - Kills spids dead.  Then guts and eats them.

SQL Server Brian Wilson Edition - not mobile

SQL Server Spinal Tap Edition - gets lost in it’s own data center. Scales to 11.

SQL Server ABBA Edition - You make jokes about those using it, but secretly you’ve done some development work on it when no one is looking.

SQL Server Bob Marley Edition - The only “Green” SQL Edition released to date.

SQL Server Mariah Carey Edition -  Augmented and screechy.  What is it doing to the make the drives squeel like that?

SQL Server Milli Vanilli Edition - Is actually Microsoft Access

SQL Server Keith Richards Edition - So powerful it can be loaded on a server, dropped from a tree and still run.

SQL Server Sheryl Crow Edition - Can only be managed by DBAs with 1 testicle.

SQL Server Axl Rose Edition - Not compatible with any O/S.

SQL Server David Hasselhoff Edition - Is big in Germany

SQL Server Justin Timberlake Edition - You won’t believe what else is included in its box.

 

You may now return to the serious things in your life; and yes, those are 3 minutes you’ll never get back.

My editor at O’Reilly & Associates sent me an email the other day.  It read “I’m pleased to tell you that SQL in a Nutshell will be one of 19 titles submitted to Apple by early next week. It may take up to 3 weeks after that for them to appear.”  From what I understand, it might cost anywhere from a few cents to $4.99.  That’s an awesome price, considering the print edition is within spittin’ distance of $50.00.  And, in my humble opinion, it’s a price that very nearly constitutes highway robbery when you consider the amount of toil that I and my co-authors put into the book, now in its third edition.  On the other hand, any publicity is good publicity.  So I’m excited to see what happens next.

In case you hadn’t heard this from other authors, let me be the first to tell you.  Remember that old song Video Killed the Radio Star?  Well, we’ve got the same crime with slightly different actors.  (No, it’s not Ms Scarlet in the Library with the Lead Pipe!)  Simply put, the internet is killing print.  It’s a fact.  Newspapers are losing subscribers in droves (averaging about 6% – 7% last quarter alone).  Magazines are withering away.  While my favorite news magazine The Economist (an English publication with a conservative bent) is still as fat as an old Sears & Roebuck catalog (also killed by the internet) with lots of words in tiny print, have you bothered to pick up a copy of Time or Newsweek lately?  They’re so thin that they’re almost transparent, which can also be said of royalty checks for database authors too. 

My books have been available via the website Safari for quite a while.  Which, for some reason, seems to be as effective a marketing statement as “I’m kind’a a big in Europe. Really.  I am.”  And while I know there’s a line for it on my royalty statements, it has never made a measurable difference to my bottom line as an author.  Will the Internet ever make a dollar for any of us content creators?  I fear not.  After all, smarter and richer people have tried to make a buck tackling this same question. (Ok – now that I think of it, those people are probably not smarter.  But for a while longer, at least, they’re a whole lot richer.)  Ever taken a look at all those news stories about kids getting sued for downloading Metallica and Colbie Calet without paying?  Well, the downloaders are winning.

As I thought about the opportunity, I happened upon this great article that helped me see why having my book as an iPhone app was a good thing:  http://bits.blogs.nytimes.com/2009/02/26/why-are-iphone-users-willing-to-pay-for-content/.  Maybe there’s salvation for content creators in the delivery channel?  Maybe iPhone users will consider paying a tiny fee for great content partly because they simply enjoy using their cool gadget/PDA/statement of personal style with excellent interfaces and eminently readable print, bookmarking, and other fancy features?  The jury is still out.  Yes – reading  SQL in a Nutshell on your iPhone or iPod will make you cooler.  (That’s my story – and I’m sticking to it.)  And if too few people agree with that statement, I, and many other content creators like me might not come around for the next edition…

Cheers,

-Kevin

Yo…check it. I’m sittin’ here chillin with my Tweetdeck rollin’
When I get word of a SQL Rap contest, the illest rhymes we be extollin’
What’s up for grabs is an iPod Touch
Whatcha gotta do? Well, it ain’t much
Cook up the illest rhymes and sell em like a 3rd party tool
And this first rhyme I’m spittin is aimed at SQLFool

Go-daddy, Go-momma don’t matter the sex
This contest brings the baddest DBA’s like an ill-formed Index
What you and Ward started, like Ma$e can’t be stopped
These other sucka emcees are just another table my T-SQL’s gonna drop

Now Ward, aka the SQLTwit
Ready yourself for these rhymes I’m about to spit

You live in the woods, probably pickin berries
Since you’re backwoods I don’t wanna make this too complex like queries
Your lyrical rap styles are tuned like fine code
I heard you can ask for the BrentO discount when purchasing Quest Toad

Speaking of Brent O, his raps are well taken
Damn rapping about him now makes me crave #bacon
He looks like the SQL world’s Tina Fey
Next time you drop by Twitter, drop him a line and say ‘HEYYYY’

My VM snapshots are wrapping up from their orphaned state madness
Sorry to leave you guys with much lyrical sadness
These rhymes are whack but my skills are finger lickin’
So peace to your mothers, a love note from the SQL Chicken!

Configuration Management Databases are an enterprise-quality way of gathering a lot of IT and business information in one place – but how much do DBAs really need?

For more about building a CMDB, check out the CMDB tutorial wiki article. We’ve got sample scripts you can use, and instructions on how to get the result data into a database for easier reporting.

If you have an MP3 player or a portable video player and you want to download our videos automatically, you can subscribe to our podcast feeds here:

• MP4 (Apple) Video Feed
• WMV (Microsoft) Video Feed
• MP3 Audio-Only Feed
• Zune One-Click Subscribe for Video
• Zune One-Click Subscribe for MP3

You can also download this video to watch it later:

• MP4 (Apple) Video Download
• WMV (Microsoft) Video Download
• MP3 Audio-Only Download

Disclaimer: I mean what I am going to post. This is not a Microsoft fan boy post. That would include a conversation about my Zune, the Xbox 360 I don't have or something... I think this is a great resource for a Microsoft shop.

A TAM?

Technical Account Manager. When your company pays for a premier support contract with Microsoft you get assigned a TAM. They help liaison between you and the support arm at Microsoft. When a case is open for too long, an on the ball TAM will call you, call the support group and get things moving. If you want to ask a question without incurring hours, they can sometimes help you out. Most TAMs will come out to your site at least monthly and just check in with all of the folks who have support accounts. They want to make sure you are happy and getting what you need.

How do I get one?

Well you have to have a Premier Support contract. I don't know the details on pricing or how it works but if you are an enterprise that wants to have that peace of mind that you have the hours available, have the escalation assistance available, etc. It makes sense in my mind.

So what, I don't need a TAM.

Well right, it's not all about your TAM. It's the services offered by Premier Support Services through your TAM and the support umbrella. Some of the benefits from being a PSS customer and having your very own TAM:

• A recent training course on SQL Server 2005 Performance Tuning and Optimization. This was taught by a Premier Field Engineer (They will rapidly respond to a critical situation at your site that can't be handled over the phone. They work within premier support, not sure how they are dispatched to non-premier customers. Like anything I imagine if you set it all up front, it is more timely and less expensive) named George. Knowledgeable guy, great material. There are countless classes like this for all products. You don't "pay" for the course but you have proactive support hours deducted from your account. If you do it right and spend more time in proactive mode, your reactive calls are hopefully lower and you end up doling out less hours anyway so it all works out.
• Alerts (Upcoming security patches, serious issues, information of interest to my role - SQL -, upcoming free webinar reminders, upcoming local free or PSS-hour deducting courses) - I probably get at least one e-mail a week from my TAM and that is consistent with other gigs that included premier support.
•  An Extra Set of Hands - Your TAM sees your cases on their web interface, checks in to be sure you are getting proper support and helps escalate as needed.
• Access to web interface/advanced hotfixes - When you have premier you can log calls through a web interface. You can also search this interface for special knowledge base  articles and hotfixes available only to Premier customers which may assist you.
• Someone to vent to - As in most of life, things don't always happen in an ideal fashion. Without Premier you can still get to the chain of command. With a TAM you have someone readily available to whine to and you get instant help in making it right.
• A monthly visit - It has been my experience at a couple of places that our TAM came up and visited monthly. Asked how things were, chatted about upcoming events, asked how they could better help us, suggest proactive services and even head out to lunch on occasion.

Proactive Services

One of my pet peeves is always being stuck in reactive mode. Sometimes you need to stop and ask yourself where to begin. Sometimes you may need some help. Through Premier Support you can have a SQL Server Health Check or a SQL Risk Assessment Program check (SQLRAP.. Not Michelle Ufford's kind of SQL Rap - Ward Pond's kind of SQLRAP)

Through these programs, a Premier Support engineer with expertise in the product being checked will come onsite, work with you and analyze your environment with a goal of increasing your proactive abilities. They don't come to ding you, audit you or belittle you. They come with great tools, analyze your instances and help document a plan to get where you need to go. They also give you an excuse to be proactive. You have to schedule the days to work with them and you need to sort of just let things pile up and focus on proactive mode. This is an invaluable tool for you to use. In fact I feel the same way about these checks as I feel about finding your instances with the Microsoft Assessment and Planning tool.

You can learn more about Premier Support Services at this link.

OK here we go… In May the Edmonton Chapter of PASS is going to have it’s 1st meeting. Details below:

Date:  May 28th 2009
Time: 5:00 pm - 7:00 pm
Location: Stanley A. Milner library
Map: 7 Sir Winston Churchill Square
Meeting Room: 6th Floor - Room 7
Speaker: Scott Gillespie
Topic: To Be Determined (will be included in the newsletter coming out TOMORROW!!)

Agenda:
5:00 pm - Pizza and Socializing
5:30 pm - Sponsor Presentation
5:45 pm - Feature Presentation
6:45 pm - Wrap Up and Draws

Presenter Information: Scott Gillespie is one of the Principals in Winding River Solutions Inc. which was established in late 2001. Winding River is a Microsoft Gold Certified Partner whose consultants have extensive experience in the development of distributed applications and are highly skilled in infrastructure architecture and the deployment of Microsoft technologies. Winding River is also a Microsoft Gold Certified Partner for Learning Solutions and it is these same consultants who bring to the classroom a valuable combination of proven training abilities and real field experience. Scott is an experienced systems consultant, developer and trainer specializing in Windows Server, SQL Server database, Exchange Server, SharePoint Portal Server and Exchange collaboration applications. Scott has been involved in full enterprise deployment projects that use a wide range of Microsoft solutions.

If you haven’t signed up already at EDMPASS.com please do so now to receive meeting notifications, news, and updates from EDMPASS.

Hope to see you there.

Enjoy!!

       

Two weeks from today, Brent Ozar will be speaking at CBusPASS. This time around Brent will be presenting on Using Cloud-Based BI to Interpret Perfmon & Profiler Results. LiveMeeting will be available. There won’t be a dial-in number, though. Why? Because the sound will travel through Brent’s microphone, into a series of tubes, and then it will fly out of your speakers at a very high velocity. That’s right, we have integrated LiveMeeting sound! When the time comes you just need to join the LiveMeeting and you should be good to go.

The meeting will be held, as per the usual, at Battelle For Kids, 1160 Dublin Road Suite 500, Columbus, Ohio 43215.

Note: Battelle For Kids has moved to a new suite in the same building.

Abstract

After learning how to use Perfmon and Profiler to gather performance statistics about your SQL Server, it still takes a lot of time to interpret those results and figure out what’s going on. Microsoft’s SQL Server Data Mining team has built a free cloud-based data mining tool for Excel that can help slice and dice mountains of data and help you make sense of it all.

Even if you’re not ready for BI in the cloud, you can use this same type of tool in combination with a local SQL Server Analysis Services instance. Wait! Don’t freak out - it’s much easier than you think, and you never have to leave the comforting environment of Excel. Even if this doesn’t sound like fun to you, you might want to learn about it because mid-level managers in your company might want to use this technique to analyze sales or customer data.

Attendees will learn how to install & configure data mining in Excel, how to analyze Perfmon data to break the server’s load into categories, and how to use BI to write a performance report about your SQL Server.

Live Meeting

May 14th, 2009 @ 6:30 PM EST.

Live Meeting Login

Audio Information

Computer Audio
To use computer audio, you need speakers and microphone, or a headset.

First Time Users:
To save time before the meeting, check your system to make sure it is ready to use Microsoft Office Live Meeting.

Troubleshooting
Unable to join the meeting? Follow these steps:

1. Copy this address and paste it into your web browser:
   https://www.livemeeting.com/cc/usergroups/join
2. Copy and paste the required information:
   Meeting ID: NM86HK
   Entry Code: 5\-b6|jkH
   Location: https://www.livemeeting.com/cc/usergroups