SQLChicken.com

SQL Server DBA Tips & Tricks

By

Deploying SSIS Packages with BIxPress

oldbusted-newhotness

Same functionality but obvious differences

If you’ve worked with SSIS for any amount of time, you may quickly come to find that the native way of deploying packages can be…cumbersome. The native tools, while helpful, aren’t exactly the most intuitive to setup or use. This post will show you how you can quickly and easily deploy and configure packages using BIxPress.

Old and Busted

Before I show you how to deploy packages, I should probably quickly explain how to deploy packages in SSIS using native methods. I won’t go into every single detail here on how to deploy packages natively, however, if you’re interested in doing it step-by-step the built-in Help in Business Intelligence Development Studios (BIDS) has a complete walkthrough tutorial for you to check out. To access those tutorials simply press Ctrl+F1 from within BIDS (or click on Help menu and select How Do I from menu). From the ‘How Do I?’ list click on Find Tutorials, Integration Services and then select your tutorial. The one I’m referring to in this post is the Tutorial: Deploying Packages.

The condensed version of the tutorial is this: in order to deploy packages you have to go through a series of steps that aren’t exactly obvious from the interface. First, you have to manually enable the ability to even deploy. You get to this by going to the properties of the project, go to Deployment Utility and set the CreateDeploymentUtility option to True. Once you’re done doing that, you have to build (or rebuild) the project for it to generate what is called a Deployment Manifest file. This file is saved to the file path configured for DeploymentOutputPath where you set the properties for the Deployment Utility. This part alone reeks of user-interface fail to me, but I digress.

Once you’ve created your deployment manifest you’ll need to copy that manifest file out to a share on the target server. After you’ve copied it there, you double click it to launch the Package Installation Wizard. This wizard is pretty typical of Microsoft wizards and is pretty straightforward as far as walking you through your various options. For complete details on deploying using the wizard, refer to the tutorial in the Help. By the time you’re done with the wizard you’ll have deployed the package but your options for customization of deployment are limited.

New Hotness

After learning SSIS over the past year, one of the things that BIxPress has absolutely spoiled me with is the ease of deploying packages. In BIDS just right-click on your package and select Deploy SSIS Package (BIxPress) from the context menu. This will launch the BIxPress Package Deployment wizard. The first screen that comes up gives you a few really cool options such as copying folder structures (if needed), deploy XML files for you if you used XML configurations, you can change the location of those configuration files on your target server, and even change the package level protection from here. These options here have made deployments a breeze for me as on QA servers I had clients putting configs in D:SSISConfigs and on production it was something different like E:SSIS_Configs. Being able to quickly and easily change these options on the fly has saved me tons of headaches.

The next screen is the real meat of this feature. Here you can actually select more than just the one package you right-clicked initially for deployment. Additionally you have lots of options for deploying to and from a server. You can deploy to/from your regular options of File System, SQL Server or SSIS Package Store but here its easily laid out for you for ease. Speaking of ease, ever wanted to deploy in the opposite direction (i.e. production to development)? Simply check off the box ‘Enable 2-way deployment’ and you can quickly deploy bi-directionally quickly and easily. Pretty slick, eh?

Once you check off the packages to deploy and select your deployment destination options, simply click the deploy button in the middle and it quickly deploys your packages. Once it is complete you get a summary of the deployment results which you can save for change management purposes. That’s it, you’re done!

If you want to try out BIxPress you can download a trial copy from the Pragmatic Works website.

Share

By

OMG I’m an MVP!

I can’t believe it, but I can finally say that I’m now officially a Microsoft SQL Server MVP! Last week I got the great news from my MVP lead, Ryan Bolz (Twitter) in…a peculiar way. Allow me to explain. Typically MVP renewals and new awardees are notified four times a year, once every quarter. This past quarter’s notifications went out on July 1st. Like so many in the community, when the quarterly MVP day rolled around I was guilty of refreshing my inbox every 5 minutes for 24 hours hoping for that magic Wonka ticket into MVP-ville.

Felt a little something like this...

Alas, July 1st came and went with no email. Disappointed turtle was disappointed. Now I say I was disappointed since I knew I’d been nominated the last few quarters but hadn’t heard anything yet. And yes, I know “it’s the award you’re not supposed to want”, but still it’s stressful knowing you’re up for it but not hearing anything one way or the other. This time around I figured “oh well, keep it up and eventually…maybe”. Anywho, last week as I was at the JSSUG meeting when I got a DM from Ryan with the following: “Hi Jorge — Congratulations! You are a MVP. Call me so I can explain it all to you.”

