Recently I was working on an SSIS package that was to be executed in a SQL Server 2008 instance. The package had already been created in BIDS 2008; I just needed to make some changes to the SQL on a few steps. So I opened the package in my local SQL Server BIDS 2008 R2, made the updates, saved the package, and submitted it for review.

When they tried to open the package in their local installations of SQL Server BIDS 2008, two of my team members got an error and were not able to open the package.
This puzzled me, as the enhancements in the second release of SQL Server 2008 were made to the database engine. Upon researching the error, I discovered that these two versions are compatible, unless the package is exporting to an ADO.NET destination, which was true in my case. Unfortunately Microsoft does not provide a hotfix for this problem, but the steps to manually correct this are easy.

First, open the package in a text or XML editor, then browse to the Component tag that contains your ADO.NET destination.

<component id=”16″

name=”ADO NET Destination”

componentClassID=”{2E42D45B-F83C-400F-8D77-61DDE6A7DF29}”

description=”Writes to a database using ADO.NET provider.”

localeId=”-1″

usesDispositions=”true”

validateExternalMetadata=”True”

version=”0″

pipelineVersion=”0″

contactInfo=”Writes to a database using ADO.NET provider.;Microsoft Corporation

Under this tag, edit the “version” attribute by changing the value from “1” to “0”. Next, remove the entire <property name=”UseBulkInsertWhenPossible> element by either commenting it out or deleting it.

Then save your changes and your package is ready for deployment.

Here is the full article that outlines this problem: Moving SSIS Packages with ADO .NET Destinations between 2008 R2 and 2008

Like this post? Share it!