Temporary file in SSIS package

Introduction

Temporary file is a very important factors for every T-SQL developer. I think all the T-SQL developer must know the value of temporary file when they are working with complex T-SQL statement. In this article I am trying to use the temporary file in my SSIS package. Is not it an easy task? Yes but we must know some settings as the temp file is deleted when the session is completed.  Hope all of you must understand what I want to mean by that.

Using Temporary file in SSIS

Step-1 [ Here in the control flow tab we are using Execute SQL Task ]

By the Execute SQL Task we are going to create our temp table in the name of "Tmp_VendorsDetails". It contains Vendor ID, Vendor Name and Vendor Grade (like Grade A, B and C).

The SQL statement to Create Temporary File (in Execute SQL Task)

IF OBJECT_ID(N'Tempdb..##Tmp_VendorsDetails') IS NOT NULL

   BEGIN

     DROP TABLE ##Tmp_VendorsDetails;

   END

GO

CREATE TABLE ##Tmp_VendorsDetails

       (VENDORID     INT         NOT NULL IDENTITY(1,1),

        VENDORNAME   VARCHAR(50) NOT NULL,

        VENDORGRADE  VARCHAR(1));

GO          

-- Inserting Data to Temporary Table

INSERT INTO ##Tmp_VendorsDetails

       (VENDORNAME, VENDORGRADE)

VALUES ('RADHARAM TRADING', 'A'),

       ('RATAN TRADERS', 'A'),

       ('BANIK AND CO.', 'C'),

       ('TAHAMAT ALI STORE', 'B');         

Please note that we must run this script at SSMS to get it from data flow tasks.

 Step-2 [ Create the  Destination Table ]

-- Destination Table

IF OBJECT_ID(N'Tbl_DestVendorsDetails') IS NOT NULL

   BEGIN

     DROP TABLE Tbl_DestVendorsDetails;

   END

GO

CREATE TABLE Tbl_DestVendorsDetails

       (VENDORID     INT         NOT NULL IDENTITY(1,1),

        VENDORNAME   VARCHAR(50) NOT NULL,

        VENDORGRADE  VARCHAR(1));

GO                     

Step-3 [ Control Flow Task of SSIS ]

Step-4 [ Data Flow Task of SSIS ]

Step-5 [ Run the Package ]

We find error in the package.

How to Solve this Problem

To fix this issue, we will need to change a property on the connection manager. The name of the property is a connection manager property. If we right click the OLE DB Connection Manager and choose properties we will see a property called RetainSameConnection. This must be set to "True".

The RetainSameConnection property means that the temp table will not be deleted when the task is completed.

Run the package again

Hope you like it.

Posted by: MR. JOYDEEP DAS