This resource will be reviewed shortly.
If you think this resource contain inappropriate content, please report to webmaster.
1. Create a stored procedure that will create a temporary table and populate with few records.
SET ansi_nulls ON
SET quoted_identifier ON
CREATE PROCEDURE SP_SAMPLEPROCEDURE
FROM (SELECT 'AAA' AS NAME,
100 AS [Value],
'Delhi' AS [Place]
SELECT 'BBB' AS NAME,
200 AS [Value],
'Chennai' AS [Place]
SELECT 'CCC' AS NAME,
300 AS [Value],
'Bangalore' AS [Place]
SELECT 'DDD' AS NAME,
400 AS [Value],
'Mumbai' AS [Place]) AS TT
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.
3. OLE DB Connection Manager
Right click on the connection manager pane and select "OLE DB Connection Manager"
4. Right-click the connection manager and click Properties. Set the property RetainSameConnection on the connection "LocalHost.Test" to the value True.
5. Create a variable "TableName" of string type and assign the value "TempTable".
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
DROP TABLE temptable
CREATE TABLE temptable
INSERT INTO temptable
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
14. Now we need to add a Flat File Destination component. Map the source and destination as shown below
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