一切尽在自动中（二），sqlserver “利用FTP下载然后bcp方式导入数据到数据库”

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-- ALTER  proc getftp 
-- as

SET NOCOUNT ON
SET XACT_ABORT OFF

DECLARE @SQL   VARCHAR(5000)
DECLARE @RESULT   INT
DECLARE @TableName VARCHAR(200)
DECLARE @TodayFileName VARCHAR(200)
DECLARE @FileName  VARCHAR(200)
DECLARE @OldFileName VARCHAR(200)
DECLARE @OldFile  VARCHAR(200)
DECLARE @cmd  VARCHAR(5000)

DECLARE @FTPServerIP  VARCHAR(20)
DECLARE @FTPUser   VARCHAR(20)
DECLARE @FTPPwd   VARCHAR(20)
DECLARE @FTP_Path   VARCHAR(500)
DECLARE @LocalDrive  VARCHAR(4)
DECLARE @LocalFolder VARCHAR(500)
DECLARE @LocalPath  VARCHAR(500)
DECLARE @DBServerIP  VARCHAR(20)
DECLARE @DBDataBase  VARCHAR(20)
DECLARE @DBUser   VARCHAR(20)
DECLARE @DBPwd   VARCHAR(20)

DECLARE @LogName  VARCHAR(200) --プログラム確認用
DECLARE @DCLogName VARCHAR(200) --長期保存用
DECLARE @LogText  VARCHAR(200)
DECLARE @LogMessage VARCHAR(200)

DECLARE @Today  VARCHAR(30)
DECLARE @Day3Ago  VARCHAR(30)

---Set

SET @Today   =CONVERT(VARCHAR(10),DATEADD(D,0,GETDATE()),112)--CONVERT(VARCHAR(10),GETDATE(),112)
SET @Day3Ago  =CONVERT(VARCHAR(10),DATEADD(D,-4,GETDATE()),112)

SET @LogName  = 'AppMsg.txt'   --'DC_Log_'   + @Today  + '.txt'
SET @DCLogName = 'DC_System_Log.txt'

SET @TableName = 'ShipData_'
SET @TodayFileName   = @TableName  + @Today  + '.cab'
SET @FileName  = @TableName  + @Today  + '.csv'
SET @OldFileName = @TableName  + @Day3Ago + '.cab'
SET @OldFile  = @TableName  + @Day3Ago + '.*'
--

SET @FTPServerIP = '172.20.2.44'   -- 変える場合修正必要
SET @FTPUser  = 'ftpmanage'   -- 変える場合修正必要
SET @FTPPwd  = 'qingdaoftp'   -- 変える場合修正必要
SET @FTP_Path = 'sfa'   -- 変える場合修正必要

SET @DBServerIP = 'taodev063'   -- 変える場合修正必要
SET @DBDataBase = 'mysql'   -- 変える場合修正必要
SET @DBUser  = 'sa'    -- 変える場合修正必要
SET @DBPwd  = ''    -- 変える場合修正必要

SET @LocalDrive  = 'D:'    -- 変える場合修正必要
SET @LocalFolder  = 'ftptest'  -- 変える場合修正必要     
SET @LocalPath  = @LocalDrive + '/' + @LocalFolder + '/'    
IF @LocalFolder  = ''  SET @LocalPath = @LocalDrive + '/'

SET @LogName ='DWH_DB_LOG'

SET  @LogMessage = @TableName + '    ' + 'Begin '
SET @LogMessage = @LogMessage +  '    ' + convert(varchar(20),getdate(),120)
SET @cmd ='@echo ' + @LogMessage + '>>' + @LocalPath + @LogName + '.LOG'
EXEC  MASTER..XP_CMDSHELL @cmd ,NO_OUTPUT

-- FTPの登録をコントロール

SET @cmd = '@echo open ' + @FTPServerIP + '>' + @LocalPath + 'ftpcmd'
EXEC  MASTER..XP_CMDSHELL @cmd ,NO_OUTPUT

