Technical: Microsoft – SQL Server – Engine – Data Export and Import- Error – “The Execute Package utility requires Integration Services to be installed”

Technical: Microsoft – SQL Server – Engine – Data Export and Import – Error – “The Execute Package utility requires Integration Services to be installed”

Introduction

There is one little hurdle that one might have to cross when one uses “Microsoft SQL Server Management Studio – SQL Server Import and Export Wizard” to import and export data.

Error

The hurdle occurs when one tries to later re-use the package created. If “SQL Server Integration Services” is not installed on the machine, one gets the error pasted below:

Error: The Execute Package utility requires Integration Services to be installed by one of these editions of SQL Server Standard 2008: Standard, Enterprise, Developer, or Evaluation.  To install Integration Services, run SQL Server Setup and select Integratipn Services.

RequiresIntegrationServices

The error is straight forward.

Solution:

One possible is to install the latest an evaluation version SQL Server and be sure to include “Integration Services”.

Download MS SQL Server 2012 – SP1 Trial Edition

Download MS SQL Server 2012 – SP1 Trial Edition

Download Microsoft SQL Server 2012 – SP1 from http://technet.microsoft.com/en-us/evalcenter/hh225126.aspx.

Install MS SQL Server 2012

Install the downloaded version and make sure that you include Integration Services as part of installation.

Backup package

Make a copy of the package.  Using MS Windows Explored, please make a copy of the package.

Use “SQL Server 2012 \ Integration Services \ Project Conversion Wizard”

(

     This step did not work for our purpose as it does not produce

          the traditional dtxs/XML file, but a binary file (.ispac); which needs to

          be deployed to SQL Server/msdb or SSIS ENgine

)

Hopefully, you installed the tooling components that come with SQL Server 2012.  The nice tool known as Integration Services \ Project Conversion Wizard comes with it.

Launch Tool

Launch that tool and choose to convert the package

Locate Packages

The Locate Package window allows to choose the package’s location.  Remember a package can be saved on the File System \ SSIS Package Store \ SQL Server (msdb).

locatePackages

Select Packages

Select the packages that you want converted.

SelectPackages

Select Destination

Select the packages destination

Please create and select a new folder to save the new files.

Here you will have to deal with the more contentious aspects of SSIS /DTS packages; what to do with the step child that are packaged as “sensitive data“.

As always, I will skip the hard part by choosing “DontSaveSensitive”.

Select Destination

SelectDestination

Configuration and Parameters

Based on your packages, you might have to deal with configurations and parameters.

Review

Review your choices:

Review

Review

Review your choices.

Review

Result

Please review the result of the conversion process.

results

Close

And, click on the close button.

Execute Package

Launch SQL Server Integration Services Execute Package

Launch “Microsoft SQL Server 2012” / “Integration Services” / “Execute Package Utility”

LaunchExecutePackage

General – General – Load File

GeneraFile System

ExecutePackageUtility-General-PackageSource

File System

Empty File System as dtsx file not found!

ChooseFileSystemFile - dtsx

Using IDE ( Business Intelligence Studio [BIDS] / SQL Server Data Tools [SSDT]) Convert Package to new Version

Using SQL Server Business Intelligence BIDS  or SQL Server Data Tools convert package to new version.

We will use SQL Server Data Tools BI.

Download

Download SSDT (Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2012) from http://www.microsoft.com/en-us/download/details.aspx?id=36843.

Install SSDT

Install SSDT

Launch SSDT

Launch “SQL Server Data Tools for Visual Studio 2012” (SSDT)

launchSSDT

Open File

Using menu items (File \ Open file) …

The package is automatically updated:

The package is upgraded.
See the list of warning and information messages for details.

ThePackageIsUpdated

Save the “File As”

Use the menu “File / Save As” to save the file under a new name:

SavePackageToFile

Again, chose not to “Save sensitive”:

SaveCopyofPackage

Use “SQL Server Execute Package” to execute the package

Initiate SQL Server Execute Package

Using the Start button, initiate “Microsoft SQL Server 2012” \ “Integration Services” \ “Execute Package Utility”

LaunchExecutePackage_v2

Initiate SQL Server Execute Package

Using the Start button, initiate “Microsoft SQL Server 2012” \ “Integration Services” \ “Execute Package Utility”

Execute Package Utility – General

The first window that occurs is the “General” window:

ExecutePackageUtility-General-PackageSource

Choose :

  • Package source :- File System
  • Package :- choose the file

ExecutePackageUtility-General-PackageSource_AfterFileChosen

Execute Package Utility – “Connection Managers”

Make sure that you edit the DB Connection Settings:

ConnectionManagers

Once you have made neccessary changes, please click the Execute button.

Summary

In summary, you need SQL Server Integration Services to execute saved SSIS packages.

SQL Server Integration Services Conversion Wizard is promising, but a bit restrictive when one wants to quickly make and re-execute changes.

Leave a Reply

Please log in using one of these methods to post your comment:

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