Batch update properties in SSIS packages (T-SQL Tuesday #68)

T-SQL Tuesday #68 is hosted by Andy Yun (@SQLBek). Many SQL Server defaults are not ideal, and most of us have a list of defaults we always change. Andy wants us to Just Say No to Defaults and blog about what, why or how we change defaults.

If you are an SSIS developer like me, there is a big chance that the ProtectionLevel in SSIS Packages is on top of your list of defaults to change. The default ProtectionLevel is EncryptSensitiveWithUserKey (ugh), but most of the time it is not the best option. Raise your hand if you have ever asked your favorite search engine for advice on issues like “SSIS package fails in SQL Server Agent job” or if you have ever heard someone exclaim “but it works on my machine!?” 😅

There are many great blog posts about the different ProtectionLevels, why you probably want to change to DontSaveSensitive as your default, and how to use configurations and parameters instead of encrypted SSIS packages. I will not go into details about any of that in this post, but I will use ProtectionLevel as an example default property you want to change in many SSIS packages at the same time.

How do you batch update properties in existing SSIS packages? You probably don’t want to open up every single package and change them manually?

Batch Property Update in BIDS Helper

BIDS Helper. has a handy little feature called Batch Property Update that allows you to update a property in one or more packages at the same time. Just select all the packages you want to update, right-click and click Batch Property Update:

Enter the Property Path and the new value. You can change any property, but in this example you would enter:

Property Path: \Package.Properties[ProtectionLevel] Value: 0

(0 = DontSaveSensitive. For other values, read the Access Control for Sensitive Data in Packages article.)

Click OK, and that’s it! If you have many packages, you may want to grab a coffee when you click OK. BIDS Helper will actually open up all the packages to update the property, and this can take some time.

There are of course other ways to batch update properties in SSIS packages. You can use the dtexec utility with the /Set parameter, PowerShell or even a Find & Replace in files. If you are only interested in changing the ProtectionLevel, especially if you need to encrypt packages with passwords, Microsoft has written an article about how to Set or Change the Protection Level of Packages using the command line.

However, if you are just looking for a simple way to batch update properties in SSIS packages without having to open other programs or write any code, the BIDS Helper Batch Update Property feature is very handy.

(P.S. My recommendation is of course to start using Biml to generate your SSIS packages. Not only is DontSaveSensitive the default ProtectionLevel in generated SSIS packages, but changing anything is super easy when using Biml. You only have to do it once for all your packages!)

About the Author

Cathrine Wilhelmsen is a Microsoft Data Platform MVP, international speaker, author, blogger, organizer, and chronic volunteer. She loves data and coding, as well as teaching and sharing knowledge - oh, and sci-fi, gaming, coffee and chocolate 🤓