Using Stored Procedure in Power Pivot

To get data from sql server using stored procedures in power pivot, follow below points:

1. SET ANSI_WARNINGS OFF (there should not be any warnings returned by the query)
2. SET NOCOUNT ON (query should not return more than 1 count. In the below example execute SPand insert returns count, then again on select another count is returned. So totally 2, and it should not be the case).

EX: 

SET ANSI_WARNINGS OFF

SET NOCOUNT ON

CREATE TABLE #tmp

(

EmployeeID INT

, Employee VARCHAR(225)

)

INSERT INTO #tmp EXEC usp_Name

SELECT * FROM #tmp

DROP TABLE #tmp

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;

            }