SET @cmd = '@echo USER ' + @FTPUser + ' ' + @FTPPwd + '>>' + @LocalPath + 'ftpcmd'
EXEC  MASTER..XP_CMDSHELL @cmd ,NO_OUTPUT

SET @cmd ='@echo cd ..>>' + @LocalPath + 'ftpcmd'
EXEC  MASTER..XP_CMDSHELL @cmd ,NO_OUTPUT

SET @cmd ='@echo cd ' + @FTP_Path + '>>' + @LocalPath + 'ftpcmd'
EXEC  MASTER..XP_CMDSHELL @cmd ,NO_OUTPUT

SET @cmd ='@echo lcd ' + substring(@LocalPath, 1, len(@LocalPath) - 1) + '>>' + @LocalPath + 'ftpcmd'
EXEC  MASTER..XP_CMDSHELL @cmd ,NO_OUTPUT

SET @cmd ='@echo get ' + @TodayFileName +'>>' + @LocalPath + 'ftpcmd'
EXEC  MASTER..XP_CMDSHELL @cmd ,NO_OUTPUT

SET @cmd ='@echo del ' + @OldFileName + '>>' + @LocalPath + 'ftpcmd'
EXEC  MASTER..XP_CMDSHELL @cmd ,NO_OUTPUT

SET @cmd ='@echo bye>>' + @LocalPath + 'ftpcmd'
EXEC  MASTER..XP_CMDSHELL @cmd ,NO_OUTPUT

-- ファイル取得、解凍、削除のコントロール

SET @cmd ='@echo ' + @LocalDrive + '>' + @LocalPath + 'cmd.bat'
EXEC  MASTER..XP_CMDSHELL @cmd ,NO_OUTPUT

SET @cmd ='@echo CD /' + @LocalFolder + '>>' + @LocalPath + 'cmd.bat'
EXEC  MASTER..XP_CMDSHELL @cmd ,NO_OUTPUT

SET @cmd = '@echo ftp -n -v -s:ftpcmd>>'+ @LocalPath + 'cmd.bat'  -->>→>
EXEC  MASTER..XP_CMDSHELL @cmd ,NO_OUTPUT

SET @cmd = '@echo EXPAND -R ' + @TodayFileName +'>>' + @LocalPath + 'cmd.bat'
EXEC  MASTER..XP_CMDSHELL @cmd ,NO_OUTPUT

 

SET @cmd = '@echo exit>>' + @LocalPath + 'cmd.bat'
EXEC  MASTER..XP_CMDSHELL @cmd ,NO_OUTPUT

SET @cmd = @LocalPath + 'cmd.bat'
EXEC  @RESULT =  MASTER..XP_CMDSHELL @cmd   ,NO_OUTPUT

 

IF @RESULT <> 0
BEGIN
  SET @LogMessage = @TableName + '    ' + 'ＦＴＰから取得/解凍時,エラー起こる.或は中国側まだ送信です'
  SET @LogMessage = @LogMessage +  '    ' + convert(varchar(20),getdate(),120)

  GOTO FTP_SendLog
END

Exec( 'Truncate Table Temp' )
  
  SET @cmd =  'BCP '+@DBDataBase+'.dbo.'+@TableName + 'Temp' + ' IN ' + @LocalPath + @FileName + ' -c -S' + @DBServerIP + ' -U' + @DBUser + ' -P' + @DBPwd
Print @cmd  
  EXEC @RESULT = MASTER..XP_CMDSHELL @cmd
 

 

 

 

 

 

 

FTP_SendLog:

-- SELECT @LogMessage AS Result

SET @cmd ='@echo ' + @LogMessage + '>>' + @LocalPath + @LogName + '.LOG'
EXEC  MASTER..XP_CMDSHELL @cmd ,NO_OUTPUT

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO