Using Temp tebales in SSIS DataFlow

We often go through situations where we use temporary tables in stored procedures and then try to get the output from the temporary table in stored procedure.
When we try to use such procedure the SSIS packages give metadata error as it can not determine such output and can not create specific metadata.

To solve the above issue we use WITH RESULT SETS () to specify outputs.

Lets create table employee
CREATE TABLE EMPLOYEE
(
ID INT,
[NAME] VARCHAR(30)
)

INSERT INTO EMPLOYEE VALUES (1,'James'),(2,'Elina'),(3,'Subramnyam')

After creating the table and inserting records lets create a procedure to pull data from employee table and store in temp table and then send the output.

CREATE PROCEDURE uspGetEmployees
AS
BEGIN
SELECT * INTO #EMP FROM Employee

SELECT * FROM #EMP

IF OBJECT_ID('tempdb..#EMP') IS NOT NULL
DROP TABLE #EMP

END


Lets try to use the above procedure in an SSIS package

Drag a data flow task in your SSIS control flow container as shown below.

Add OLEDB connection object to the database where you created above objects.
Drag OLEDB source inside data flow and select your oledb connection.

Now select SQL Commond from the data access mode drop down.

Finally write the below query in SQL Command Text area

Now drag the dataflow pointer to your destination and execute the data flow.

Try the same process by removing WITH RESULT SETS.