How to submit to a webservice from an SSIS package

jamesmcwhinney · ‎06-11-2015

Background

We have a complex SSIS package running nightly on our 2012 SQL server which pulls from 7 different ERP environments (development, test, production, training) and summarizes information about the thousands of patches installed into a table on the SQL server.

We then use that table as the foundation for our application change management website which allows us to ensure each ERP environment is at the correct patch level, and store various information and documentation relating to each patch (functional design, test results, etc).

With our implementation of ServiceNow we need to transition to using the release management module within service now to house this information.

The closest match appears to be the Features table.

With that in mind, I set out to find a way to get the SSIS package to update an import set table in ServiceNow rather than our internal database.

This way, the entire process of extracting, transforming, and pushing the information to ServiceNow is handled in one spot (the SSIS package).

After almost 2 days trying to figure out how to get this to work, I finally found a way to get it to work. If you know other ways to achieve this it love to hear about them.

Gotchas / Stumbling Points

At first I tried using the "Web Service" task, but I wasn't able to find any good documentation on how to format the input variable containing the rows.

After trying various formats, I gave up on that and moved on to trying a script task.

When writing the c# code to access the web service, ServiceNow points out that you must update the app.config with some configuration changes.

However, when running the code from within an SSIS package, the app.config is ignored.

As a result, you need to include these adjustments in your code by passing the binding and the endpoint to the constructor of the ServiceNowSoapClient.

Also note that the endpoint .do?SOAP is used for this and not the WSDL.

Procedure

1. Create a new scripting task

2. Add the service reference using the WSDL (https://yourinstance/yourtablename.do?WSDL)

3. Create the connection in your code as shown below.

SSIS Script Task

using System.ServiceModel;

public void Main()

try{

//configure the binding

var binding = new BasicHttpBinding();

binding.Security.Mode = BasicHttpSecurityMode.Transport;

binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Basic;

binding.Security.Transport.ProxyCredentialType = HttpProxyCredentialType.Basic;

binding.Security.Transport.Realm = "";

binding.Security.Message.AlgorithmSuite = System.ServiceModel.Security.SecurityAlgorithmSuite.Default;

var endpointAddress = new EndpointAddress("https://yourinstance/yourtablename.do?SOAP");

SNSolutionImportWebService.ServiceNowSoapClient soapClient = new SNSolutionImportWebService.ServiceNowSoapClient(binding, endpointAddress);

soapClient.ClientCredentials.UserName.UserName = "username";

soapClient.ClientCredentials.UserName.Password = "password";

//Clear the table

SNSolutionImportWebService.deleteMultiple DeletionCriteria = new SNSolutionImportWebService.deleteMultiple();

DeletionCriteria.sys_created_by = "username";

//Build Update

SNSolutionImportWebService.insertMultipleRecord[] NewRecords = new SNSolutionImportWebService.insertMultipleRecord[1];

NewRecords[0] = new SNSolutionImportWebService.insertMultipleRecord();

NewRecords[0].u_changename = "test";

//Send Update

soapClient.insertMultiple(NewRecords);

} catch (Exception error) {

Dts.Events.FireError(0, "Error", error.Message + "\r" + error.StackTrace, String.Empty, 0);

}

Dts.TaskResult = (int)ScriptResults.Success;

}

Jace Benson · ‎05-02-2018

I know this is a really old topic, and it sounds like you got this working, did you ever get any feedback on this to improve this integration?