Monday, December 19, 2005 - Posts

SSIS Packages are Encrypted by Default

By default, SSIS files in development are encypted to prevent an unauthorized person from seeing your SSIS package. The type of encyrption is seamless behind the scene and is at a workstation and user level. So, if you were to send a package that you're developing to another developer on your team, he would not be able to open it by default. The same would apply if you logged in with a different user. You would receive the below error:

There were errors while the package was being loaded.
The package might be corrupted.
See the Error List for details.

The error is very misleading. In truth, you can't open the package because the originating user encrypted the package whether on purpose or not. To fix this, the owner of the package can open the package and in the Properties pane, select a different option (like a package password) for the ProtectionLevel option. The default option is EncryptSensitiveWithUserKey. To protect the entire package with a password, select the EncryptAllWithPassword option.

An option that I like is that a SSIS designer encrypts all packages with the default option and when he's ready to send to production, he can develop a batch file to loop through a directory's .dtsx file and set a password. The batch file would use Dtutil.exe and look like this: 

for %%f in (*.dtsx) do Dtutil.exe /file %%f /encrypt file;%%f;3;newpassword

This would loop through each .dtsx file in yoru directory and assign the password of newpassword. The production support group could then use the same batch file to reset the password to a production password.

-- Brian Knight

SQL Server 2005 Business Intelligence Metadata Samples Toolkit Released

For the past few months, I've been working on a whitepaper for Microsoft on metadata. The whitepaper evolved as did the product. Well, finally both the product and the whitepaper have been released! So what does it do? Well it's a starter kit that provides you all the code for both viewing and analzying your enviornment's metadata. For example, if you have dozens of SSIS packages in your environment that all load a similar table, you can use this tool to see what the impact would be if you altered the table or the package as well as see the lineage of how the data got into the table. The kit includes:

  • DependencyAnalyzer: a utility that scans SSIS packages and SSAS databases for enumerating metadata (objects, properties and dependencies).
  • DependencyViewer: a utility to view metadata organization & inter-dependencies (lineage/impact analysis).
  • Analysis Services Samples : sample SSAS packages that can be analyzed
  • Integration Services Samples: sample SSIS packages that can be analyzed
  • Report Model Samples (Report Builder): report builder model
  • Reports: sample reports built from metadata store
  • SQL: SQL script for creating metadata store

Download the Toolkit:

I did a quick video also on how to use it that can be downloaded in MPG format here. Downloader beware though! The video is not optimally compressed and runs 80 MB. But, if you have a high-speed internet connection here's the link:

-- Brian Knight