Month: February 2015

Download Files in SSIS with an HTTP or HTTPS connection.

/ Mitchell Pearson / Leave a comment

Recently I wanted to download historical stock information from the internet and store this information into a data warehouse. Using the Task Factory Download File Task I was able to dynamically download thousands of csv files with historical stock information and trends for my data warehouse.

The Download file task allows you to download files over an HTTP connection from inside an SSIS Package and it also has support for HTTPS, I will show you where to enter credentials.

If you want to walk through this example and don’t currently have Task Factory you can download a free 14 day trial here: Free Trial Download

Demo Time:

So in this example I am going to download a CSV File from Yahoo Finance with historical price information for Fed Ex.

  • Create a new package in SSIS and pull the Task Factory Download File Task into the control flow.
  • Now we need to create our HTTP Connection manager.
  • Inside the Download File Task select “Create New HTTP Connection…”

image

  • Creating the connection manager is very easy, especially if you are not using HTTPS.
    • Server URL: (This is the domain that is hosting the file)
    • Use Credentials: (Select this box and proceed with your username and password if credentials are required.
    • Test Connection: (Test Connection before proceeding.)
  • For Server URL I used: Http://finance.yahoo.com
  • Click Ok. Connection manager has now been created successfully.

image

  • Now we need the exact location of the file we want to download.

image

  • Now that we have the exact location of the file we can finish setting up the Download File Task.
  • Paste the file location into the section “Enter Path of File To Download:”
    • Note I am simply specifying a Text Value for this walkthrough. You can use variables and expressions with this task to make it as dynamic as you need.

image

  • The final step in this process is to specify the location where the file will be stored once downloaded. Once again I am using “Text Value”.
  • “Enter File Path For Local File:” Enter the specific file location. (Screenshot)

image

Now execute the package and there you go!

image

Thanks for Looking.

Advertisement

SQL Tips! How to edit the query for Edit TOP 200 Rows in Management Studio (SSMS)

/ Mitchell Pearson / 3 Comments

It has been some time since I have written one of my SQL Tip blogs. I like to use the “Edit Top 200” rows functionality provided by SSMS when making quick but different updates across like records in SQL Server. Did you know you can edit the top 200 rows query and get the records you’re interested in updating?  We can do this in just a few easy steps.

First open up the “Edit Top 200 Rows” window in SSMS for the table you want to edit.

image

Second, Right click at the top left of the table and from the drop down list select Pane > SQL.

image

Next, now we see a SQL statement that we can edit. Here I simply added the where clause WHERE MakeFlag = ‘TRUE’

image

Once the SQL has been updated it needs to be executed. Right click on the top left corner of the table again. This time select “Execute SQL”.

image

Below you can see the new result set available now for editing!

image

As always thanks for looking.

SSIS Email Connection Information for SMTP, IMAP, and POP3 connections.

/ Mitchell Pearson / Leave a comment

Quite often when performing a demo, teaching, or building SSIS Packages I am using either SMTP, IMAP or POP3 connection information. Each time I have to find my own local document that has this information stored or I have to search the web, so I’ve decided to post the information here for my own convenience and perhaps yours as well!

If you have some providers you would like to add to this just email me at mpearson@pragmaticworks.com Thanks!

Note: Task Factory by Pragmatic Works offers three separate email tasks for use in SSIS. If you are interested you can learn more about Task Factory here: http://pragmaticworks.com/Products/Task-Factory

Yahoo Configuration Settings

SMTP

  • Mail Server:     smtp.yahoo.mail.com
  • Port:                465
  • Encryption:      SSL

IMAP

  • Mail Server:    Imap.mail.yahoo.com
  • Port:               993
  • TLS/SSL:       Yes

POP3

  • Mail Server:     pop.mail.yahoo.com
  • Port:                995
  • Encryption:     SSL

Gmail Configuration Settings

SMTP

  • Mail Server:      smtp.gmail.com
  • Port:                 587
  • Encryption:       TSL

Office 365 Configuration Settings

SMTP

  • Server name:     smtp.office365.com
  • Port:                  587
  • Encryption:        TLS

IMAP

  • Server name:    outlook.office365.com
  • Port:                 993
  • Encryption:      SSL

POP3

  • Server name:   outlook.office365.com
  • Port:                995
  • Encryption:      SSL 

Build notification framework to send SMS Text messages and Emails in SSIS!

/ Mitchell Pearson / Leave a comment

Of the over 40 components in Task Factory the TF Advanced Email and SMS Task is one of my favorites. In this blog I am going to discuss the rich features this task offers and provide a walkthrough of setting this task up to send an email or SMS text message upon package failure.

If you like this blog and you want to try out a free trial of Task Factory you can find it here: http://pragmaticworks.com/Products/Task-Factory/TrialDownload

Feature Highlights

  • Supports Email and SMS messages.
  • Can send emails with attachments.
  • Inbuilt HTML editor for HTML email.
  • Allows use of variables as placeholders in the Email Body so variables can be automatically replaced when an email is sent!
  • Expressions can be set on properties to make the package dynamic.

Note: We will be using SMTP connection information to set up our email connection manager. Generally a basic Bing/Google search will return the connection information required. For example Yahoo SMTP Connection info or Gmail SMTP Connection info.

Ok let’s get started with our walkthrough.

  1. Please open up a new SSIS Package.
  2. Next click on the Event Handlers tab at the top.
    1. Don’t change the executable, by default it is set at the package level.
    2. Also leave OnError for the Event Handler.

image

  1. Next click on the hyperlink “Click here to create an ‘OnError’ event handler for executable ‘<Executable Name>’.

image

  1. Now pull in the Task Factory Advanced Email and SMS task.
  2. Next we will set up a new SMTP Connection Manager.
    1. Select the drop down box next to SMTP Connection and then select “Create New Email Connection”.

image

  1. On the general tab fill out the following information:
    • Protocol Type: (Must be SMTP)
    • Mail Server: (The Mail Server will differ by mail provider. I’m using Office 365 settings.)
    • User Name:
    • Password
  2. On the advanced tab fill out the following:
    • Server Port: (Once again changes per mail provider)
    • Type of encrypted connection: TLS, SSL or NONE
    • Timeout in Seconds: (Default is 60)
  3. Finally test connection to validate and click ok to close.

image

image

image

Setting up SMS Providers and Emails

In this section I will demo how easy it is to set up the component to send either an SMS or Email message. Note all of this can be set up dynamically using variables and expressions!

  1. Select the dropdown for “To:” and select either email or SMS. Here I selected email and then manually entered the email address.
    • Please note you can send emails to multiple recipients by separating them with a semi colon ;.

image

  1. Ok, next let’s override our email option and select SMS.
    • First enter the phone number.
    • Next select the provider (The “Provider” will be required for SMS messages.)
    • Finally select the add Icon, see screenshot below:

image

Dynamically set the body of the email and subject using expressions:

In this section we are going to use expressions for the body and subject of the email. Note that expressions can be set on virtually every property of this component.

  1. Select the “Expressions” tab found at the bottom left of the TF Advanced Email editor.

image

  1. From the properties window select “Subject” from the drop down.
  2. In the “Expression” box select the Ellipsis button to open the expression editor.
  3. Here is where we can use the SSIS Expression language and variables to make our subject dynamic at run time. (See third Screenshot below)

image

image

image

  1. Once you have completed your expression for the Subject click ok and that will close the expression editor. Now from the properties window select “Message” and once again select the Ellipsis button to open the expression editor.

image

Now when I execute the package if an OnError event happens in the package and only if this event occurs then I will receive an email or text message!

Here is the email I received upon failure of the package, pretty cool!

image

Thanks for looking!

Get list of subfolders in SSIS with SSIS Script Task!

/ Mitchell Pearson / 4 Comments

Recently I needed to get a list of subfolders in SSIS. I also only wanted to bring back a list of subfolders that had the date appended to the end. So let’s walk through how to do this in Script Task using c#.

image

First we need to create two variables in SSIS:

image

Next bring a script task into the control flow and open it up for editing.

  • Select C# for the script language.
  • ReadWriteVariables select objDirectoryList

image

Now select Edit Script

  • Under the section “public void Main()” enter the following code:
    • Of course replace the directory location with your directory. 
Dts.Variables["objDirectoryList"].Value = 
       System.IO.Directory.GetDirectories(@"C:\Blogs\RootFolder\","*20*",AllDirectories);
Dts.TaskResult = (int)ScriptResults.Success;
        }

        public SearchOption AllDirectories { get; set; }

Essentially all I’m doing here is populating the object variable objDirectoryList with all the subdirectories in the folder C:\Blogs\RootFolder\. Notice that I have also added a filter here “*20*”. If you do not wish to have a filter and  you want to bring back all subdirectories then just replace my filter with “*”.

Finally we can now iterate through this object variable in a FELC.

image

Below you can see the two directories that are returned from the script task above.

image

image

Thanks for looking, hope this helps.