SSIS 脚本任务(Script Task)实战
最近在忙一个酒店预订系统项目，已集成API XML，但是提交酒店订单到API之后，订单的状态要实行与API同步。因为可能下一分钟，API的订单状态可能已经确认，但本地订单状态还是 处理当中的。
当客人打开某个订单详细信息的时候，再去Call 一次API的状态。这是既传统，又笨的方法。
但是问题 接踵而至
1. 订单处理既然不及时，又不准时。
2. API提供商不给单条记录去同步状态。
等等。
详细解决方案：
用SSIS 设计 脚本任务, 再部署SSIS包任务，每2分钟Call一次API。
步骤：
1.生成Post XML代码
T-SQL CODE
ALTER proc [dbo].[BookingXML] as declare @XmlOutput xml declare @a nvarchar(max) set @XmlOutput = ( select distinct a.APICode as hmcref from dbo.HotelBooking a left join dbo.sys_Procduct b on a.FID=b.FID where isnull(a.APICode,'')<>'' and isnull(a.APIStatus,'') in ('IC','CA','OR') --and a.BDT>=Getdate() and b.Del=0 FOR XML Path(''), ROOT('hmcreflist'), ELEMENTS) if cast(@XmlOutput as nvarchar(max))<>'' begin set @a = '<request><company>aa</company><id>bb</id><pass>cc</pass><lang>SIM</lang>{@Str}</request>' select cast(replace(@a, '{@Str}', cast(@XmlOutput as nvarchar(max))) as xml) end else begin select '' end
XML CODE
<request> <company>aa</company> <id>bb</id> <pass>cc</pass> <lang>SIM</lang> <hmcreflist> <hmcref>W2049850</hmcref> <hmcref>W2049856</hmcref> <hmcref>W2050473</hmcref> <hmcref>W2050522</hmcref> <hmcref>W2050593</hmcref> <hmcref>W2050594</hmcref> </hmcreflist> </request>
2.设计SSIS包
设置ResultSet XML
结果集 变量名称为 user::XMLOutput 结果名称为0
脚本任务
PrecompileScriptIntoBinaryCode 要设为False, 否则提示 “IDE未成生成二进制的错误”
ReadWriteVariables 设为sql 任务的ResultSet 的变量 XMLOutput
重点来了
点击 “设计脚本”，使用HttpWebRequest用post发送请求，再用HtppWebResponse返回XML，再用根据订单号update 状态。
VB.net读取sql server ResultSet变量
Dts.Variables("XMLOutput")
脚本任务链接Sql server数据库, 原先在 连接管理设置有 AServ 名称连接器，有脚本任务直接调用就行。
Dts.Connections.Item("AServ").AcquireConnection(Nothing) Dim conn As New SqlClient.SqlConnection(Dts.Connections.Item("AServ").ConnectionString)
注:用 IO.StreamWriter 生成操作和返回的日志，方便以后维护。
请把全局和脚体任务的MaximumErrorCount调大一点，这样发生错误之后，会继续执行任务。
完整的VB.net如下：
其中 Admin_UpdateBookingStatus 自定义订单状态处理储存过程。
Imports System Imports System.IO Imports System.Net Imports System.Data Imports System.Text Imports System.Math Imports System.Xml Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main() ' ' Add your code here ' 'Dts.TaskResult = Dts.Results.Success Dim XMLString As String = " " If Dts.Variables("XMLOutput").Value.ToString <> "" Then XMLString = Dts.Variables("XMLOutput").Value.ToString.Replace("<ROOT>", "").Replace("</ROOT>", "") XMLString = "<?xml version=""1.0"" encoding=""UTF-8"" ?>" + XMLString Dim request As WebRequest = WebRequest.Create("http://www.abc.com/api/ddd.php") ' Set the Method property of the request to POST. request.Method = "POST" ' Create POST data and convert it to a byte array. Dim postData As String = XMLString Dim byteArray As Byte() = Encoding.UTF8.GetBytes(postData) ' Set the ContentType property of the WebRequest. request.ContentType = "application/soap+xml; charset=utf-8" ' Set the ContentLength property of the WebRequest. request.ContentLength = byteArray.Length 'Get the request stream. Dim dataStream As Stream = request.GetRequestStream() ' Write the data to the request stream. dataStream.Write(byteArray, 0, byteArray.Length) ' Close the Stream object. dataStream.Close() ' Get the response. Dim response As WebResponse = request.GetResponse() ' Get the stream containing content returned by the server. dataStream = response.GetResponseStream() ' Open the stream using a StreamReader for easy access. Dim reader As New StreamReader(dataStream) Dim xmlDoc As New XmlDocument() xmlDoc.LoadXml(reader.ReadToEnd()) Dim HMCREF As XmlNodeList Dim STATUSCODE As XmlNodeList HMCREF = xmlDoc.SelectNodes("//HMCREF") STATUSCODE = xmlDoc.SelectNodes("//STATUSCODE") ' Read the content. Dim BNO As String = "" Dim Status As String = "" Dim i As Integer Dim bstr As String = "" bstr = "E:\\log\\bookingstatus\\log-" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".txt" If HMCREF.Count = STATUSCODE.Count Then For i = 0 To HMCREF.Count - 1 If STATUSCODE.Item(i).InnerText() <> "" And HMCREF.Item(i).InnerText() <> "" Then If BNO <> "" Then BNO = BNO + "," + HMCREF.Item(i).InnerText() Else BNO = HMCREF.Item(i).InnerText() End If If Status <> "" Then Status = Status + "," + STATUSCODE.Item(i).InnerText() Else Status = STATUSCODE.Item(i).InnerText() End If End If Next Else GenerateXmlFile(bstr, "'&XML=" + xmlDoc.InnerXml) End If If BNO <> "" And Status <> "" Then Dts.Connections.Item("AServ").AcquireConnection(Nothing) Dim conn As New SqlClient.SqlConnection(Dts.Connections.Item("AServ").ConnectionString) Dim cmd As New SqlClient.SqlCommand conn.Open() cmd.Connection = conn cmd.CommandTimeout = 300 cmd.CommandText = " Admin_UpdateBookingStatus '" + BNO + "', '" + Status + "' " cmd.ExecuteNonQuery() conn.Close() GenerateXmlFile(bstr, "SQL = Admin_UpdateBookingStatus '" + BNO + "', '" + Status + "'&XML=" + xmlDoc.InnerXml) End If dataStream.Close() response.Close() End If End Sub Public Sub GenerateXmlFile(ByVal filePath As String, ByVal fileContents As String) Dim objStreamWriter As IO.StreamWriter Try objStreamWriter = New IO.StreamWriter(filePath) objStreamWriter.Write(fileContents) objStreamWriter.Close() Catch Excep As Exception MsgBox(Excep.Message) End Try Dts.TaskResult = Dts.Results.Success End Sub End Class
