SSIS 2008 and SSIS 2008 R2 are the same (or are they?)

SSIS 2008 and SSIS 2008 R2 are the same

Well pretty much.

One big difference is that the ADO.NET Destination has been upgraded. Trouble is that the upgrade is tiny and SQL Server 2008 is not forwardly compatible.
If you use an ADO connection and develop your package on 2008R2 then deploy to a 2008 box you will get the errors…


Code: 0xC004801F
Source: Codes Codes (SSIS.Pipeline)
Description: The component metadata for "component "ADO NET Destination" (16)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. End

And

Code: 0xC0047062
Source: Division Division (ADO NET Destination [490])
Description: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "ADO NET Destination" (490) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper100 wrapper, Int32 lPipelineVersion) End

The problem is easily fixable if you don’t mind editing the XML code for the package and this is explained excellently in this article

http://agilebi.com/jwelch/2010/05/13/moving-ssis-packages-with-ado-net-destinations-between-2008-r2-and-2008/#comment-64654

Basically it revolves around setting the version number back and removing the line that 2008 cannot handle / understand.

I mention this as it annoys me that MS could not simply create a hotfix for the 2008 boxes so that if they see the higher version number they don’t read the additional lines / new functionality.

If you could develop a package for either then this would not be an issue but as the tools (BIDS / Visual Studio) only create for a single audience then it’s a bit difficult for a developer who has to deal with both deployment targets to make backward compatible packages.

In Visual Studio / Bids you can create reports for either version so why not packages?

I shall post about this to Microsoft Connect and if it affects you then I urge that you do the same.

Dave

Solution / work around

Look in xml for each element of type “component” named “ADO NET Destination” and change the version=”1″ attribute to version=”0″
Then within each component element there is an element called “properties” which contains several “property” elements. Simply delete the property with name=”UseBulkInsertWhenPossible” as it is not recognised by 2008

e.g.

<component name="ADO NET Destination" version="1" ……>

<properties>

<property name="TableOrViewName" … >xxx</property>

<property name="BatchSize" … >0</property>

<property name="CommandTimeout" … >30</property>

<propertyname="UseBulkInsertWhenPossible">true</property>

</properties>
</component>

Becomes

<component name="ADO NET Destination" version="0" ……>

<properties>

<property name="TableOrViewName" … >xxx</property>

<property name="BatchSize" … >0</property>

<property name="CommandTimeout" … >30</property>

</properties>
</component>

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Developer stuff, SQL Stuff and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s