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.
