Hi All, Is there a way to connect to Microsoft outlook shared calendar within SSIS? Thanks, Pulkit Ojha Microsoft Business Intelligence Developer

Note sure but you may be able to do that via a Script Task or Script Component.Tom SQL Server MVP Toronto, ON Canada

Hi Pulkit, You must be able to connect by placing whatever .Net code does it into a Script Task of SSIS Data Flow. In essence you inherit from the CalendarProvider, and off you go See this MSDN article on how such interoperability is achieved using .Net: http://msdn.microsoft.com/en-us/library/aa168130%28v=office.11%29.aspx and http://stackoverflow.com/questions/90899/net-get-all-outlook-calendar-items has en example on how to access calendar items.Arthur My Blog

Any idea how do we actually connect to exchange server? Any help would be great. Thanks, Pulkit Ojha Microsoft Business Intelligence Developer

Connecting to MS Exchange Server to retrieve and set calendar events: // Reference to Microsoft ActiveX Data Objects 2.5 Library // Reference to Microsoft CDO for Exchange 2000 Library // Reference to Active DS Type Library static CDO.Appointment CreateAppointment(DateTime StartTime, DateTime EndTime, String Subject, String Location, String TextBody, CDO.IMailbox iMbx) { try { // Variables. CDO.Appointment iAppt = new CDO.Appointment(); ADODB.Connection Conn = new ADODB.Connection(); Conn.Provider = "ExOLEDB.DataSource"; //Set the appointment properties. iAppt.StartTime = StartTime; iAppt.EndTime = EndTime; iAppt.Subject = Subject; iAppt.Location = Location; iAppt.TextBody = TextBody; //Save the appointment Conn.Open(iMbx.BaseFolder, "", "", -1); iAppt.DataSource.SaveToContainer(iMbx.Calendar, Conn, ADODB.ConnectModeEnum.adModeReadWrite, ADODB.RecordCreateOptionsEnum.adCreateNonCollection, ADODB.RecordOpenOptionsEnum.adOpenSource, "", ""); Console.WriteLine("Appointment saved."); return iAppt; } catch (Exception err) { Console.WriteLine(err.ToString()); return null; } } Arthur My Blog

Thanks Arthur. Thanks for the help. Will this code work when reqiured to connect to Exchange 2003, as we have exchange server 2003 for our organization. Also do we require Microsoft Outlook on the machine where this code would be running? Also can't get hold of Microsoft CDO for Exchange 2000 Library Can you please advise where I can get that? Hope I am not asking for too much. Thanks, Pulkit Ojha Microsoft Business Intelligence Developer

Also Arthur in the above code piece I can't understand how it actually connects as there is no information used in that code which tells which server to connect to. Can you please guide me on this? Thanks, Pulkit Ojha Microsoft Business Intelligence Developer

