I'm trying to create a file using a stored procedure with SSIS.  I've tried to use the Execute SQL Task, but it will not create a file nor output to that file.  I'm using "Full result set", but I don't know how to sent the result to the file.  

Is there another Control Flow Item I need to use?

The reason why I'm using SSIS and not SQL Server Agent is because the file name must contain a timestamp.  I'm using a Script Task to take care of this.



Thanks in advance!

Damon

Have a source set to get the data using the SQL and make a Flat File Connection connected to it.

Here is more to see in this post: https://decipherinfosys.wordpress.com/2008/07/23/ssis-exporting-data-to-a-text-file-using-a-package/

If you create an Excel file connection, you can issue a CREATE TABLE statement (via an Execute SQL Task) and it will create a new excel file for you. The Syntax is slightly different than what you may be used to:

CREATE TABLE `newFile` (
    `text` LongText,
    `date` DateTime,
    `number` Long
)

Hi Damon,

Based on your description, you want to export the data returned by a stored procedure to a text file which file name contains a timestamp.

After testing the issue in my environment, we can use Data Flow Task to achieve your requirement. For more details, please see:

1. Drag a Data Flow Task to Control Flow Task.
   
2. Create an OLE DB Connection Manager that connects to the corresponding server database.
   
3. Create a Flat File Connection Manager that connects to any text file.
   
4. Click the Flat File Connection Manager, then navigate to the Expressions property.
   
5. Click the next to the Expressions property, add a  ConnectionString property with Expression as below (supposing you need the file with name like file_2015-06-29 11_04_09.374000000.txt in C:\Users\user_name\Desktop\SSIS FILES folder):
   "C:\\Users\\user_name\\Desktop\\SSIS FILES\\file_"+ REPLACE((DT_WSTR,50) GETDATE(), ":", "_")  +".txt"
   
6. Drag an OLE DB Source with the OLE DB Connection Manager already create, then change Data access mode to SQL command, type the query that execute the stored procedure in SQL command text.
   
7. Drag a Flat File Destination with the Flat File Connection Manager that connects to the Source component, then click Mapping pane to finish the mapping.
   
8. After executing the Data Flow Task, a file with a timestamp would be created.

Thanks,
Katherine Xiong