|
For a project I was working on, users wanted to be able Trigger exports from SQL to a Mainframe application that used a Fixed Column Width format for the data transfer. Since the Mainframe expects the same file name each time, the users wanted a way to maintain a history of the files that were exported across. I tried to set up the parameters so that it could be reused without having to rewrite it each time.
Originally this started out as an SSIS package, but the users weren't happy with the delay in the export, and I didn't want to schedule the job to run every minute, or set up a "WAIT" as a first step and just keep the job running in memory, so I looked into using the SQL CLR.
This solution uses a CLR Stored Procedure that accepts a Query, FileName and ArchivePath to create the file(s). The Query and FileName are required since that's the point of the procedure, but if your don't need to archive the files, you can pass in an Empty String for the Archive Path. To give the Stored Procedure the most flexibility, I do all of the data formatting in the Query that's passed in. In my current case, I have a View that puts in any padding requirements for the columns. Since the Stored Procedure works with the File System obviously the assembly must be created with External Access or the Trustworthy option of the database must be set.
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub ExportFixedWidthFile(ByVal Query As SqlString, _
ByVal FileName As SqlString, ByVal ArchiveFile As SqlString)
'Open the Stream for writing the File
Dim tw As New StreamWriter(FileName.ToString(), False)
'Use the existing Database Connection
Using c As New SqlConnection("context connection=true")
c.Open()
'Create the Command using the Query and Execute as a Data Reader
Dim cmd As New SqlCommand(Query, c)
Dim dr As SqlDataReader = cmd.ExecuteReader()
'Loop Through the Rows and Columns sending the Data to the Stream
While dr.Read
For i As Integer = 0 To dr.FieldCount - 1
tw.Write(dr.GetString(i))
Next
'Write a New Line following Each Record
tw.WriteLine()
tw.Flush()
End While
dr.Close()
c.Close()
End Using
tw.Close()
tw = Nothing
'If ArchiveFile is passed in, then copy the file to the Archive
If ArchiveFile.ToString <> "" Then
System.IO.File.Copy(FileName.ToString(), ArchiveFile.ToString, True)
End If
End Sub
End Class
Once the assembly is loaded into your database and the Stored Procedure you can call it as easially as this:
DECLARE
@Query VARCHAR(1000),
@FileName VARCHAR(255),
@ArchiveFile VARCHAR(255)
--Export File without an Archive File
SELECT
@Query = 'SELECT * FROM myExportView',
@FileName = 'C:\Temp\TestFile.txt',
@ArchiveFile = ''
EXEC dbo.ExportFixedWidthFile @Query, @FileName, @ArchiveFile
--Export File with an Archive
--(Normally, I add a Date Time to the Archive File Name.)
SELECT
@Query = 'SELECT * FROM myExportView',
@FileName = 'C:\Temp\TestArchive.txt',
@ArchiveFile = 'C:\Temp\Archive\Tested.txt'
EXEC dbo.ExportFixedWidthFile @Query, @FileName, @ArchiveFile
|