Create text file from Stored Procedure output in SSIS with Flat File Destination – SQL Server 2008

This is quite an obvious need in SSIS where you wanted to call Stored Procedure and keep its output in text file. This is one of the common and easy task to do in SQL Server Integration Services.  Let us not talk more and do some action.

Before we move ahead in BIDS, let us create one Stored Procedure which we will be called from SSIS. I will be using table and records from my previous article. Table name is StudDetail. You can create table script from here.

Now, herewith, I am giving the SP created based on StudDetail table.

create proc usp_StudDetail 

as 

begin 

 select StudentID,PassYear,Grades from StudDetail 

end 

GO

Now, Open new project in BIDS and in “Control Flow” tab of your Package.dtsx file drag and drop one “Data Flow” task. Double click to “Data Flow” task to configure it then you will be redirected to “Data Flow” tab of your package.dtsx file.

--Take one “OLEDB Source” task from tool box and drop it to your package.

--double click on OLEDB Source task to configure it.

--From “OLEDB Connection Manager”, set your database connection

--From “Data Access Mode” drop down, select “SQL Command”

--put “exec usp_studdetail” (without double quote) in “SQL Command Text” and click on OK.

Now, you are done with configuring OLEDB Source task and configuration screen should seems like this:

--Now drag “Flat File Destination” task from tool box to your work area.

-- Drag the Green arrow from OLEDB Source to the Flat File Destination task and drop it to create precedence

--double click on “Flat File Destination” to configure it.

--click on “New” button to create “Flat File Connection Manager” if it doesn’t exists and select “Delimited file” radio button and press OK button.

--Now, from the dialog box, you have to set two property, “Connection Manager Name” and “File Name” along with path.

Screen should look something like this:

Ok, Now you are done, just run the package by hitting F5 and you will see file will be created at the path you have specified.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com