SSIS - Using a temp table in a data flow source


Create a stored procedure that will populate the few records. The sample SSIS package will first call the stored procedure and then will insert the records into physical table and then populate the records into flat file.

Create a stored procedure that will populate the few records. The sample SSIS package will first call the stored procedure and then will insert the records into physical table and then populate the records into flat file.

1. Create a stored procedure that will create a temporary table and populate with few records.


SET ansi_nulls ON

go

SET quoted_identifier ON

go

CREATE PROCEDURE SP_SAMPLEPROCEDURE
AS
BEGIN
SELECT *
INTO #temp
FROM (SELECT 'AAA' AS NAME,
100 AS [Value],
'Delhi' AS [Place]
UNION
SELECT 'BBB' AS NAME,
200 AS [Value],
'Chennai' AS [Place]
UNION
SELECT 'CCC' AS NAME,
300 AS [Value],
'Bangalore' AS [Place]
UNION
SELECT 'DDD' AS NAME,
400 AS [Value],
'Mumbai' AS [Place]) AS TT

SELECT *
FROM #temp
END

go


2. Create a new SSIS project and solution.
• Open SQL Server Business Intelligence Studio (BIDS).
• Select "New Project"
• Select 'Integration Services' template.
• Name of "SSIS" (this will be the name of the overall solution).
• Location of existing folder "C:\SSIS".
• Check the 'Create Directory for Solution' box.
• Click 'Ok' and the Solution will be created.
• In the Solution Explorer window Right click the default package name of 'Package.dtsx' and select rename.
• Use name "SampleExcel.dtsx" and press. Select yes to renaming the pack object as well.

1

3. OLE DB Connection Manager
Right click on the connection manager pane and select "OLE DB Connection Manager"

22

4. Right-click the connection manager and click Properties. Set the property RetainSameConnection on the connection "LocalHost.Test" to the value True.

44

5. Create a variable "TableName" of string type and assign the value "TempTable".

33

6. From the Toolbox, Double-click or drag the 'Execute SQL Task' to the Control Flow canvasa and use the below sql script to insert the data into physical temp table from stored procedure.

 
IF OBJECT_ID('TempTable') IS NOT NULL
BEGIN
DROP TABLE temptable
END

go

CREATE TABLE temptable
(
NAME NVARCHAR(50),
value INT,
place NVARCHAR(50),
)

go

INSERT INTO temptable
EXEC sp_SampleProcedure

go


7. Drag and drop a Data Flow Task onto the Control Flow tab.
8. Right-click the Data Flow Task and click Properties.Set the property Delay Validation to the value True.
9. Double-click the Data Flow Task to switch to Data Flow tab. Drag and drop an OLE DB Source onto the Data Flow tab. Double-click OLE DB Source to view the OLE DB Source Editor.
10. We first need to select one of the connection managers we created earlier. From the OLE DB connection manager drop-down list, select the connection manager you created to the "Test" database. On my system, the name of the connection manager is "LocalHost.Test".
11. Select SQL command from variable from Data access mode
12. Select User:: TempName from Variable name
13. The OLE DB Source Editor should now look similar to the one shown in below image

66

14. Now we need to add a Flat File Destination component. Map the source and destination as shown below

77

15. From the Toolbox, Double-click or drag the 'Execute SQL Task' to the Control Flow tab to drop temp table

16. Now Hit F5 will run the application and here the numbers of rows are indicated at the bottom of the each control as shown in the above screen. And finally the results are loaded to flat file destination which looks like below

88


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: