Download files from authenticated web HTTP Connection Manager in SSIS

0
votes

Problem: I am in process of automating the download of files from authenticated web .I tried different alternatives one with HTTP connection Manager and the other using Script task

HTTP Connection Manager
I have included URL, Username and password of the web, but when i try to test the connection, the following error occurs.

Unable to connect to the remote server

Questions:

  1. For URL , does it should point straight to the server or Login page? I tried either ways, but it didn't work
  2. Do I need to specify anything in Domain box and Client certificate?
  3. What is Client Certificate?

Script task

I have followed the link for downloading the files using script task

Download source file from website with SSIS

it throws unknown exception, not sure what it is?

Questions:

Is there a simple solution using Script task?

In either cases , Can you please help me in connecting to authenticated web and download the files and also check if files already exists. I dont have prior experience in using SSIS to download files from web. I have tried different solutions from here, none of them worked.

1
c#httpwebssisssis-2012
Step 1: go into your browser and type in the URL, login, pwd. Does it work? Step 2: Try a command line tool like CURL. Does it work? What line does your script task fail on? – Nick.McDermaid
Yes, the website works when i give my authenticated info directly in browser. I dont have CURL installed in my computer. I need to try. The Script task just throws an exception without specifying where the exception occurs. I did try and catch to get the exception information,but was not successfull – lvars

1 Answers

0
votes

The HTTP connection manager in SSIS uses TLS1.2 in later .Net versions, here is a guide to that:

  1. .NET 4.6 and above. You don’t need to do any additional work to support TLS 1.2, it’s supported by default.

  2. .NET 4.5. TLS 1.2 is supported, but it’s not a default protocol. You need to opt-in to use it. The following code will make TLS 1.2 default, make sure to execute it before making a connection to secured resource:

    ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12

  3. .NET 4.0. TLS 1.2 is not supported, but if you have .NET 4.5 (or above) installed on the system then you still can opt in for TLS 1.2 even if your application framework doesn’t support it. The only problem is that SecurityProtocolType in .NET 4.0 doesn’t have an entry for TLS1.2, so we’d have to use a numerical representation of this enum value:

    ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072;

  4. .NET 3.5 or below. TLS 1.2 is not supported (*) and there is no workaround. Upgrade your application to more recent version of the framework.

I found that here: https://blogs.perficient.com/2016/04/28/tsl-1-2-and-net-support/

If the website you are connecting to is using TLS1.0 or TLS1.1 then you will not be able to connect to it in later .Net versions (4.6 or later)