SSIS Script Task, using C#, to Download Files w/ variable Security Protocols

I have a SSIS package that I use to download files from various websites. I began by using a C# script published by Tom Archer - MSFT on https://www.codeguru.com/columns/dotnettips/article.php/c7005/Downloading-Files-with-the-WebRequest-and-WebResponse-Classes.htm. I had to modify this script because certain security protocols were required for some sites but not others. I've included my modifications below so that Security Protocols can be controlled based on variables I pass to the Script Task. My resulting code is as follows:

public static int DownloadFile(String remoteFilename,
                                String localFilename,
                                String securityProtocolType)
{
    //Make sure to use the following namespaces:
    //using System.Net;
    //using System.IO;
    
    // Function will return the number of bytes processed
    // to the caller. Initialize to 0 here.
    int bytesProcessed = 0;

    // Assign values to these objects here so that they can
    // be referenced in the finally block
    Stream remoteStream = null;
    Stream localStream = null;
    WebResponse response = null;

        if (securityProtocolType == "Tls12") //Added if statement since some downloads will fail with Tls12 selected
        { ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072; } // 3072 = TLS 1.2 Security Protocol

    // Use a try/catch/finally block as both the WebRequest and Stream
    // classes throw exceptions upon error
    try
    {
        // Create a request for the specified remote file name
        WebRequest request = WebRequest.Create(remoteFilename);
        if (request != null)
        {
            // Send the request to the server and retrieve the
            // WebResponse object 
            response = request.GetResponse();
            if (response != null)
            {
                // Once the WebResponse object has been retrieved,
                // get the stream object associated with the response's data
                remoteStream = response.GetResponseStream();

                // Create the local file
                localStream = File.Create(localFilename);

                // Allocate a 1k buffer
                byte[] buffer = new byte[1024];
                int bytesRead;

                // Simple do/while loop to read from stream until
                // no bytes are returned
                do
                {
                    // Read data (up to 1k) from the stream
                    bytesRead = remoteStream.Read(buffer, 0, buffer.Length);

                    // Write the data to the local file
                    localStream.Write(buffer, 0, bytesRead);

                    // Increment total bytes processed
                    bytesProcessed += bytesRead;
                } while (bytesRead > 0);
            }
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
    }
    finally
    {
        // Close the response and streams objects here 
        // to make sure they're closed even if an exception
        // is thrown at some point
        if (response != null) response.Close();
        if (remoteStream != null) remoteStream.Close();
        if (localStream != null) localStream.Close();
    }

    // Return total bytes processed to caller.
    return bytesProcessed;
}

/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
/// </summary>
public void Main()
{
    int read110 = DownloadFile(Dts.Variables["User::rSourceFullName"].Value.ToString(), Dts.Variables["User::rDestinationFullName"].Value.ToString(), Dts.Variables["User::rSecurityProtocolType"].Value.ToString());

    //if no bytes are processed return error
    if (read110 > 0) { Dts.TaskResult = (int)ScriptResults.Success; }
    else { Dts.TaskResult = (int)ScriptResults.Failure; }
   
}

PASS Summit Tips for First Timers

I volunteered for the pass buddy program and shared a list of tips with them for attending PASS Summit 2017.


Tip #1- if your staying near the event I would not suggest a rental car. You can catch the shuttle to/from the airport & Uber to other places you might want to go.

Tip #2- Sign up for after parties before you head down. This is a good time to meet other people.

Tip #3- Have a flexible schedule. Try to skip one time slot (maybe a slot where nothing seems overly interesting to you) to visit the vendor area (it’ll be less crowded). Also a session you start to attend may not be exactly what you expected, have a back up session you can go to.

Tip #4- Skip another time slot and talk to the Microsoft team that is on sight, they can give you great job specific advice on a project you may be working on or planning to implement.

Tip #5- Don’t bring a laptop, unless needed. Do you really want to lug it around all day? All sessions are recorded so you can reference them (as long as you purchased the recordings) *note it takes a bit of time (1-2 months) to receive the recordings*

Tip #6- Make sure your luggage has room for all the swag you end up picking up.

CheckSum is pretty damn accurate

I am at a SSIS Masters course with Jorge Segarra and Brian Knight (Pragmatic Works). An attendee brought up an issue about checksum not being 100% accurate, which we all acknowledge; however, another attendee said it is only 99% accurate when checking for differences. I know it's not even close to 1% inaccurate so I wrote a quick script to attempt find an inaccurate checksum value via brute force. I ran the script below for 15 minutes, checking over a quarter of a billion random values in which I found 0 invalid matches. If someone wants to run this longer and change the bigint to a numeric, let me know how many attempts until you find a record.

Summary: checksum is pretty damn accurate (not 100%, but pretty close).


DECLARE @value1a VARCHAR(36)
DECLARE @value2a VARCHAR(36)
DECLARE @value1b VARCHAR(36)
DECLARE @value2b VARCHAR(36)

DECLARE @attempt bigint
SET @attempt = 0


   SET @value1a = NEWID()
   SET @value2a = NEWID()
   SET @value1b = NEWID()
   SET @value2b = NEWID()

WHILE checksum(@value1a, @value2a) <> checksum(@value1b, @value2b)
BEGIN
   SET @attempt = @attempt + 1
   SET @value1a = NEWID()
   SET @value2a = NEWID()
   SET @value1b = NEWID()
   SET @value2b = NEWID()
   IF @attempt%100000 = 0 PRINT @attempt
END

SELECT @attempt
SELECT @value1a value1a, @value2a value2a, checksum(@value1a, @value2a) chk_a
SELECT @value1b value2a, @value2b value2b, checksum(@value1b, @value2b) chk_b



Creating a simple T-SQL query

For people starting to learn how to write T-SQL queries the first thing you need to know is how to perform a simple query. A query is a way to get information back from a table in a database.

A table should represent a single entity, for example you may have a person table or a product table. We will focus on the person table for this example. A person may have multiple attributes (first name, last name, birth date, sex). These attributes are defined as columns/fields in a table (similar to a row header in a spreadsheet).



The first part of the query is the select statement. You will use the select statement to define what information you want to see pulled from the table. Each field needs to be separated by a comma. If we are only interested in seeing the first name and last name we would write the following.

select first_name, last_name

The statement above will fail to execute because the query is not complete. We have not defined where we wanted to get this information from. For this example table is named "person". In order to declare where we want to get the data from, we use the "from" statement after the list of columns as follows:

select first_name, last_name
from person

That is pretty much the simplest a query will get.