API access using SSIS
Question asked by sam adam - 9/6/2016 at 11:55 AM
I am trying to use the Web Service Task in SSIS to access the TopFilesByIP method. I am unable to do so. Look at the SSIS error below. According to the error it seems like AuthorizationCode is not set but that is not the case. I added that in the "Input" field on WebServiceTask. 
Error: 0xC002F304 at Web Service Task, Web Service Task: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution. The error is: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Exception: SSWeb.AuthorizationCode in config\AppConfig.xml file must be set and must be >= 10 characters long in order to use this web service.
   at SSWeb.Services.Query.ExecuteQuery(String authUserName, String authPassword, String authorizationCode, String queryName, String[] keyValuePairs)
   --- End of inner exception stack trace ---.
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection)
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
Additional Questions:
Can I make an http request like

1 Reply

Reply to Thread
Grady Werner Replied
Employee Post
To use that API, a variable that must be set in the web.config file in the MRS/App_Data/Config/AppConfig.xml.  Do the following:
  1. Stop the application pool that stats runs under
  2. Back up the file just in case
  3. Open the file with notepad
  4. Find the line that looks like this:
    <WebServiceAuthorizationCode />
  5. Replace it with a value of your choosing (a GUID is a good choice):
  6. Save the file
  7. Start the application pool
  8. Use that same value in your calls
Queries are expensive in terms of CPU, so this a way to manually enable that API and protect it at the same time.
I hope this helps.
Grady Werner
SmarterTools Inc.

Reply to Thread