Get latest File from a folder in SSIS

Sample code do the following:

• Reads file from a Folder
• Identifies the latest file
• Rename the file and assign it to a variable

     try
            {
                var directory = new DirectoryInfo(Dts.Variables["User::SourceFolderPath"].Value.ToString());

                FileInfo[] files = directory.GetFiles();
                DateTime fileModifiedDate = DateTime.MinValue;

                foreach (FileInfo file in files)
                {
                    if (file.LastWriteTime > fileModifiedDate)
                    {
                        fileModifiedDate = file.LastWriteTime;
                        Dts.Variables["User::SourceFileName"].Value = file.ToString();
                    }

                }

                string oldFilePath = Dts.Variables["User::SourceFolderPath"].Value.ToString() + "\\" + Dts.Variables["User::SourceFileName"].Value.ToString(); // Full path of old file
                string newFilePath = Dts.Variables["User::SourceFolderPath"].Value.ToString() + "\\" + fileModifiedDate.Date.ToString("MM.dd.yyyy") + "_"
                                                                                               + Dts.Variables["User::SourceFileName"].Value.ToString(); // Full path of new file
                                  if
(Dts.Variables["User::SourceFileName"].Value.ToString().StartsWith(fileModifiedDate.Date.ToString("MM.dd.yyyy")) == false)
                    {
                        File.Move(oldFilePath, newFilePath);    //Raname file, if it donot start with latest fileCreateDate
                        Dts.Variables["User::SourceFileName"].Value = fileModifiedDate.Date.ToString("MM.dd.yyyy") + "_" + Dts.Variables["User::SourceFileName"].Value.ToString();
                    }
               
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception ex)
            {  
                //Unlock variables
                if (Dts.Variables.Locked == true)
                {
                    Dts.Variables.Unlock();
                }
                //An error occurred.
                Dts.Events.FireError(0, "Error occured", ex.Message, String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }

Download Email Attachment using SSIS Script Task

Below are the 2 different methods that can be used to download an Email Attachment using SSIS Script Task.

Method1: Using Exchange Serivce API

• If there is no exchange service API installed, download and install it from http://www.microsoft.com/en-in/download/details.aspx?id=42951
• The connection to the Exchange Service will point to your MailBox (the credential used to run the script) by default

the below code identifies the latest mail with Attachment from inbox by checking for a specific Subject and saves the attachment.

            string filePath = "";
            string fileName = "";
            DateTime latestReceivedtime = DateTime.MinValue;
            EmailMessage latestEmail = null;
            try
            {
                //Connect to ExchangeService
                ExchangeService es = new ExchangeService(ExchangeVersion.Exchange2013);
                //es.Credentials = new WebCredentials("User", "Password", "domain");
                //es.AutodiscoverUrl("URL");
                es.Url = new Uri("https://domain/EWS/Exchange.asmx"); //mail.company.com
             
                /* bind the others mailbox looking for using service instance, provided you have access to                                others mail box
                Mailbox mb = new Mailbox(@"firstname.lastname@company.com");
                FolderId fid = new FolderId(WellKnownFolderName.Inbox, mb);
                Folder inbox = Folder.Bind(es, fid);
                 */

                 //  10 mails per page in DESC order
                 ItemView view = new ItemView(10);
                 view.OrderBy.Add(ItemSchema.DateTimeReceived, SortDirection.Descending);
             
                //Find mails
                 FindItemsResults<Item> fir = es.FindItems(WellKnownFolderName.Inbox, "Subject", view);

                 foreach (Item item in fir.Items)
                 {
                   item.Load(); //Load the entire message with attachment
                   EmailMessage email = item as EmailMessage;
                   if (email != null)
                   {
                    if (email.HasAttachments == true && email.Attachments.Count == 1)
                    {
                       if (email.Subject.StartsWith("Subject") == true)
                       {                                                  
                            if (email.DateTimeReceived > latestReceivedtime) //get latest email
                            {
                                latestReceivedtime = email.DateTimeReceived;
                                filePath = Path.Combine(Dts.Variables["User::SourceFolderPath"].Value.ToString()
                                                        , email.DateTimeReceived.Date.ToString("MM.dd.yyyy") + "_" + email.Attachments[0].Name);
                                fileName = email.DateTimeReceived.Date.ToString("MM.dd.yyyy") + "_" + email.Attachments[0].Name.ToString();
                                latestEmail = email;
                            }
                         }
                      }
                   }
                 }

                 //Save attachment
                 if (File.Exists(filePath) == false && filePath != "")
                 {                
                      FileAttachment fileAttachment = latestEmail.Attachments[0] as FileAttachment;
                      fileAttachment.Load(filePath);
                  }                
               

                 Dts.Variables["User::SourceFileName"].Value = fileName;

                 Dts.TaskResult = (int)ScriptResults.Success;
             
            }
            catch (System.Runtime.InteropServices.COMException ex)
            {
                //Unlock variables
                if (Dts.Variables.Locked == true)
                {
                    Dts.Variables.Unlock();
                }
                //An error occurred.
                Dts.Events.FireError(0, "Error occured", ex.Message, String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;

            }

Method2: Using Outlook API

• Microsoft Office Outlook need to be installed
• By default, it points to outlook that is installed on the machine and uses the same credentials as Outlook mail by default does.

below is the sample code that identifies latest Email from a sender and the subject which has an attachment and saves the attachment in a folder.

            Microsoft.Office.Interop.Outlook.Application app = null;

            Microsoft.Office.Interop.Outlook._NameSpace ns = null;

            Microsoft.Office.Interop.Outlook.MAPIFolder inbox = null;

            string filePath = "";

            string fileName = "";

            DateTime latestReceivedtime = DateTime.MinValue;

            try

            {

              app = new Microsoft.Office.Interop.Outlook.Application();

              ns = app.GetNamespace("MAPI");

              //ns.Logon(null, null, false, false);

              inbox = ns.GetDefaultFolder(Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderInbox);

              Microsoft.Office.Interop.Outlook.Items inBoxItems = inbox.Items;

              Microsoft.Office.Interop.Outlook.MailItem newEmail = null;

              // inBoxItems = inBoxItems.Restrict("[Unread] = true");

              Microsoft.Office.Interop.Outlook.MailItem latestEmail = null;

              foreach (object collectionItem in inBoxItems)

              {

                 newEmail = collectionItem as Microsoft.Office.Interop.Outlook.MailItem;

                 if (newEmail != null && newEmail.SenderEmailAddress == "SenderEmail") //newEmail.SenderName == "senderName")  

                 {

                    if (newEmail.Attachments.Count == 1)// && newEmail.ReceivedTime.Date == DateTime.Now.Date)

                    {

                        if(newEmail.Subject.StartsWith("Subject") == true && newEmail.Attachments[1].FileName.StartsWith("FileName") == true)

                        {

                            if (newEmail.ReceivedTime > latestReceivedtime) //get latest email

                            {

                                latestReceivedtime = newEmail.ReceivedTime;

                                filePath = Path.Combine(Dts.Variables["User::SourceFolderPath"].Value.ToString(), newEmail.ReceivedTime.Date.ToString("MM.dd.yyyy") + "_" + newEmail.Attachments[1].FileName);

                                fileName = newEmail.ReceivedTime.Date.ToString("MM.dd.yyyy") + "_" + newEmail.Attachments[1].FileName.ToString();

                                latestEmail = newEmail;                                

                            }

                        }

                    }

                 }

              }

              if (File.Exists(filePath) == false)

              {

                  latestEmail.Attachments[1].SaveAsFile(filePath);

              }

              Dts.Variables["User::SourceFileName"].Value = fileName;

              Dts.TaskResult = (int)ScriptResults.Success;

            }

            catch (System.Runtime.InteropServices.COMException ex)

            {

                //Unlock variables

                if (Dts.Variables.Locked == true)

                {

                    Dts.Variables.Unlock();

                }

                //An error occurred.

                Dts.Events.FireError(0, "Error occured", ex.Message, String.Empty, 0);

                Dts.TaskResult = (int)ScriptResults.Failure;

            }

Instant SQL \ MDX & DAX Formatter

To format a SQL \ MDX & DAX Script Instantly one can use the below link. Its really good !

http://www.dpriver.com/pp/sqlformat.htm

http://formatmdx.azurewebsites.net/

SSAS -Tabular Model and SSRS - DAX Query

• SSAS -Tabular Model and SSRS - DAX Query:
  1. Tabular model tutorials : http://msdn.microsoft.com/en-in/library/hh231691.aspx
  2. Points to remember for Direct query mode in Tabular Model - http://msdn.microsoft.com/en-IN/library/hh230898.aspx
  3. DAX for Tabular Model : http://msdn.microsoft.com/en-in/library/hh213101.aspx#bkmk_DAXintm
  
  Note:
  1. Multiple DataSources are not allowed in Direct Query mode.
  2. Calculated Columns are not supported in Direct query mode.
  3. MDX doesnt work if Model is in Direct Query mode. Use DAX or XMLA.
  4. Direct Query mode doesnt support some DAX functions. http://msdn.microsoft.com/en-us/library/hh213006.aspx

# Keywords for SSRS Charts

To increase readability of a chart, we can use #keywords.
Example: Disable the Legend in the chart. And append the Legend text to the DataLabels

The link gives more information http://msdn.microsoft.com/en-us/library/dd207017.aspx

JavaScript in SSRS Reports

JavaScript can be used in SSRS Reports to meet atypical requirements.

Note : Type the following code "Go To URL"  in Action Tab

1. Open a report in a new Tab from other report

="javascript:void(window.open('http://to be opened report URL from report Manager'))"

2. Alert Box

="javascript:void alert('Some Alert Message')"

3. Confirmation Box

="javascript:var check=confirm('Do you want to navigate to New report?'); if (check == true) {window.open('http://to be opened report URL from report Manager')}"

4. Prompt Box

="javascript:var FirstName=prompt('what is your First Name?');LastName=prompt('what is your Last Name?'); if(FirstName!='') {void alert(FirstName+','+LastName)} "

5. Using Parameters in javascript

="javascript:var check=confirm('Do you want to generate the report for the selected DateRange?"& Parameters!StartDate.Value & "-" & Parameters!EndDate.Value & "'); if (check == true) {window.open('http://to be opened report URL from report Manager','_self')}"

Pie Chart with data labels outside in ssrs

Fig I

Fig II

Inorder to convert Fig I to Fig II, Select LabelStyle as Outside.

Note : Select CollectedStyle as SingleSlice if you want to group smaller units into a single unit as "Others"
Select CollectedStyle as CollectedPie to get another small pie chart that gives details of "Other" sector.