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!

Check out Microsoft Surface

If you have not seen this, you must check it out.

This technology looks awesome. Minority Report type GUIs are not too far off! I have dreamt often of this sort of thing. I think in a few short years time we are going to be seeing a whole new generation of applications based around this sort of interface.

Wednesday, May 30, 2007

SSIS Extracting data from OleDB Connection with Parameters

I have been putting together a DTC package using Business Intelligence Studio for Visual Studio 2005. For those in the know, this is the IDE for working with Integration Services for SQL Server 2005.

One of the tasks I had was to pull data from an Oracle data store based on a parameter that was generated from a query to a SQL 2005 db. I would then need to move the data into the SQL 2005 database, along with a few derived columns.

Whilst I was easily able to set up connectivity to the Oracle DB using ODBC within a Data Flow scope, I needed to use parameters and the Data Reader object does not support them. OleDB does, if you use a variable with an expression you can evaluate. I'll post how to do this later.

The problem I had was that the Microsoft OleDB provider for Oracle does not seem to work very well. By that, I mean I could not get the thing to function at all within a Data Flow section, but I could get it to work within a Control Flow.

The answer to all my problems was to download and install the relevant Oracle OleDB Provider. I made the relevant adjustments to my "OLE DB Source" component and relevant connections and ran the project. Everything worked. Having now deployed the package to the test environment and created and scheduled a job, there have yet to be any further problems.

So the lesson I learnt from this was that if you are going to connect to a database from the Microsoft platform always grab the database manufacturer's providers. They are generally free and supported so hopefully fairly simple to justify the addition to deployment.

Changes in Functionality from Windows Server 2003 with SP1 to Windows Server 2008

So Longhorn has a name and it Windows Server 2008. There is a document up at the Microsoft site now that has a description of all the changes from Server 2003 SP 1 to Server 2008. You can grab that here.

Monday, May 28, 2007

Oracle Import Error: Could not validate configuration of Primary Transport of Send Port

I have been attempting to import some binding information for a BizTalk application I've been working on for a couple of weeks. The specific problem has to do with the OracleDB adapter bindings.

When I tested out my deploy script on the test servers I received an error quite similar to this:

TITLE: Import Error

Failed to import "pathtomsifile".
Location of the log file is provided in the Results page.

Change requests failed for some resources. (mscorlib)

Unable to process create change request for BizTalk binding "BindingInfo.xml". Import bindings (overwrite) failed. (Microsoft.BizTalk.ResourceManagers)

Failed to update binding information. (mscorlib)

Could not validate configuration of Primary Transport of Send Port 'portname' with SSO server.
Specify user name and password (Microsoft.BizTalk.ExplorerOM)

Specify user name and password (Microsoft.BizTalk.ExplorerOM)

A solution to this can be found here. A later verson of Microsoft.biztalk.adapters.bizutil.dll is required in the GAC than the one I had! It needs to be dated 14th Sept 2006 or later.

Thursday, May 24, 2007

BizTalk Server Pattern Wizard

Available here is a freeware type tool called the BizTalk Server Pattern Wizard. It claims to help capture and reuse your BizTalk projects best practices.

SQL Server 2005 Best Practices Analyser

Available here is the February 2007 CTP of the SQL Server 2005 Best Practices Analyser. Information about this can be viewed here.

Here is a screen shot of the output of a basic scan on my locally hosted test server:

Wednesday, May 23, 2007

Beta of Orcas (Next Version of Visual Studio) Out

You can download a beta of Orcas here. This is also gives the community a glimpse at the .NET Framework 3.5 with Beta 1 provided. So for those of you who are keen to get an early start in adapting to the new technologies and development environment for what will primarily be a Vista world, check it out.

VPC images are also available, with and without Team Foundation Server.

Oracle Adapter Error

Was running into an issue with the Oracle Adapter that comes with the "Microsoft BizTalk Adpaters for Enterprise Applications" bundle. At run-time event log was reporting three errors messages, including "Runtime Error : Unable to load DLL (BAUtil.dll)."

After a bit of googling I found the following solution on the Microsoft Knowledge Base site.

This solved the problem, however it may be worth noting to check the security settings for the "C:\Program Files\Common Files\Microsoft BizTalk Adapters for Enterprise Applications" folder. You need to make sure the BTS host(s) that will be utilizing this adapter have Read/Write permissions there.

Deploy Failed, "A file load exception occurred while attempting to install the assembly into the Global Assembly Cache."

Occassionally when deploying a BizTalk resource from Visual Studio you will receive the following error message:

Error 16 Failed to add resource(s). Change requests failed for some resources. BizTalkAssemblyResourceManager failed to complete end type change request. Unable to install the "C:\Documents and Settings\foc18bs\Local Settings\Temp\BT\PID1188\BizTalkAssembly\c94a039fff06502b4de07ec61b983b9c\EPS.Schemas.dll" assembly into the Global Assembly Cache.
Exception: A file load exception occurred while attempting to install the assembly into the Global Assembly Cache. This error may occur if the assembly is delay signed, or if assembly was renamed after creating/signing.

I encountered this problem today and after a bit of exploring found out that it was happening because the assembly in question was still in use by a process. Sometimes it can get difficult to track down what process is using the file so I consulted the free Microsoft utility, Process Explorer and found out that some mmc process had a handle on it.

I killed all the non devenv processes that had a handle on the dll. I was then able to successfully redeploy.

Edit: You may have to kill some of the handles that devenv.exe has as well. Spefically those that have a handle on the instance of the dll in the GAC directory.

Tuesday, May 22, 2007

Starcraft 2

Not technically anything to do with BizTalk or .NET (although perhaps Starcraft is coded using c++/clr!), but I thought it well worth mentioning.

Starcraft 2 has been announced by Blizzard!

The orignal game was considered to be one of the best and the title has become a classic. The original created TV celebrities from Korea and still remains as one of the top RTS games today.

Friday, May 18, 2007

Multiple Hosts on a Single BTS Server

Some of you may have come across the BizTalk Server Best Practices Analyzer. You can run this tool against a BTS server and it will spit out a quite verbose listing of currently existing configuration issues and helpful recommendations.

One of the more interesting recommendations it makes is about the use of multiple hosts. Using a separate host and host instance for receive ports, send ports, tracking and orchestrations allows for some significant flexibility. From an infrastructure point of view it makes upgrading your single machine installation to a multi-machine or farm a far easier task. There should be performance increases as you are separating the workload amongst a group of processes instead of one. A discussion is brewing over at topxml.


Recently I have been working on a BizTalk project that receives large messages at a fairly regular interval. Whilst BTS 2006 does implement an enhanced messaging engine that touts improvements to transformation carried out on large messages, in practice I have found the increase to be noticeable but not enough when dealing with increased traffic as well.

There is a great article of debatching inbound messages from the sql adapter over at Richard Seroters blog.

If any of you run through this, something that caught me out was not setting the Body XPath attribute on the schema designated as your envelope. This attribute, which is found in the Parse section of the your desired route node tells BizTalk where to start looping for child messages. So if you need to go three or four layers deep before you want to start looping you can get around having to create an intermediary schema by using this. If you don't set this property it is very unlikely that the debatching process will work for you.

Thursday, May 17, 2007

Windows Server 2003 Resource Kit Tools

In case any of you do not have this package, do yourself a favour and download it. Essential!