brickpack (7/12/2013)
I actually need to automatically generate this file on a daily basis and upload it to an FTP site. I'm hoping that I can accomplish this whole process using SSIS. Do I need to get C# (or other) involved in this or can I create the whole XML document in T-SQL...?
The code below posts to a file handler page, but it should be just as easy to post to an FTP location. This is the main procedure I use to make literally hundreds of posts every day to to a major vendor.
/*
Must run this code on the server to activate the API
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ole Automation Procedures', 1
RECONFIGURE
Then, give EXECUTE permission to the following system stored procedures
sp_OACreate
sp_OAMethod
sp_OAGetProperty
sp_OADestroy
*/
CREATE PROCEDURE dbo.HTTP_POST_ByGUID
@uMember_id UNIQUEIDENTIFIER
,@sDescription VARCHAR(255)
,@sRequestURL VARCHAR(1000)
,@sXML VARCHAR(4000)
AS
BEGIN
/*
EXEC dbo.HTTP_POST_ByGUID
'7C4551F0-4A4C-4FD9-B996-000001815FEF'
,'Title for the transaction here'
,'https://www.someURL/request.ashx'
, '[....raw varchar XML string here...]'
*/
SET NOCOUNT ON
DECLARE
@iPointer INT
,@sResponseText VARCHAR(4000)
,@iStatus INT
,@sStatusText VARCHAR(4000)
,@iCheckSource INT
,@sSource VARCHAR(4000)
,@xmlPostValue VARCHAR(4000)
/* Build the XML string */
SET @sSource =
'<?xml version="1.0" encoding="UTF-8"?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:cp="http://www.cpscreen.com/schemas" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">'
+'<soapenv:Body>'
+'<BackgroundReports xmlns="http://www.cpscreen.com/schemas">'
+@sXML
+'</BackgroundReports></soapenv:Body></soapenv:Envelope>'
SET @xmlPostValue = @sSource
SET @xmlPostValue = 'xml='+@xmlPostValue
SET @iCheckSource = LEN(@xmlPostValue)
IF @iCheckSource > 4000
BEGIN
SELECT
'XML source post must be less than 4000 chars (='+CAST(@iCheckSource AS VARCHAR(10))+')' AS TooManyCharacters
,@xmlPostValue AS [XMLPostValue]
,@sDescription AS [Description]
RETURN
END
EXEC sp_OACreate
'MSXML2.ServerXMLHTTP'
,@iPointer OUTPUT
--Check to see if errors where created.
EXEC sp_OAGetErrorInfo
@iPointer
,@sSource OUT
,@sDescription OUT
IF @sSource IS NOT NULL
BEGIN
SELECT
'Error While Creating HTTP Object' AS SourceError
,@sSource AS [Source]
,@sDescription AS [Description]
RETURN
END
-- Open a connection to the URL.
EXEC sp_OAMethod
@iPointer
,'OPEN'
,NULL
,'POST'
,@sRequestURL
EXEC sp_OAGetErrorInfo
@iPointer
,@sSource OUT
,@sDescription OUT
IF @sSource IS NOT NULL
BEGIN
SELECT
'Error While opening connection' AS ConnectionError
,@sSource AS Source
,@sDescription AS [Description]
RETURN
END
-- Send the request.
EXEC sp_OAMethod
@iPointer
,'send'
,NULL
,@xmlPostValue
EXEC sp_OAGetErrorInfo
@iPointer
,@sSource OUT
,@sDescription OUT
IF @sSource IS NOT NULL
BEGIN
SELECT
'Error While sending data' AS SendError
,@sSource AS Source
,@sDescription AS [Description]
RETURN
END
-- Send the request.
EXEC sp_OAMethod
@iPointer
,'responseText'
,@sResponseText OUTPUT
EXEC sp_OAMethod
@iPointer
,'Status'
,@iStatus OUTPUT
EXEC sp_OAMethod
@iPointer
,'StatusText'
,@sStatusText OUTPUT
EXEC sp_OADestroy
@iPointer
WAITFOR DELAY '00:00:03'
SELECT
@uMember_id AS uMember_id
,@iStatus AS StatusCode
,@sStatusText AS StatusText
,@sResponseText AS ResponseText
END