There are a lot of articles on the net about how to achieve this, some of them good, some other hard to follow. My problem was pretty simple: scan a folder from new files, import and process them and at the end move them some place else.
There are 2 approaches:
- First is to use FSO and OLE Automation
- Second CLR integration
I decide to implement the second solution because:
- is very hard to work with OLE automation
- if the COM object executes in SQL Server address space, a bug in your code will crash SQL Server
VB.NET code
Imports System.IO
Imports System.Security.Permissions
<IODescriptionAttribute(
"SQLServerFileSystemWatcherDesc")> _
<PermissionSetAttribute(SecurityAction.InheritanceDemand, Name:=
"FullTrust")> _
<PermissionSetAttribute(SecurityAction.LinkDemand, Name:=
"FullTrust")> _
Public Class Import
<PermissionSet(SecurityAction.Demand, Name:=
"FullTrust")> _
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub Arhive(ByVal sourcePath As String, ByVal fname As String, ByVal destPath As String)
File.Move(finalSource & fname, finalDest & fname)
End Sub
End Class
Code is compiled like this from command prompt:
vbc /t:library CAMAImport.vb
SQL Server side
Steps:
1) Enable CLR integration:
EXEC sp_configure @configname = 'clr enabled', @configvalue = 1
RECONFIGURE WITH OVERRIDE
GO
2)Register the assembly (please note PERMISSION_SET = EXTERNAL_ACCESS will never work wih SAFE! - took me an hour to figure out this):
CREATE ASSEMBLY WDMImport FROM 'c:\SQLServer\WDMLog\Import.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
3) Tell database server where to find the object code:
CREATE PROC ImportMoveFile
(@sourcePath NVARCHAR(255), @fname NVARCHAR(255), @destPath NVARCHAR(255) )
AS EXTERNAL NAME WDMImport.Import.Arhive
GO
4) Run it:
EXEC ImportMoveFile 'c:\', 'test.txt', 'c:\Imported\'