/// <summary> /// Connects to SharePoint and gets any columns on the target /// </summary> /// <param name="input"></param> /// <param name="sharepointUrl"></param> /// <param name="listName"></param> private void CreateExternalMetaDataColumns( IDTSInput input, string sharepointUrl, string listName, string viewName, Dictionary<string, string> existingColumnData) { // No need to load if the Url is bad. if ((sharepointUrl == null) || (sharepointUrl.Length == 0)) return; // Need a list to continue if ((listName == null) || (listName.Length == 0)) return; input.ExternalMetadataColumnCollection.IsUsed = true; // If the list has changed, then we do not want any of the exisiting column data to // influence it (provides a way to actually reset the names if needed) if (input.Description != listName) { existingColumnData.Clear(); input.Description = listName; } try { List<SharePointUtility.DataObject.ColumnData> accessibleColumns = GetAccessibleSharePointColumns(sharepointUrl, listName, viewName); foreach (var column in accessibleColumns) { // Setup the primary column details from the List var dtsColumnMeta = input.ExternalMetadataColumnCollection.New(); if (existingColumnData.ContainsKey(column.Name)) dtsColumnMeta.Name = existingColumnData[column.Name]; else dtsColumnMeta.Name = column.FriendlyName; dtsColumnMeta.Description = column.DisplayName; dtsColumnMeta.Length = 0; dtsColumnMeta.Precision = 0; dtsColumnMeta.Scale = 0; if ("Boolean|AllDayEvent|Attachments|CrossProjectLink|Recurrence".Contains(column.SharePointType)) { dtsColumnMeta.DataType = DataType.DT_BOOL; } else if (column.SharePointType == "DateTime") { dtsColumnMeta.DataType = DataType.DT_DBTIMESTAMP; } else if ("Number|Currency".Contains(column.SharePointType)) { // Max = 100,000,000,000.00000 dtsColumnMeta.DataType = DataType.DT_R8; } else if ("Counter|Integer".Contains(column.SharePointType)) { dtsColumnMeta.DataType = DataType.DT_I4; } else if ("Guid".Contains(column.SharePointType)) { dtsColumnMeta.DataType = DataType.DT_GUID; } else { if (column.MaxLength == -1) { dtsColumnMeta.DataType = DataType.DT_NTEXT; dtsColumnMeta.Length = 0; } else { dtsColumnMeta.DataType = DataType.DT_WSTR; dtsColumnMeta.Length = column.MaxLength; } } var fieldNameMeta = dtsColumnMeta.CustomPropertyCollection.New(); fieldNameMeta.Name = "Id"; fieldNameMeta.Description = "SharePoint ID"; fieldNameMeta.Value = column.Name; // Map any columns found with the same name in the input var foundCol = (from col in input.InputColumnCollection.Cast<IDTSInputColumn>() where col.Name == dtsColumnMeta.Name select col).SingleOrDefault(); if (foundCol != null) { foundCol.ExternalMetadataColumnID = dtsColumnMeta.ID; } } } catch (SharePointUnhandledException) { // Exception happened, so clear the columns, which will invalidate this object. input.ExternalMetadataColumnCollection.RemoveAll(); throw; } }
/// <summary> /// Connects to SharePoint and gets any columns on the target /// </summary> /// <param name="input"></param> /// <param name="sharepointUrl"></param> /// <param name="listName"></param> private void CreateExternalMetaDataColumns( IDTSInput input, string sharepointUrl, string listName, string viewName, Dictionary <string, string> existingColumnData) { // No need to load if the Url is bad. if ((sharepointUrl == null) || (sharepointUrl.Length == 0)) { return; } // Need a list to continue if ((listName == null) || (listName.Length == 0)) { return; } input.ExternalMetadataColumnCollection.IsUsed = true; // If the list has changed, then we do not want any of the exisiting column data to // influence it (provides a way to actually reset the names if needed) if (input.Description != listName) { existingColumnData.Clear(); input.Description = listName; } try { List <SharePointUtility.DataObject.ColumnData> accessibleColumns = GetAccessibleSharePointColumns(sharepointUrl, listName, viewName); foreach (var column in accessibleColumns) { // Setup the primary column details from the List var dtsColumnMeta = input.ExternalMetadataColumnCollection.New(); if (existingColumnData.ContainsKey(column.Name)) { dtsColumnMeta.Name = existingColumnData[column.Name]; } else { dtsColumnMeta.Name = column.FriendlyName; } dtsColumnMeta.Description = column.DisplayName; dtsColumnMeta.Length = 0; dtsColumnMeta.Precision = 0; dtsColumnMeta.Scale = 0; if ("Boolean|AllDayEvent|Attachments|CrossProjectLink|Recurrence".Contains(column.SharePointType)) { dtsColumnMeta.DataType = DataType.DT_BOOL; } else if (column.SharePointType == "DateTime") { dtsColumnMeta.DataType = DataType.DT_DBTIMESTAMP; } else if ("Number|Currency".Contains(column.SharePointType)) { // Max = 100,000,000,000.00000 dtsColumnMeta.DataType = DataType.DT_R8; } else if ("Counter|Integer".Contains(column.SharePointType)) { dtsColumnMeta.DataType = DataType.DT_I4; } else if ("Guid".Contains(column.SharePointType)) { dtsColumnMeta.DataType = DataType.DT_GUID; } else { if (column.MaxLength == -1) { dtsColumnMeta.DataType = DataType.DT_NTEXT; dtsColumnMeta.Length = 0; } else { dtsColumnMeta.DataType = DataType.DT_WSTR; dtsColumnMeta.Length = column.MaxLength; } } var fieldNameMeta = dtsColumnMeta.CustomPropertyCollection.New(); fieldNameMeta.Name = "Id"; fieldNameMeta.Description = "SharePoint ID"; fieldNameMeta.Value = column.Name; // Map any columns found with the same name in the input var foundCol = (from col in input.InputColumnCollection.Cast <IDTSInputColumn>() where col.Name == dtsColumnMeta.Name select col).SingleOrDefault(); if (foundCol != null) { foundCol.ExternalMetadataColumnID = dtsColumnMeta.ID; } } } catch (SharePointUnhandledException) { // Exception happened, so clear the columns, which will invalidate this object. input.ExternalMetadataColumnCollection.RemoveAll(); throw; } }