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

With(NOLOCK) Vs With(NOWAIT)

Introduction

We all know about dirty reads and it is not good for transaction table specially OLTP environment.

But sometimes customer wants like we are going to fire the query from frontend, if the table is locked by other transaction it just simply return a message. We don’t want any dirty read or not want to wait until the Transaction finish. If necessary, we again fire the query after sometime to get the result.

Before jumping into this type of solution we are going to show something related to dirty read.

Scenario

We have table named customer.

CREATE TABLE tbl_CustomerDetails

       (

              CustID        Int           NOT NULL IDENTITY Primary Key,

              CustName      Varchar(50)   NOT NULL,

              ContactNo     Varchar(12)   NOT NULL

       );

GO

INSERT tbl_CustomerDetails

       (CustName, ContactNo)

VALUES('Joydeep', '123456'),

      ('Deblina', '897654');

We are trying to update this customer table from session-1 and in the same time we are trying to retrieve record from customer table in session-2.

Session -1

BEGIN TRAN

   UPDATE tbl_CustomerDetails

      SET ContactNo = '99999'

   WHERE CustID = 1;

Please note that we are not providing any Commit or Rollback Transaction option in Session -1.

Now try to retrieve the records from session -2

Session -2

SELECT * FROM tbl_CustomerDetails;

Here we are not getting any output, until the Session -1 is committed or roll backed. Here the update statement of Session – 1 is just make lock on table object.

So it is not going to solve our solutions.

Now, to get the result.

Session -2

SELECT * FROM tbl_CustomerDetails With(NOLOCK);

Here we are using With(NOLOCK) keyword to get the dirty data.

CustID      CustName                                           ContactNo

----------- -------------------------------------------------- ------------

1           Joydeep                                            99999

2           Deblina                                            897654

(2 row(s) affected)

Now think about a situation.

In Session -1 we are using Rollback Transaction after getting the result in Session -2

Session – 1

ROLLBACK TRAN

So, what we get in the Session -2 is not correct data. That is called dirty read.

Now, it is not going to solve our problem as customer doesn’t want to get dirty records.

To solve this problem we are using With(NOWAIT) key word.

Session – 2

SELECT * FROM tbl_CustomerDetails With(NOWAIT);

CustID      CustName                                           ContactNo

----------- -------------------------------------------------- ------------

Msg 1222, Level 16, State 51, Line 1

Lock request time out period exceeded.

It is not waiting or not giving any dirty data. It just simply through an error message saying Lock request time out. It means Session-1 provides a lock on this table and for that it is unable to retrieve data.

So, we have to wait and fire the query again until the Session -1 finishes his transaction.

It is definitely solve our problem statement.

Hope you understand it.

Posted by: MR. JOYDEEP DAS