SSIS Tips & Tricks 12/28/2010 12 Noon Eastern Time (GMT-5) Josef Richberg Learn some of the quirks and capabilities of SSIS. Learn about the pitfall of “parallel path choicing”. Use the Script Component as a data source (to solve complex sourcing issues) and as a data destination (to get around the identity column conundrum). See how to use Script Tasks to build directory scanners for efficient file import techniques. We’ll also go over some the tunable properties of the Data Flow object. Josef Richberg Josef is a DBA for HarperCollins Publishers working with SQL Server and SSIS. He has over 17 years’ experience designing, building, and tuning SQL Server. Josef was the recipient of the ’2009 Exceptional DBA’ award and actively blogs at http://josef-richberg.squarespace.com. How do I view the presentation? Attendee URL: Live Meeting link
December, 2010:
Quick Blog: What’s Missing From SQLPS
Last week Norm Kelm ( blog | twitter ) asked me on twitter ‘What cmdlets/features are missing from SQLPS.exe? Get-Snapin, etc. Where’s a doc that explains?’ To get the cmdlet half of this answer is somewhat easy in PowerShell using Compare-Object but it made me realize this is one of the many PowerShell tips that I have failed to blog about yet! I plan to fix that next year with a deluge of blog posts but why wait until next year right? To get a very fast answer to this answer I opened up PowerShell.exe because while I normally demo in PowerShell_ISE.exe, I don’t use a profile in PowerShell.exe (more on that in a later post). I ran this command to get the count of just the cmdlets. (get-command -CommandType Cmdlet).count Then I fired up SQLPS.exe (typically found in C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn) and ran the same command (after switching out of SQL Server and over to the C drive; no idea why that was necessary). The only problem here is that it’s not really 236 – 130 = 106 because SQLPS.exe adds 5 cmdlets that I made sure to exclude (along with everything else I normally use) from PowerShell.exe. To get a more accurate list of the cmdlets and which one has what I decided to write a script that everyone con run on their own machine. One of the many cool features of PowerShell is that you call call it’s executable and pass it a -command or even a –file. I went ahead and threw together this simple command below so that you can see the differences yourself. Note: I assume you are running Windows 7 which comes with PowerShell 2.0 $SQLPS=SQLPS.exe -command “CD C:\; get-command -CommandType Cmdlet | select CommandType, Name; exit”; $PSTwo=powershell.exe -command “get-command -CommandType Cmdlet | select CommandType, Name; exit”; Compare-Object -ReferenceObject $SQLPS -DifferenceObject $PSTwo| Export-CSV C:\temp\SQLPSCommandDifferences.csv -NoTypeInformation Now all that you have to do is open up that csv file that was just created (C:\temp\SQLPSCommandDifferences.csv) and have a look. Note: I could have just displayed the info inside of my PowerShell window by leaving off the last line and the pipe but then I would have needed to do something to format the output so I went with the CSV file instead. For more information on this topic be sure to check out Chad Miller’s post on it.
PASS Summit 2010 Feedback Results Are In
I got my PASS Summit 2010 Feedback earlier this week and am blown away with the results! Actually, I’m humbled, very humbled. Before I bore you with the scores or anything: today is my brother Jason’s Birthday ( blog | twitter | flickr ) so I thought I’d share with you some pictures he took while he was out there with us at the PASS Summit. This one is taken from the Columbia Tower (the REALLY tall black building, well I guess it’s not actually that tall, I mean it’s not as tall as Bank of America Plaza in Atlanta but anyways…). The Columbia tower is a much better deal than the Space Needle, it only costs $5 to go up and it’s MUCH taller. Onto the boring part: I put a lot of work into that session and even on the day of the event I was still second-guessing what scripts to put in and which to leave-out. Thankfully fellow [PowerShell] speaker Trevor Barkhouse ( blog | twitter ) sat through my entire presentation that morning and helped me carefully tweak the sequence of scripts. I had the 4th most well attended session of the 186 sessions at the PASS Summit on the official scoring list I was sent! My scores were nothing short of amazing considering I had never spoken on a stage this large before. Even if you leave the whole first-time-at-Summit-speaker thing out I still scored just above the midpoint for all speakers at the Summit. Not half bad for a n00b <—Literally! I am not a professional speaker. In fact I have been speaking for less than a year still. I had a time budget in preparation and I had to make trade offs. I decided to focus vastly more time on demos and script flow than slides or things like inserting jokes in the session. I’ll definitely work to improve the things that people comment on and I’ll share those in a later post (short on time today). The Scores: How would you rate the… Score amount of time allocated to cover the topic/session? 4.17 Speaker’s presentation skills? 4.33 quality of the presentation materials? 4.41 usefulness of the information presented in your day-to-day environment? 4.46 accuracy of the session title, description and experience level to the actual session? 4.5 Speaker’s knowledge of the subject? 4.62 Time allotted: Based on the comments from people who came up and talked to me afterwards at the Summit, they would have loved for me to have kept going for another 20 minutes. I don’t think Adam Machanic ( blog | twitter ) would have appreciated that much but I sure had the material for it! Presentation skills:I will definitely work on upping my presentation skills but this is the toughest competition I’ve ever been up against so not half bad! Quality of the materials: This may have been lowered because of the many A/V related comments I received. Or maybe refining my presentation skills will help this too. Usefulness in your day-to-day environment: is the entire reason I put the presentation together in the first place. My goal for this particular score is not a number but a note from the organizers mentioning the shocking number of people who scratched out the choices and wrote in a number above the range. I will not let people down the next time Accuracy of title: I have some ideas on how to tweak the abstract a little but I’m happy with this score. Speaker’s knowledge: I’m happy with this score. I’m sure Adam (who was speaking right after me) got nothing but 5s on this score. Considering the company, my score was stellar. I will not spend any time thinking about let alone trying to improve this score. I’ll post the comments I received in a separate post. Gotta run. Oh btw… that table up there. Generated if off of an Excel spreadsheet using a single line of PowerShell code! (I’ll blog that soon, promise.)
PowerShell Script to Download SQL MCM Videos
Microsoft has made changes to the Microsoft Certified Master program for SQL Server to make it much more accessible to everyone. In short if you have the required credentials (MCITP: DBA 2008, DBD 2008), the required skills, and $2,500; you too can become an MCM for SQL Server. Microsoft has partnered with SQLskills to produce 40 hours of introductory MCM training material videos which they have made freely available to everyone. If you’d like more training than just the videos SQLskills has some classes that you can attend. If you’re like me you’ve either already downloaded the videos or you’re not even reading this right now because you clicked the link and just started downloading the videos. Well if you don’t want to spend the rest of your day downloading all of those videos by hand I’ve got a little PowerShell script I think you’ll enjoy. $wc = new-object net.webclient [regex]$regex =”(?<url>http://download.microsoft.com/download/./././[0-f]{8}-[0-f]{4}-[0-f]{4}-[0-f]{4}-[0-f]{12}/(?<file>[^>]*?wmv))” [xml]$xml = $wc.DownloadString(“http://www.microsoft.com/feeds/TechNet/en-us/How-to-videos/SQL_Server_2008_Microsoft_Certified_Master_(MCM)_Readiness_Videos.xml”) $xml.rss.channel.item | foreach { if ($wc.DownloadString($_.link) -match $regex) { $url = $matches.url $file = “$home\Videos\$($matches.file)” if (Test-Path $file) {Write-Host “$file is already there mate”} else { Write-Host “Downloading $file” $wc.DownloadFile($url,$file) } } } This script will check the RSS feed, connect to all of the links in it, find the first .wmv link that it comes across, then copy that file to your videos directory in your documents folder IF it’s not already there. If you have to stop this script that’s fine, it will figure out which videos have already been downloaded and skip them*. If you want to try downloading the .MP4 files instead, just swap out .wmv for .mp4 and you should be all set. A warning about that though; they seem to be a little more than twice the size of the .wmv files *This script is written for Windows 7 and maybe for Windows Vista. You’ll have to edit it yourself for Windows XP or just bug Nic Cain to post one **IMPORTANT: I think I have fixed the HTML rendering issues for the code block but you may need to download the script here. PowerShell First Timers! First off, welcome to the best addiction that you will ever have! Since so many people are checking out this post and firing up PowerShell for the first time, I’ve gathered together some useful links. Here’s one from the ScriptingGuys themselves: How Do I Install PowerShell on Windows 7 and Other Questions I have a series of posts you may want to look at to help you get up & running as well as a video I did a while back for MSDN’s geekSpeak: PowerShell Week Post 0 (5/17) PowerShell Week Post 1 (5/18) PowerShell Week Post 2 (5/20) PowerShell Week Post 3 (5/20) I’ll blog more later this week about how I put together the script but I wanted to get it into people’s hands ASAP. Special Thanks go out to Jeremiah Peschka ( blog | twitter ) for not laughing too hard at my first ever attempt at a RegEx as well as Nicolas Cain ( blog | twitter ) & Chad Miller (Blog|Twitter) for giving me pointers on how to use my new RegEx hammer inside of PowerShell.
PowerShell Grab Bag Code
Thanks to everyone who took the time to check out my PowerShell Grab Bag session today for the PowerShell Virtual Chapter of PASS. Here’s a zip of all the code that I used and the two links I promised to include. Today’s Code Quest’s Free PowerShell Commands for Active Directory Shay Levy’s ( blog | twitter ) WinRar blog post Happy Scripting!
Presenting for the PowerShell Virtual Chapter of PASS
Sorry for the late notice folks but I will be Presenting for the PowerShell Virtual Chapter of PASS today at Noon Eastern. Today’s session is just going to be a bunch of cool things that I’ve been working on lately but haven’t gotten a chance to present or blog about yet. I’m still working on one last script but I’ll post the code before the session if I can. Hope to see you there! PowerShell Grab Bag Wednesday December 15, 2010, 12 Noon Eastern Time (GMT -5) Speaker: Me Live Meeting Information Join the meeting In this short grab bag session we’ll go over a bunch of new scripts that I’ve built over the last few weeks. Things like: checking free space on mount points, checking SQL agent for failed jobs, polling instances to make sure they’re available, checking the status of log shipping across multiple targets, emailing result sets and scheduling all these with SQL Agent. Checking remote registries (including why on earth you would ever want to do that?!). Zipping files, even better, we’ll go over how to not zip files that have already been zipped. The final grab from this session: for all the Summit attendees out there, I’ll share with you my script to download all the session videos from the 2010 Summit
Early December 2010 Free Training From PASS VCs
Understanding Join Operators How to Use Dynamic Management Views to Monitor and Diagnose Performance Issues With High Volume OLTP Workloads Using OLAP to Optimize and Maintain Predictive Analytics Models Understanding Join Operators December 7, 2010 12 PM noon Eastern Time (GMT -5) Ami Levin SQL Server implements three different physical operators to perform joins. In this session, we will examine how each of these operators works, including its advantages and challenges. Using real life examples, we will better understand the logic behind the optimizer’s decisions on which operator to use for various joins. Finally, we will learn how to avoid some common join related pitfalls and how to get better performance from our queries. Ami Levin Ami is a Microsoft SQL Server MVP, with over 20 years of experience in the IT industry. For the past 12 years, he has been consulting, teaching and speaking on SQL Server worldwide. He manages the Israeli SQL Server user group, leads the local support forum, and is a regular speaker at Microsoft conferences. Ami is the CTO and co-founder of DBSophic, a company that develops workload tuning solutions for SQL Server applications. How do I attend? Live Meeting link Dr. DMV: How to Use Dynamic Management Views to Monitor and Diagnose Performance Issues With High Volume OLTP Workloads December 8, 2010 12 PM noon Mountain Time (GMT -7) Glenn Berry SQL Server 2005 introduced Dynamic Management Views (DMVs) that allow you to see exactly what is happening inside your SQL Server instances and databases with much more detail than ever before. SQL Server 2008 R2 adds even more capability in this area. You can discover your top wait types, most CPU intensive stored procedures, find missing indexes, and identify unused indexes, to name just a few examples. This session (which is applicable to both 2005, 2008 and 2008 R2), presents and explains over thirty DMV queries that you can quickly and easily use to detect and diagnose performance issues in your environment. Glenn Berry Glenn works as a Database Architect at NewsGator Technologies in Denver, CO. He is a SQL Server MVP, and he has a whole collection of Microsoft certifications, including MCITP, MCDBA, MCSE, MCSD, MCAD, and MCTS, which proves that he likes to take tests. His expertise includes DMVs, high availability, full text search, and SQL Azure. How do I attend? Live Meeting link Using OLAP to Optimize and Maintain Predictive Analytics Models December 10, 2010 12 PM noon Eastern Time (GMT -5) Eugene Asahara In complex Predictive Analytics (PA) scenarios where it is being applied to a complex system or the players involved are actually trying to undermine the predictions (ex: credit card fraud), the sophistication of the PA must be taken up a notch or two. In this Webcast I will describe techniques for building sophisticated PA systems on the Microsoft BI Stack by using OLAP to: • Analyze, validate, and optimize PA models. • Manage and Monitor the performance of the PA models in a Performance Management style. • Surface PA results to end users in a manner that allows them to work through the ambiguity that remains around predictions. Eugene Asahara Eugene specializes in high-end Analysis Services implementation and performance tuning, predictive analytics, and overall BI architecture. He has thirteen years of experience on the Microsoft BI stack; including one year on the SSAS product team and seven years as a Lead DB/BI Architect at Microsoft Consulting Services. Aside from consulting on BI engagements by day Eugene develops bleeding-edge BI software by night. Many of his thoughts around BI and that bleeding-edge space can be found at www.softcodedlogic.com. How do I attend? Live Meeting link
Atlanta User Groups Different December 2010 Dates
For the month of December the meeting dates and what happening for different User Groups has been all rearranged for obvious reason (Christmas vacation). Here is what’s going on at the three user groups that I attend regularly: Atlanta B/I: Tonight! Atlanta MDF: Holiday Party, Next Wednesday This is a joint party with the following user groups: AtlantaMDF, Atlanta MS Pros, Atlanta .NET, Atlanta PHP, AWDG, Gwinnett .Net, IASA Atlanta, Silverlight Atlanta Atlanta PowerShell: No meeting this month Topic: BI: Then and Now? Level: Beginner Date: Monday, December 6, 2010 Location: Matrix Resources Dunwoody Office Sponsor: Strategy Companion Corporation Overview: We will begin by taking a look how the focus and characteristics of Business Intelligence have changed over the last 25 years. We will also discuss the recent history of Microsoft’s focus on BI, and will take an in-depth look at another approach to SQL Server-based BI provided by Strategy Companion Corporation. You will see why companies such as Citigroup, L’Oreal, Honeywell, DataQuick, and many others have embraced Analyzer, Strategy Companion’s award-winning front-end to Analysis Services, for their Business Intelligence applications. You’ll see why SQL Server magazine recently called Analyzer "the best solution to complete the Microsoft BI platform." (Editor’s Best Award, December 2009.) And you’ll learn ways to quickly and add significant value to your SQL Server-based data – the kind of value business people will be able to see, understand, and appreciate. Speaker: Bob Abernethy Bob Abernethy is SVP & GM of Strategy Companion Corporation. A veteran of Oracle Corporation and Siebel Systems, Bob brings over twenty years of software industry experience to his discussion with customers about their Business Intelligence implementations. Bob received his Bachelor of Science degree from Cornell University in New York and his Masters of Management Information Systems from West Coast University in Southern California. the current president of the Kansas City SQL Server Users Group. Atlanta Technical Community Holiday Party This year, Microsoft, will bring the Atlanta technical community together not only in spirit, but in holiday fun as well. We invite you to join us for a holiday celebration at {three} Sheets on Wednesday, December 15 at 7pm. This will be the perfect event for networking and seeing what’s going on with the different technologies in the community. When you’re not mingling, have some fun playing the new Xbox Kinect. Or enjoy some of the delicious appetizers provided by Microsoft. Don’t forget, it’s the giving season! It’s not required, but please bring an new, unwrapped toy to give to Toys for Tots. We would like to do our part for this wonderful cause.