Use the SQL Server CLR to Read and Write Text Files

using System;
using System.Data;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
public class ReadWriteFileTips
{
  [SqlFunction]
  public static SqlBoolean WriteTextFile(SqlString text,
                                        SqlString path,
                                        SqlBoolean append)
  {
    // Parameters
    // text: Contains information to be written.
    // path: The complete file path to write to.
    // append: Determines whether data is to be appended to the file.
    // if the file exists and append is false, the file is overwritten.
    // If the file exists and append is true, the data is appended to the file.
    // Otherwise, a new file is created.
    try
    {
      // Check for null input.
      if (!text.IsNull &&
          !path.IsNull &&
          !append.IsNull)
      {
        // Get the directory information for the specified path.
        var dir = Path.GetDirectoryName(path.Value);
        // Determine whether the specified path refers to an existing directory.
        if (!Directory.Exists(dir))
          // Create all the directories in the specified path.
          Directory.CreateDirectory(dir);
        // Initialize a new instance of the StreamWriter class
        // for the specified file on the specified path.
        // If the file exists, it can be either overwritten or appended to.
        // If the file does not exist, create a new file.
        using (var sw = new StreamWriter(path.Value, append.Value))
        {
          // Write specified text followed by a line terminator.
          sw.WriteLine(text);
        }
        // Return true on success.
        return SqlBoolean.True;
      }
      else
        // Return null if any input is null.
        return SqlBoolean.Null;
    }
    catch (Exception ex)
    {
      // Return null on error.
      return SqlBoolean.Null;
    }
  }
  [SqlProcedure]
  public static void ReadTextFile(SqlString path)
  {
    // Parameters
    // path: The complete file path to read from.
    try
    {
      // Check for null input.
      if (!path.IsNull)
      {
        // Initialize a new instance of the StreamReader class for the specified path.
        using (var sr = new StreamReader(path.Value))
        {
          // Create the record and specify the metadata for the column.
          var rec = new SqlDataRecord(
                            new SqlMetaData("Line", SqlDbType.NVarChar, SqlMetaData.Max));
          // Mark the beginning of the result-set.
          SqlContext.Pipe.SendResultsStart(rec);
          // Determine whether the end of the file.
          while (sr.Peek() >= 0)
          {
            // Set value for the column.
            rec.SetString(0, sr.ReadLine());
            // Send the row back to the client.
            SqlContext.Pipe.SendResultsRow(rec);
          }
          // Mark the end of the result-set.
          SqlContext.Pipe.SendResultsEnd();
        }
      }
    }
    catch (Exception ex)
    {
      // Send exception message on error.
      SqlContext.Pipe.Send(ex.Message);
    }
  }
};

/*
HOW TO DEPLOY THE CODE
======================
Configure your SQL Server instance
for CLR (if not already configured)
-----------------------------------
USE master
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
Create your test database or choose one
---------------------------------------
CREATE DATABASE DB_TIPS
GO
Configure your database
-----------------------
USE DB_TIPS
GO
ALTER DATABASE DB_TIPS SET TRUSTWORTHY ON
GO
Save text in article code window to
C:\MSSQLTips\TextFileTips.cs
-----------------------------------
Run the 3 lines below as one in a DOS command window;
this will compile the code to TextFileTips.dll
-----------------------------------------------------
C:\Windows\Microsoft.NET\Framework\v3.5\csc.exe 
 /target:library /out:C:\MSSQLTips\TextFileTips.dll 
 C:\MSSQLTips\TextFileTips.cs
Register the assembly
---------------------
CREATE ASSEMBLY TextFileTips
FROM 'C:\MSSQLTips\TextFileTips.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
Create objects from the assembly
--------------------------------
CREATE FUNCTION [dbo].[WriteTextFile](
@text [nvarchar](4000), 
@path [nvarchar](4000), 
@append [bit])
RETURNS [bit] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [TextFileTips].[ReadWriteFileTips].[WriteTextFile]
GO
CREATE PROCEDURE [dbo].[ReadTextFile]
 @path [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [TextFileTips].[ReadWriteFileTips].[ReadTextFile]
GO
*/

------------------------------------------------------------
--
SELECT dbo.WriteTextFile(
'text1', 'C:\MSSQLTips\Line.txt', 0)
GO
--returns 1
--
SELECT dbo.WriteTextFile(
'text2', 'C:\MSSQLTips\Line.txt', 1)
GO
--returns 1
--
SELECT dbo.WriteTextFile(
'text3', 'C:\MSSQLTips\Line.txt', 1)
GO
--returns 1
--
EXEC ReadTextFile 'C:\MSSQLTips\Line.txt'
GO
--prints
Line
----------------
text1
text2
text3
--
------------------------------------------------------------