update wsdl file in web service task in SSIS dynamically

Question

i am trying to develop an SSIS package with web service task, i have specified the service url http://localhost/myservice.asmx?wsdl in the connection manager and configured the location of wsdl using expressions

but when i deploy the package to a prod environment, SSIS package will be on a DB server and web service will be hosted on a different server, which means i need to change the service url in the dtsConfig file.

but the problem i am facing is when i change the connection string url in dtsConfif file it will not update the wsdl file, and it points to old url and package fails with 404 error

Answer 1

If I understand, you are changing a value in the dtsConfig, but your SSIS package is not picking up on the new value. Are you sure it is looking at the updated dtsConfig file when it runs?

You should have, for example, a SQL Server Agent Job that is executing your SSSI package, and it should be pointing to the updated configuration file; or else, the Job should override the configured values. You can try different ways to set the url string to the desired destination.