RiCo技術農場

使用非同步基本可以改善效能，

目前舊系統有個麻煩問題，其他系統呼叫舊系統要新增檔案，

如果遇到檔案很大常常會timeout，

新系統目前我規畫使用非同步來處理大檔案移動會是比較好的選擇，

主要我先區分交易區內和外邏輯，

舊系統'相關SP都有不好習慣，就是包交易應該排除非必要操作、運算..等，

簡單說就是縮短交易時間，再來就是我不使用Service Broker 或 xp_cmdshell來執行SQL Server非同步，

我將利用SQL Agent Job特性來達到相同需求，簡單紀錄一下。

 

檔案新增處理邏輯假設如下

0.取號

1.新增檔案編號資料 到 檔案主檔

2.新增案件編號資料 到 案件主檔

3.rename 上傳檔案名稱並移動到File Server

4.返回檔案編號結束

 

交易區內 =1、2、4

交易區外=0、3

 

 

--提供非同步新增檔案供其他AP介接

create proc usp_asyncAddFile
(
@src nvarchar(100) 
,@tar nvarchar(100)
,@PackageLocation  nvarchar(2000)
) 
as
--[dbo].[usp_asyncAddFile] 'E:\TDDD\IPZ-382\IPZ-382.wmv','F:\IPZ-382.wmv','E:\myssis2008pck\myMoveFile.dtsx' 
set nocount on ;
set XACT_ABORT ON;

begin try  
    --取檔案編號號
    
    --rename 上傳檔案名稱並移動到File Server 
    exec [dbo].USP_CopyFile @src,@tar,@PackageLocation    
    
    begin  tran A    
    
    --insert into 檔案主檔
    
    --insert into 案件主檔
    
    if (XACT_STATE()) = 1 
    begin
        commit tran A 
        select 'Success. DocId=12345678' as [Result];  --成功:返回檔案編號
    end      
    else
    begin
        rollback tran A
        select 'Fail.' as [Result];--失敗:無檔案編號
    end
    
end try
begin catch
IF @@TRANCOUNT > 0
    rollback tran A;
select 'Error:'+ error_message() as [Result];
end catch

 

--使用SSIS2008 移動檔案

create PROCEDURE [dbo].[USP_CopyFile]
(
@src nvarchar(100) 
,@tar nvarchar(100)
,@PackageLocation  nvarchar(2000)
)
AS
-- [dbo].[[USP_CopyFile]] 'E:\TDDD\IPZ-382\IPZ-382.wmv','F:\IPZ-382.wmv','E:\myssis2008pck\myMoveFile.dtsx'
BEGIN
set nocount on;

--declare @ServerName NVARCHAR(100)
declare @cmd NVARCHAR(4000)
declare @jid UNIQUEIDENTIFIER
declare @jname NVARCHAR(128)
--declare @jobName NVARCHAR(128)

-- Create a unique job name
--SET @ServerName = CONVERT(sysname, SERVERPROPERTY(N'servername'))
set @jname = CAST(NEWID() AS CHAR(36))
--set @jobName = @jname

--for ssis2008 by filesystem
set @cmd = '"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" '
set @cmd = @cmd + '/FILE "'+@PackageLocation+'" '
--set @cmd = @cmd + '/SERVER ' + @ServerName + ' '
set @cmd = @cmd + '/CHECKPOINTING OFF '

-- Specify ssis variable value in the package 
set @cmd = @cmd + '/SET "\Package.Variables[User::SourceFile].Properties[Value]";"' + @src + '" '
set @cmd = @cmd + '/SET "\Package.Variables[User::TargetFile].Properties[Value]";"' + @tar + '" '
--print @cmd

-- Create job
/*
0 - do not delete job
1 - delete when job succeeds
2 - delete when job fails
3 - delete when job completes
*/
EXEC msdb.dbo.sp_add_job
@job_name = @jname,
@enabled = 1,
@category_name = '',
--deletes the job when it is done, regardless of whether or not it was successful
@delete_level = 3,
@job_id = @jid OUTPUT

--Add the job to the Sql Server instance
EXEC msdb.dbo.sp_add_jobserver
@job_id = @jid,
@server_name = '(local)'

--Add the step to the job that invokes the ssis package
EXEC msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name = 'Execute SSIS',
@subsystem = 'CMDEXEC',
@command = @cmd

-- Start job
EXEC msdb.dbo.sp_start_job @job_id = @jid

END

 

執行同步模式(總共花了17秒才可回應)

[dbo].[usp_syncAddFile] 'E:\TDDD\IPZ-382\IPZ-382.wmv','F:\IPZ-382.wmv','E:\myssis2008pck\myMoveFile.dtsx' 

 

 

執行非同步模式(總共不到 1 秒時間即可回應)

[dbo].[usp_asyncAddFile] 'E:\TDDD\IPZ-382\IPZ-382.wmv','F:\IPZ-382.wmv','E:\myssis2008pck\myMoveFile.dtsx'

交易處理應要很短暫。

 

 

另一Session 早已先啟動Agent Job 處理大型檔案移動

 

AgentJob結果可在eventLog查看

ps:我使用@delete_level = 3。

 

 

 

 

 

參考

[SQL SERVER][SSIS]透過SQL Agent Job執行SSIS並傳入變數

Asynchronous T-SQL Execution Without Service Broker