How to set SSIS Http Connection Manager credentials with config file?

Question

Within an SSIS package I have a Web Service task which uses an Http Connection Manager. I need to set credentials for the connection so that it will authenticate with the remote web service. I can set these at design time, however I would like these credentials to be provided to the package using an XML package configuration file.

The problem is that the credentials for the connection do not appear among the properties that can be configured. I have tried setting this programmatically, but I cannot seem to access the credentials that way either:

Dim webConnMgr As ConnectionManager = Dts.Connections("My web service")
Dim webConn As Wrapper.ConnectionManagerHttpClass = _
    CType(webConnMgr.InnerObject, Wrapper.ConnectionManagerHttpClass)
webConn.?

Any ideas?

Answer 1

Sounds like you need to assign the values in the configuration file to a variable in your package. You can then access the variable from your script task (Dts.Variables("VariableName").Value.ToString if I remember rightly!).

Answer 2

I have found no way to specify the credentials using a configuration file, or programmatically, but a workaround is to simply save all the credentials (as set in the designer) in the package, and then have different copies of the package for each different configuration. This is done by setting the ProtectionLevel of the package to, for example, EncryptSensitiveWithPassword.

This is obviously not ideal.

Answer 3

SQL 2005 has very limited member properties for the ConnectionManagerHttpClass. Thankfully it has been addressed in SQL 2008 :)

MSDN - SQL 2005 ConnectionManagerHttpClass properties.

VS.

MSDN - SQL 2008 ConnectionManagerHttpClass properties.

According to this MSDN link, the public class [ConnectionManagerHttpClass] implements IDTSConnectionManager90, ConnectionManagerHttp.

Since we will assume you are using SQL 2005 (according to your Stackoverflow tags), you may want to check out the IDTSConnectionManager90.ConnectionString Property on MSDN as well.

Also check out the ConnectionManagerHttpClass.ConnectionString Property on MSDN, which implements IDTSConnectionManager90.ConnectionString and is overridable.

This should point you into the right direction. May the farce be with you.

Answer 4

SQL2008 SSIS exposes these properties correctly; unfortunately 2005 is the environment I have to work with. I have worked around the problem by developing a custom component which allows the properties to be set.