Thanks Arthur for your help, but I found an alternative which is as below. Hope this helps someone who is looking to read calendar entries of a particular user between specific dates. Well these can be made flexible depending on the requirements. I have specified the code which uses an individual's account & credentials to communicate to exchange server. In this case it was me. This is VB.Net Code which I eventually used in my SSIS Script task. You can copy this and paste into a VB.Net project and it should work for you As Is after replacing your UserName, Password, Domain Name and your Exchange Server Name. When using this code in your script task you might want to add reference to System.XML.dll which should resolve any reference errors. This should give you all the calendar entries between 1st October 2011 and 31st October 2011. Hope this helps. ------------------------------------------------------------------------------------------------------------------------------------------------------ Option Explicit On Option Strict On Module Module1 Sub Main() Dim Request As System.Net.HttpWebRequest Dim Response As System.Net.HttpWebResponse Dim MyCredentialCache As System.Net.CredentialCache Dim strPassword As String Dim strDomain As String Dim strUserName As String Dim strCalendarURI As String Dim strQuery As String Dim bytes() As Byte Dim RequestStream As System.IO.Stream Dim ResponseStream As System.IO.Stream Dim ResponseXmlDoc As System.Xml.XmlDocument Dim HrefNodes As System.Xml.XmlNodeList Dim SubjectNodeList As System.Xml.XmlNodeList Dim LocationNodeList As System.Xml.XmlNodeList Dim StartTimeNodeList As System.Xml.XmlNodeList Dim EndTimeNodeList As System.Xml.XmlNodeList Dim BusyStatusNodeList As System.Xml.XmlNodeList Dim InstanceTypeNodeList As System.Xml.XmlNodeList Try strUserName = "username" 'this is your machine log in name or account name strPassword = "password" 'this is your normal password strDomain = "YourDomainName" strCalendarURI = " http://YourExchangeServerName/exchange/Pulkit.Ojha/calendar/" 'here it should not be the username after the word "/exchange" instead should be the firstname.lastname or whateverconvention your organization follows to represent your emailid strQuery = "<?xml version=""1.0""?>" & _ "<g:searchrequest xmlns:g=""DAV:"">" & _ "<g:sql>SELECT ""urn:schemas:calendar:location"", ""urn:schemas:httpmail:subject"", " & _ """urn:schemas:calendar:dtstart"", ""urn:schemas:calendar:dtend"", " & _ """urn:schemas:calendar:busystatus"", ""urn:schemas:calendar:instancetype"" " & _ "FROM Scope('SHALLOW TRAVERSAL OF """ & strCalendarURI & """') " & _ "WHERE NOT ""urn:schemas:calendar:instancetype"" = 1 " & _ "AND ""DAV:contentclass"" = 'urn:content-classes:appointment' " & _ "AND ""urn:schemas:calendar:dtstart"" > '2011/10/01 00:00:00' " & _ "AND ""urn:schemas:calendar:dtstart"" &lt; '2011/10/31 00:00:00' " & _ "ORDER BY ""urn:schemas:calendar:dtstart"" ASC" & _ "</g:sql></g:searchrequest>" MyCredentialCache = New System.Net.CredentialCache MyCredentialCache.Add( New System.Uri(strCalendarURI), _ "NTLM", _ New System.Net.NetworkCredential(strUserName, strPassword, strDomain) _ ) Request = CType(System.Net.WebRequest.Create(strCalendarURI), _ System.Net.HttpWebRequest) Request.Credentials = MyCredentialCache Request.Method = "SEARCH" bytes = System.Text.Encoding.UTF8.GetBytes(strQuery) Request.ContentLength = bytes.Length RequestStream = Request.GetRequestStream() RequestStream.Write(bytes, 0, bytes.Length) RequestStream.Close() Request.ContentType = "text/xml" Request.Headers.Add( "Translate", "F") Response = CType(Request.GetResponse(), System.Net.HttpWebResponse) ResponseStream = Response.GetResponseStream() ResponseXmlDoc = New System.Xml.XmlDocument ResponseXmlDoc.Load(ResponseStream) HrefNodes = ResponseXmlDoc.GetElementsByTagName( "a:href") SubjectNodeList = ResponseXmlDoc.GetElementsByTagName( "e:subject") LocationNodeList = ResponseXmlDoc.GetElementsByTagName( "d:location") StartTimeNodeList = ResponseXmlDoc.GetElementsByTagName( "d:dtstart") EndTimeNodeList = ResponseXmlDoc.GetElementsByTagName( "d:dtend") BusyStatusNodeList = ResponseXmlDoc.GetElementsByTagName( "d:busystatus") InstanceTypeNodeList = ResponseXmlDoc.GetElementsByTagName( "d:instancetype") If SubjectNodeList.Count > 0 Then Console.WriteLine( "Calendar items") Dim i As Integer For i = 0 To SubjectNodeList.Count - 1 Console.WriteLine( "Subject: " + SubjectNodeList(i).InnerText) Console.WriteLine( "Location: " + LocationNodeList(i).InnerText) Console.WriteLine( "Start time: " + StartTimeNodeList(i).InnerText) Console.WriteLine( "End time: " + EndTimeNodeList(i).InnerText) Console.WriteLine( "Busy status: " + BusyStatusNodeList(i).InnerText) If InstanceTypeNodeList(i).InnerText = "0" Then Console.WriteLine( " Instance type: 0-Single appointment") ElseIf InstanceTypeNodeList(i).InnerText = "1" Then Console.WriteLine( " Instance type: 1-Master recurring appointment") ElseIf InstanceTypeNodeList(i).InnerText = "2" Then Console.WriteLine( " Instance type: 2-Single instance, recurring appointment") ElseIf InstanceTypeNodeList(i).InnerText = "3" Then Console.WriteLine( " Instance type: 3-Exception to a recurring appointment") Else Console.WriteLine( " Instance type: Unknown") Console.WriteLine( "") End If Next Else Console.WriteLine( "No calendar items found") End If ResponseStream.Close() Response.Close() Console.ReadLine() Catch ex As Exception Console.WriteLine(ex.Message) Console.ReadLine() End Try End Sub End Module ------------------------------------------------------------------------------------------------------------------- Thanks, Pulkit Ojha Microsoft Business Intelligence Developer