Tagged: sql

SSIS – Change the Protection Level for all Packages in a Project

Here’s a quick one I found this morning while fixing an execution issue with one of my SSIS packages. I needed to change the Protection Level of all the packages in a project from Encrypt With User Key to Don’t Save Sensitive. The project contains over 30 packages, and I didn’t really fancy opening each one, navigating to the properties and amending the Protection Level for each one – that’s too time-consuming and frankly, boring. I initially figured I’d write a PowerShell or Python script to amend the relevant XML in each of the .dtsx files. But a quick Google came up with this MS Technet page, which details how to make the change via the command line, for both individual packages and for all packages within a project.

A quick amendment to the command to use the value for DontSaveSensitive (0) and to remove the password parameter was all that was required, so running this…

for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;0

…and pressing Y to confirm each change as prompted, resulted in the change being made to all packages. Result! Well almost. After going back to Visual Studio and changing the Protection Level of the Project (it must match that of the Packages), I tried to build the solution. It returned an error stating that for all the Packages, the Protection Level was incorrect and had to match the Project. But I’d just changed all of that! Turns out I had to open all the packages in Visual Studio (for some reason), and then it would build without making any further changes. Odd.

While this is useful to know for future reference, I’m much more likely to be developing SSIS projects using BIML, which would make changes like this far easier, as it would be a simple change in the BIML template, re-generate the packages and job done. It’s the future…

Try R, a free course

I’ve been branching out a bit in my spare time with regards to other tools and languages for manipulating and analysing data. The R language has interested me for a while, and I came across this free tutorial which serves as a pretty good introduction to its basic usage and syntax. It does so in a rather nice interactive web interface. Highly recommended if you have a spare hour or two to go through the course. As a plus, there is a link to a discount on the O’Reilly book Data Mashups In R once the course has been completed. R itself can be downloaded from here. Get stuck in fellow data geeks!Rlogo

Highlights of 2012

I don’t consider 2012 to have been a great year personally, I’m quite glad to see the back of it. But I do have to join in with almost everyone else in the blogging world and post my highlights of the year. First up of course, is albums…

Records of the year

  1. Mid Air – Paul Buchanan (Mid Air on Youtube)
  2. The Devil’s Debt – Love & Money (The Devil’s Debt on Youtube)
  3. Now for Plan A – The Tragically Hip (Goodnight Attawapsikat on Youtube)

Books of the year (not necessarily published this year, but read by me in 2012)

  1. No Country for Old Men – Cormac McCarthy
  2. A is for Angelica – Iain Broome
  3. The Deep Dark Sleep – Craig Russell

Tech books of the year

  1. DBA Survivor – Thomas LaRock
  2. How to Become an Exceptional DBA – Brad McGehee
  3. Pro SQL Server 2012 Practices – a ton of great SQL folks (still reading this but it’s the business)

Beers of the year

  1. Hit The Lip – Cromarty Brewing Co
  2. Magic 8 Ball – Magic Rock Brewing
  3. A Face With No Name – Tempest Brewing Co

Sporting moment

The 49ers beating the Saints in the playoffs courtesy of this last-gasp Alex Smith touchdown pass to Vernon Davis.

Personal highlights and goals

  1. Finally getting my degree through the Open University
  2. Running 5k for the first time ever (using Couch To 5k)
  3. Reading 50 books in the year – thanks to my Kindle!
  4. Meeting one of my music heroes, Nile Rodgers, at the Edinburgh Book Festival2012 08 22 11 55 29