Courion Tip – XMLAO and SSIS made easy

I was intimidated by the whole XMLAO thing, but it’s really not that bad once you get some experience with it.

I will flesh this out over the next several days, so this first post will be a little cryptic.

Start by adding an ADO data source connection. I set mine to pass the workflow name, the action, and the user ID several times. Side note – why have SEVERAL ‘unique’ columns in the same table? Seemed redundant to me, so, I make sure that the user id is positively unique in the data source, and then I don’t need an AutGUID, and a record number.

Here’s a list of what I am passing to the scripting object:

Next, add a Scripting object to your SSIS package, and then change it’s type to visual basic – by default, it’s C++.

Add these includes to the top:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.Xml
Imports System.Net
Imports System.Net.Security
Imports System.Security.Cryptography.X509Certificates
Imports System.IO

And now define your variables:

        Dim XMLAOPath As String
        Dim workflow As String

        Dim authGUID As String
        Dim recordID As String
        Dim targetID As String
        Dim action As String

        ServicePointManager.ServerCertificateValidationCallback = New RemoteCertificateValidationCallback(AddressOf ValidateServerCertificate)

        XMLAOPath = CType(Me.ReadOnlyVariables("XMLAOPath").Value, String)

        recordID = Row.RecordID.ToString()
        authGUID = Row.AuthGUID.ToString()
        targetID =
        action =
        workflow =

Add this line of code to actually call the workflow:

            spmlRequest = "<addRequest%20RequestId=""" & authGUID & """><identifier%20type=""RecordID""><id>" & recordID & "</id></identifier></addRequest>"

            request = "ACWorkflow=" & workflow & "&XmlInputDoc=" & spmlRequest
            MsgBox(XMLAOPath & " " & request)

Make sure your inputs and outputs match:

Advertisements

Posted May 14, 2011 by mmdmurphy in Courion Tip, SQL

Tagged with ,

%d bloggers like this: