How to upgrade SSIS 2005 to 2008 ?


In our process to upgrade the SSIS 2005 to 2008 packages, we need to be aware of the server being 64 bit for SSIS 2008.

Before running the upgrade package wizard, make following changes in existing packages:

  • All SSIS script tasks – PrecompileScriptintoBinarycode =TRUE [This is our best practice recommendation] as SSIS 2008 does not show this option and is set to true by default.
  • Remove all configuration file references from the package [this should not be there, as in our best practices for package creation, we remove the config file reference].
  • If the Script Task or Script Component references IDTSxxx90 interfaces, then you might need to change these references with the corresponding IDTSxxx100 interfaces before package upgrade.

You cannot change the language from VB (which was only option in SSIS 2005) to C# during the upgrade but you can create a new Script Task or Script Component in after upgrading to SSIS 2008.

Upgrading with the SSIS Package Upgrade Wizard:

SSIS 2008 includes the SSIS Package Upgrade Wizard which can use upgrade packages and also, if required, back up the original packages (though the back-up option works only for the packages stored on the file system). You can launch SSIS Package Upgrade Wizard from command prompt by typing SSISUpgrade.exe (default physical location C:\Program Files\Microsoft SQL Server100DTSBinn folder)

For more information on Upgrade Wizard:

http://www.sql-server-performance.com/2010/upgarde-2005-packages-2008-i/

Changes in SSIS 2008 which we need to consider after the packages are upgraded to 2008.

  • SSIS 2008 uses Visual Studio 2005 Tools for Applications (VSTA) for its scripting environment
  • SSIS 2008 uses a newer build of the SQL Server Native Client
  • SSIS 2008 contains an updated OLEDB Provider for Analysis Services (10.0)

Advantages of VSTA – Visual Studio 2005 Tools for Applications:

All scripts are precompiled in SQL Server 2008 Integration Services (SSIS). When a script is precompiled, the language engine is not loaded at run time and the package runs more quickly. However, precompiled binary files consume significant disk space.

We can divide the changes in SSIS in two categories:

1)      Script task changes and

2)       Connection string changes (this includes the SQL server native client and SSAS connection string changes)

Script task changes:

Error: Name ‘DTS’ is not declared

This problem occurs because the DTS object is not a static global object in SQL Server 2008 script tasks.

To resolve this problem, use one of the following methods:

  • Move the code that depends on the DTS object into the ScriptMain.vb file. The DTS object is an available object in the ScriptMain.vb file.
  • Pass the DTS object as a parameter to methods in the other files in your project.

Connection string changes:

Update connection strings to use the SQL Server 2008 names for the following providers:

  • Analysis Services OLE DB provider
  • SQL Server Native Client

Change the “Provider” portion of the connection string from “SQLNCLI.1” to “SQLNCLI10.1“.  The wizard does not update package configuration files.

Hope this will help !!!

Jay Ganesh

Advertisements

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