Thursday, May 31, 2007

SSIS OleDB Acquire Connection 0xC0202009 Deployment Considerations

When deploying a SSIS DTS Package to SQL Server 2005 and using any OleDB connections it is worth while considering the Protection Level property. This property is associated with your Control Flow and has a couple of different settings.

When using the "Encrypt all with Userkey" which it defaults to, you are more than likely going to run across a 0xC0202009 type error when running the package on a server other than the one you developed on. To be more specific- running the package under a user account other than the one you built the SSIS Package in Visual Studio on.

To get around this simply set the Protection Level to "Encrypt all with Password". At design time you will set your password and at deploy time you'll need to type it in. This will allow you to persist security information across servers.

There is a thread about this at Developerdex. I tend to find answers to some obscure questions there.

Matt Yeager was the chap who responded with the required information. Thanks Matt!

No comments: