SSIS - Script Task is not working on Prod Server

Introduction

A common error that developers are facing when the SSIS package is deployed in the Production Server.

To understand it properly, let’s take a simple example.

Suppose in our package, we have a script task which is used to open an Excel file and just rename the sheet with a dynamic values came from DB table. It works fine in Development environment. But when it is deployed in production server, the script task is showing error.

Reason Behind it

Our development environment contains Microsoft Office installed. To open the Excel file by script task, he needs some reference of Excel (Excel must be register in GAC). As the Microsoft Office is installed in the Development environment the script task have no problem to work.

But in the Production environment, if you observe carefully there is no Microsoft Office component. So in this situation the script task of our package not get any reference of excel that’s the reason of failing script task.

How to Solve it

Simple solution is just installed Microsoft Office in the Production Server. But it is not so easy task as licensing is related to it. Sometimes the client doesn’t want to install it in production server.

To solve such kind of thing, we can suggest client to install just “2007 Microsoft Office system Primary Interop Assemblies” in the production server.

 2007 Microsoft Office system Primary Interop Assemblies

Web link to download:

https://www.microsoft.com/en-us/download/details.aspx?id=18346

The 2007 Microsoft Office Primary Interop Assemblies (PIA) redistributable is a Microsoft Windows Installer package that contains the Primary Interop Assemblies for 2007 Microsoft Office products. Specifically, the redistributable contains the PIAs for the following products:

·         Microsoft Office Access 2007

·         Microsoft Office Excel 2007

·         Microsoft Office InfoPath 2007

·         Microsoft Office Outlook 2007

·         Microsoft Office PowerPoint 2007

·         Microsoft Office Project 2007

·         Microsoft Office Publisher 2007

·         Microsoft Office SharePoint Designer 2007

·         Microsoft Office Visio 2007

·         Microsoft Office Word 2007

  

Hope you like it.

Posted by: MR. JOYDEEP DAS