A client is switching the way they deliver files to us, from emailing them, to a secure FTP. So, I wrote this and set it up as a SQL Agent job to run hourly on Monday, which is the day the sample shows up. The goal is to take the text file and move it to the folder I process these files from, and send an email to a few people letting us know the name of the file and when it moved.
I'm using a Schema.ini file because when I load these files without one, the data becomes garbled. A file with a few dozen columns becomes two columns with a bunch of odd question mark delimiters.
It's running as I want it to, but it fails as a SQL Agent job because I either need to change the owner or my user needs the appropriate privs. My sysadmin is looking into that part.
Here's how my SP looks now, with some anonymity edits:
USE [Sample]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[filemove]
--EXECUTE filemove 'X:\Service\'
@filespec varchar(512)
as
declare @filetbl table (fileinfo varchar(2000))
declare @fileinfoemail varchar(8000)
declare @commandline varchar(8000)
declare @filename varchar(128)
declare @droppath varchar(512)
set @droppath = '\\server\path'
declare @sql varchar(8000)
select @commandline = ''
select @commandline = left('dir "' + @filespec + '" /A-D /B /O-D', 4000)
insert into @filetbl (fileinfo)
exec xp_cmdshell @commandline
--print (@commandline)
delete from @filetbl where fileinfo is null or fileinfo='file not found'
set @filename = (SELECT top 1 fileinfo FROM @filetbl)
if @filename is null
begin
select 'NOPE' as [NOPE]
end
else if @filename is not null
begin
set @sql = 'EXEC master.sys.xp_cmdshell ' + '''MOVE /Y "' + @filespec + @filename + '" "' + @droppath + '\"'''
exec(@sql)
--print (@sql)
set @sql = 'exec master..xp_cmdshell ' + '''echo [' + @filename + '] > ' + @droppath + '\' + 'Schema.ini'''
exec(@sql)
set @sql = 'exec master..xp_cmdshell ' + '''echo Format=TabDelimited >> ' + @droppath + '\' +'Schema.ini'''
exec(@sql)
set @sql = 'exec master..xp_cmdshell ' + '''echo MaxScanRows = 0 >> ' + @droppath + '\' +'Schema.ini'''
exec(@sql)
set @sql = 'exec master..xp_cmdshell ' + '''echo CharacterSet = ANSI >> ' + @droppath + '\' +'Schema.ini'''
exec(@sql)
select @commandline = ''
select @commandline = 'dir "' + @filespec + '" /A-D'
insert into @filetbl (fileinfo)
execute xp_cmdshell @commandline
delete from @filetbl where fileinfo is null or fileinfo = 'file not found' or fileinfo = 'Schema.ini'
set @fileinfoemail = (select top 1 fileinfo from @filetbl where fileinfo like '%txt%')
exec msdb.dbo.sp_send_dbmail
@profile_name = 'Erik',
@recipients = 'erik@email',
@copy_recipients = 'distrogroup@email',
@subject='Service Quality file moved',
@body = @fileinfoemail
end