Say WHAT?!? I checked and double checked the message to make sure it wasn’t some sort of sick prank. As soon as I got out of the meeting I gave Ryan a call to find out what was going on. I’d heard of folks getting awarded out of cycle before but I was still thoroughly confused as to what was going on! As it turns out, apparently I HAD been awarded MVP status in the July 1st batch but something went wrong with getting me the notification. For those not familiar with the process, Microsoft sends out the MVP emails en-masse to folks which seems to trip every spam filter around. Since this is the case, folks are told to check their spam folders on MVP day just in case. In my peculiar case it wasn’t a matter of spam filter black hole but rather an error in my contact information. Near as I can tell they had an invalid email address on file for me so they weren’t able to send me my notification!

Fast forward a few weeks and Ryan had gone on vacation and gotten sick during that period, when he finally got back to the office and catching up he somehow discovered that I had no idea I was awarded. That’s when he dug around and sent me a DM on Twitter as that was a guaranteed means of getting to me directly.

The Mushy Part

Story aside, I want to let you all know how truly grateful I am for this honor. It’s been a pleasure and privilege being a part of this amazing SQL community and this award just compels me to keep working even harder to deserve it. I want to thank everyone for the warm congrats and support, it means a LOT! I’d also like to send a special thank you to those who nominated me and sent me words of encouragement every quarter.

Speaking of working harder I got another piece of amazing news last week as well: I’ll be presenting for the first time at PASS Summit this year! What makes THAT even cooler is that the session is a Community Spotlight session, meaning you guys were gracious enough to cast your vote for me so I can bore educate the masses on Policy-Based Management at the Super Bowl/World Cup of SQL Server events! That being said thank you all so SO much for all your love and support and I look forward to continue serving this community for years to come!

Share

By

Make It Simple With Policy-Based Management

At work recently, I explained to a coworker the reasons and benefits of having databases in Simple mode in your development environment. Funnily enough, a few hours later, I see my friend Andie Letourneau (Blog | Twitter) posted a blog on how to adjust your recovery modes in development. While Andie’s homework assignment is to substitute it with a WHILE loop, I have MUCH simpler solution using Policy-Based Management!

First, let’s create the condition and policy. As always you can either use this T-SQL or you can simply download the XML policy and import it:

Download Policy Here

TSQL:

Now that you’ve created your policy, you can either evaluate it directly on that server or we can leverage the Central Management Server feature to evaluate this policy against one or more servers in your environment! Since we’re looking at this from an enterprise-level standpoint, we’re going to go over the CMS way of doing this.

Once you’ve established your CMS, you’re going to want to register servers to it. You could register everything directly under the CMS but then it becomes one giant list and that’s not very nice. One method I like to implement in my CMS is to create new server groups for each environment (e.g. DEV, QA, PROD). Within each group, I also create additional groupings for each version of SQL Server (e.g. 2000, 2005, 2008). Once the groups/folders have been created I then register my servers in their appropriate spots.

The advantage of breaking groups up like this is that CMS allows you to query against multiple servers at once. For instance if you were to right-click the folder for DEV, which in my case contains two registered servers in groups beneath it, SSMS will connect to both instances so that you can query them at the same time. It is this mechanism that we’ll use with Policy-Based Management since that feature allows us to evaluate policies against multiple servers in the same way.

To evaluate this policy, right-click the DEV folder and select Evaluate Policies. For source, click on the ellipses button and select either the folder location of the XML file or select the SQL Server instance which you imported the policy to. Once you’ve selected the policy’s location, you’ll see the Evaluate Policies screen with a list. Tick the box for the Simple Mode Check and then click the Evaluate button.

Check that out, you just evaluated policies against all of the databases in DEV! The databases that are NOT in simple mode (per our condition check) fail the policy evaluation and show up with red X’s. To quickly switch those to simple mode simply check the boxes for those that failed the check, then click the Apply button. This will enforce the policy on those databases and switch them for you to Simple mode! Imagine doing this that quickly and easily against hundreds of databases!

If you’re wondering if you can automate this process, the answer is absolutely! Check out the great open-source project Enterprise Policy-Management Framework over at Codeplex. This project allows you not only automate this process and policy enforcement but it also offers some really nice reporting of all this as well!

UPDATE: After chatting with Andie it looks like I forgot to mention a few things. Namely that for those wondering if this works on down-level servers (2000, 2005, etc.) the answer is YES! So long as you have a SQL Server 2008 server acting as your CMS, you can evaluate (certain) policies against down-level servers. Given this confusion I’ll write up a post on how all this works and what the caveats are.

The other point to make is that CMS uses Windows authentication only. If you have multiple domains, and there is no trust established between the domains, then this solution won’t work. If you DO have multiple domains and trust established, so long as your credentials allow you to traverse domains and the proper security authorizations are in place on the target SQL Servers then it will work.

Share