private void ScanIntegrationServicesExecutableForPropertiesWithNonDefaultValue(DtsObject o, string FriendlyPath) { if (o == null) { return; } if (packageDefault == null) { packageDefault = new Package(); } DtsObject defaultObject; if (o is Package) { defaultObject = packageDefault; } else if (o is IDTSName) { if (dictCachedDtsObjects.ContainsKey(((IDTSName)o).CreationName)) { defaultObject = dictCachedDtsObjects[((IDTSName)o).CreationName]; } else if (o is DtsEventHandler) { defaultObject = (DtsObject)packageDefault.EventHandlers.Add(((IDTSName)o).CreationName); dictCachedDtsObjects.Add(((IDTSName)o).CreationName, defaultObject); } else if (o is ConnectionManager) { defaultObject = (DtsObject)packageDefault.Connections.Add(((IDTSName)o).CreationName); dictCachedDtsObjects.Add(((IDTSName)o).CreationName, defaultObject); } else { defaultObject = packageDefault.Executables.Add(((IDTSName)o).CreationName); dictCachedDtsObjects.Add(((IDTSName)o).CreationName, defaultObject); } } else { throw new Exception("Object " + o.GetType().FullName + " does not implement IDTSName."); } PropertyInfo[] properties = o.GetType().GetProperties(BindingFlags.Public | BindingFlags.FlattenHierarchy | BindingFlags.Instance); foreach (PropertyInfo prop in properties) { if (!prop.CanWrite || !prop.CanRead) { continue; } //SSIS objects don't have a DefaultValueAttribute, which is wy we have to create a new control flow object (defaultObject) above and compare properties from this object to that object object[] attrs = prop.GetCustomAttributes(typeof(System.ComponentModel.BrowsableAttribute), true); System.ComponentModel.BrowsableAttribute browsableAttr = (System.ComponentModel.BrowsableAttribute)(attrs.Length > 0 ? attrs[0] : null); if (browsableAttr != null && !browsableAttr.Browsable) { continue; //don't show attributes marked not browsable } attrs = prop.GetCustomAttributes(typeof(System.ComponentModel.ReadOnlyAttribute), true); System.ComponentModel.ReadOnlyAttribute readOnlyAttr = (System.ComponentModel.ReadOnlyAttribute)(attrs.Length > 0 ? attrs[0] : null); if (readOnlyAttr != null && readOnlyAttr.IsReadOnly) { continue; //don't show attributes marked read only } if (prop.PropertyType.Namespace != "System" && !prop.PropertyType.IsPrimitive && !prop.PropertyType.IsValueType && !prop.PropertyType.IsEnum) { continue; } if (prop.PropertyType == typeof(DateTime)) { continue; } if (prop.PropertyType == typeof(string)) { continue; } if (prop.Name == "VersionBuild") { continue; } if (prop.Name == "VersionMajor") { continue; } if (prop.Name == "VersionMinor") { continue; } if (prop.Name == "PackageType") { continue; } object value = prop.GetValue(o, null); object defaultValue = prop.GetValue(defaultObject, null); if (defaultValue != null && !defaultValue.Equals(value)) { string sValue = (value == null ? string.Empty : value.ToString()); this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath, prop.Name, defaultValue.ToString(), sValue)); } } if (o is IDTSObjectHost) { IDTSObjectHost host = (IDTSObjectHost)o; if (host.InnerObject is Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe) { properties = typeof(Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe).GetProperties(BindingFlags.Public | BindingFlags.FlattenHierarchy | BindingFlags.Instance); } else if (host.InnerObject is IDTSConnectionManagerDatabaseParametersXX) { properties = typeof(IDTSConnectionManagerDatabaseParametersXX).GetProperties(BindingFlags.Public | BindingFlags.FlattenHierarchy | BindingFlags.Instance); } else //probably won't turn up any properties because reflection on a COM object Type doesn't work { properties = host.InnerObject.GetType().GetProperties(BindingFlags.Public | BindingFlags.FlattenHierarchy | BindingFlags.Instance); } foreach (PropertyInfo prop in properties) { if (!prop.CanWrite || !prop.CanRead) { continue; } object[] attrs = prop.GetCustomAttributes(typeof(System.ComponentModel.BrowsableAttribute), true); System.ComponentModel.BrowsableAttribute browsableAttr = (System.ComponentModel.BrowsableAttribute)(attrs.Length > 0 ? attrs[0] : null); if (browsableAttr != null && !browsableAttr.Browsable) { continue; //don't show attributes marked not browsable } attrs = prop.GetCustomAttributes(typeof(System.ComponentModel.ReadOnlyAttribute), true); System.ComponentModel.ReadOnlyAttribute readOnlyAttr = (System.ComponentModel.ReadOnlyAttribute)(attrs.Length > 0 ? attrs[0] : null); if (readOnlyAttr != null && readOnlyAttr.IsReadOnly) { continue; //don't show attributes marked read only } if (prop.PropertyType.Namespace != "System" && !prop.PropertyType.IsPrimitive && !prop.PropertyType.IsValueType && !prop.PropertyType.IsEnum) { continue; } if (prop.PropertyType == typeof(DateTime)) { continue; } if (prop.PropertyType == typeof(string)) { continue; } if (prop.Name == "VersionBuild") { continue; } if (prop.Name == "VersionMajor") { continue; } if (prop.Name == "VersionMinor") { continue; } if (prop.Name == "PackageType") { continue; } if (prop.Name.StartsWith("IDTS")) { continue; } object value; object defaultValue; if (host.InnerObject is Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe) { try { value = host.InnerObject.GetType().InvokeMember(prop.Name, System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.GetProperty | System.Reflection.BindingFlags.FlattenHierarchy | System.Reflection.BindingFlags.Instance, null, host.InnerObject, null); } catch { continue; } try { defaultValue = ((IDTSObjectHost)defaultObject).InnerObject.GetType().InvokeMember(prop.Name, System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.GetProperty | System.Reflection.BindingFlags.FlattenHierarchy | System.Reflection.BindingFlags.Instance, null, ((IDTSObjectHost)defaultObject).InnerObject, null); } catch { defaultValue = null; } } else { value = prop.GetValue(host.InnerObject, null); defaultValue = prop.GetValue(((IDTSObjectHost)defaultObject).InnerObject, null); } if (defaultValue != null && !defaultValue.Equals(value)) { string sValue = (value == null ? string.Empty : value.ToString()); this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath, prop.Name, defaultValue.ToString(), sValue)); } } //scan data flow transforms if (host.InnerObject is Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe) { Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe pipe = (Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe)host.InnerObject; Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe defaultPipe = (Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe)((IDTSObjectHost)defaultObject).InnerObject; foreach (IDTSComponentMetaDataXX transform in pipe.ComponentMetaDataCollection) { IDTSComponentMetaDataXX defaultTransform = defaultPipe.ComponentMetaDataCollection.New(); defaultTransform.ComponentClassID = transform.ComponentClassID; CManagedComponentWrapper defaultInst = defaultTransform.Instantiate(); try { defaultInst.ProvideComponentProperties(); } catch { continue; //if there's a corrupt package (or if you don't have the component installed on your laptop?) then this might fail... so just move on } if (!transform.ValidateExternalMetadata) //this property isn't in the CustomPropertyCollection, so we have to check it manually { this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath + "\\" + transform.Name, "ValidateExternalMetadata", "True", "False")); } foreach (IDTSOutputXX output in transform.OutputCollection) //check for error row dispositions { if (output.ErrorRowDisposition == DTSRowDisposition.RD_IgnoreFailure) { this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath + "\\" + transform.Name + "\\" + output.Name, "ErrorRowDisposition", "FailComponent", "IgnoreFailure")); } if (output.TruncationRowDisposition == DTSRowDisposition.RD_IgnoreFailure) { this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath + "\\" + transform.Name + "\\" + output.Name, "TruncationRowDisposition", "FailComponent", "IgnoreFailure")); } } Microsoft.DataTransformationServices.Design.PipelinePropertiesWrapper propWrapper = new Microsoft.DataTransformationServices.Design.PipelinePropertiesWrapper(transform, transform, 0); foreach (IDTSCustomPropertyXX prop in transform.CustomPropertyCollection) { System.ComponentModel.PropertyDescriptor propDesc = (System.ComponentModel.PropertyDescriptor)propWrapper.GetType().InvokeMember("CreateCustomPropertyPropertyDescriptor", BindingFlags.DeclaredOnly | BindingFlags.NonPublic | BindingFlags.InvokeMethod | BindingFlags.Instance, null, propWrapper, new object[] { prop }); if (propDesc == null) { continue; } if (propDesc.IsReadOnly) { continue; } if (!propDesc.IsBrowsable) { continue; } if (prop.Value is string) { continue; } if (prop.Value is DateTime) { continue; } IDTSCustomPropertyXX defaultProp; try { defaultProp = defaultTransform.CustomPropertyCollection[prop.Name]; } catch { if (prop.Name == "PreCompile" && bool.Equals(prop.Value, false)) //this property doesn't show up in the new script component we created to determine defaults, so we have to check it manually { this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath + "\\" + transform.Name, prop.Name, "True", "False")); } continue; } System.ComponentModel.ITypeDescriptorContext context = new PipelinePropertyContext(transform, propDesc); string sValue = propDesc.Converter.ConvertToString(context, prop.Value); //gets nice text descriptions for enums on component properties string sDefaultValue = propDesc.Converter.ConvertToString(context, defaultProp.Value); //gets nice text descriptions for enums on component properties if (sValue == sDefaultValue) { continue; } this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath + "\\" + transform.Name, prop.Name, sDefaultValue, sValue)); } defaultPipe.ComponentMetaDataCollection.RemoveObjectByID(defaultTransform.ID); } } } }
private void ScanIntegrationServicesExecutableForPropertiesWithNonDefaultValue(DtsObject o, string FriendlyPath) { if (o == null) return; if (packageDefault == null) { packageDefault = new Package(); } DtsObject defaultObject; if (o is Package) { defaultObject = packageDefault; } else if (o is IDTSName) { if (dictCachedDtsObjects.ContainsKey(((IDTSName)o).CreationName)) { defaultObject = dictCachedDtsObjects[((IDTSName)o).CreationName]; } else if (o is DtsEventHandler) { defaultObject = (DtsObject)packageDefault.EventHandlers.Add(((IDTSName)o).CreationName); dictCachedDtsObjects.Add(((IDTSName)o).CreationName, defaultObject); } else if (o is ConnectionManager) { defaultObject = (DtsObject)packageDefault.Connections.Add(((IDTSName)o).CreationName); dictCachedDtsObjects.Add(((IDTSName)o).CreationName, defaultObject); } else { defaultObject = packageDefault.Executables.Add(((IDTSName)o).CreationName); dictCachedDtsObjects.Add(((IDTSName)o).CreationName, defaultObject); } } else { throw new Exception("Object " + o.GetType().FullName + " does not implement IDTSName."); } PropertyInfo[] properties = o.GetType().GetProperties(BindingFlags.Public | BindingFlags.FlattenHierarchy | BindingFlags.Instance); foreach (PropertyInfo prop in properties) { if (!prop.CanWrite || !prop.CanRead) continue; //SSIS objects don't have a DefaultValueAttribute, which is wy we have to create a new control flow object (defaultObject) above and compare properties from this object to that object object[] attrs = prop.GetCustomAttributes(typeof(System.ComponentModel.BrowsableAttribute), true); System.ComponentModel.BrowsableAttribute browsableAttr = (System.ComponentModel.BrowsableAttribute)(attrs.Length > 0 ? attrs[0] : null); if (browsableAttr != null && !browsableAttr.Browsable) continue; //don't show attributes marked not browsable attrs = prop.GetCustomAttributes(typeof(System.ComponentModel.ReadOnlyAttribute), true); System.ComponentModel.ReadOnlyAttribute readOnlyAttr = (System.ComponentModel.ReadOnlyAttribute)(attrs.Length > 0 ? attrs[0] : null); if (readOnlyAttr != null && readOnlyAttr.IsReadOnly) continue; //don't show attributes marked read only if (prop.PropertyType.Namespace != "System" && !prop.PropertyType.IsPrimitive && !prop.PropertyType.IsValueType && !prop.PropertyType.IsEnum) continue; if (prop.PropertyType == typeof(DateTime)) continue; if (prop.PropertyType == typeof(string)) continue; if (prop.Name == "VersionBuild") continue; if (prop.Name == "VersionMajor") continue; if (prop.Name == "VersionMinor") continue; if (prop.Name == "PackageType") continue; object value = prop.GetValue(o, null); object defaultValue = prop.GetValue(defaultObject, null); if (defaultValue != null && !defaultValue.Equals(value)) { string sValue = (value == null ? string.Empty : value.ToString()); this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath, prop.Name, defaultValue.ToString(), sValue)); } } if (o is IDTSObjectHost) { IDTSObjectHost host = (IDTSObjectHost)o; if (host.InnerObject is Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe) properties = typeof(Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipeClass).GetProperties(BindingFlags.Public | BindingFlags.FlattenHierarchy | BindingFlags.Instance); else if (host.InnerObject is IDTSConnectionManagerDatabaseParametersXX) properties = typeof(IDTSConnectionManagerDatabaseParametersXX).GetProperties(BindingFlags.Public | BindingFlags.FlattenHierarchy | BindingFlags.Instance); else //probably won't turn up any properties because reflection on a COM object Type doesn't work properties = host.InnerObject.GetType().GetProperties(BindingFlags.Public | BindingFlags.FlattenHierarchy | BindingFlags.Instance); foreach (PropertyInfo prop in properties) { if (!prop.CanWrite || !prop.CanRead) continue; object[] attrs = prop.GetCustomAttributes(typeof(System.ComponentModel.BrowsableAttribute), true); System.ComponentModel.BrowsableAttribute browsableAttr = (System.ComponentModel.BrowsableAttribute)(attrs.Length > 0 ? attrs[0] : null); if (browsableAttr != null && !browsableAttr.Browsable) continue; //don't show attributes marked not browsable attrs = prop.GetCustomAttributes(typeof(System.ComponentModel.ReadOnlyAttribute), true); System.ComponentModel.ReadOnlyAttribute readOnlyAttr = (System.ComponentModel.ReadOnlyAttribute)(attrs.Length > 0 ? attrs[0] : null); if (readOnlyAttr != null && readOnlyAttr.IsReadOnly) continue; //don't show attributes marked read only if (prop.PropertyType.Namespace != "System" && !prop.PropertyType.IsPrimitive && !prop.PropertyType.IsValueType && !prop.PropertyType.IsEnum) continue; if (prop.PropertyType == typeof(DateTime)) continue; if (prop.PropertyType == typeof(string)) continue; if (prop.Name == "VersionBuild") continue; if (prop.Name == "VersionMajor") continue; if (prop.Name == "VersionMinor") continue; if (prop.Name == "PackageType") continue; if (prop.Name.StartsWith("IDTS")) continue; object value; object defaultValue; if (host.InnerObject is Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe) { try { value = host.InnerObject.GetType().InvokeMember(prop.Name, System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.GetProperty | System.Reflection.BindingFlags.FlattenHierarchy | System.Reflection.BindingFlags.Instance, null, host.InnerObject, null); } catch { continue; } try { defaultValue = ((IDTSObjectHost)defaultObject).InnerObject.GetType().InvokeMember(prop.Name, System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.GetProperty | System.Reflection.BindingFlags.FlattenHierarchy | System.Reflection.BindingFlags.Instance, null, ((IDTSObjectHost)defaultObject).InnerObject, null); } catch { defaultValue = null; } } else { value = prop.GetValue(host.InnerObject, null); defaultValue = prop.GetValue(((IDTSObjectHost)defaultObject).InnerObject, null); } if (defaultValue != null && !defaultValue.Equals(value)) { string sValue = (value == null ? string.Empty : value.ToString()); this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath, prop.Name, defaultValue.ToString(), sValue)); } } //scan data flow transforms if (host.InnerObject is Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe) { Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe pipe = (Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe)host.InnerObject; Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe defaultPipe = (Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe)((IDTSObjectHost)defaultObject).InnerObject; foreach (IDTSComponentMetaDataXX transform in pipe.ComponentMetaDataCollection) { IDTSComponentMetaDataXX defaultTransform = defaultPipe.ComponentMetaDataCollection.New(); defaultTransform.ComponentClassID = transform.ComponentClassID; CManagedComponentWrapper defaultInst = defaultTransform.Instantiate(); try { defaultInst.ProvideComponentProperties(); } catch { continue; //if there's a corrupt package (or if you don't have the component installed on your laptop?) then this might fail... so just move on } if (!transform.ValidateExternalMetadata) //this property isn't in the CustomPropertyCollection, so we have to check it manually { this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath + "\\" + transform.Name, "ValidateExternalMetadata", "True", "False")); } foreach (IDTSOutputXX output in transform.OutputCollection) //check for error row dispositions { if (output.ErrorRowDisposition == DTSRowDisposition.RD_IgnoreFailure) { this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath + "\\" + transform.Name + "\\" + output.Name, "ErrorRowDisposition", "FailComponent", "IgnoreFailure")); } if (output.TruncationRowDisposition == DTSRowDisposition.RD_IgnoreFailure) { this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath + "\\" + transform.Name + "\\" + output.Name, "TruncationRowDisposition", "FailComponent", "IgnoreFailure")); } } Microsoft.DataTransformationServices.Design.PipelinePropertiesWrapper propWrapper = new Microsoft.DataTransformationServices.Design.PipelinePropertiesWrapper(transform, transform, 0); foreach (IDTSCustomPropertyXX prop in transform.CustomPropertyCollection) { System.ComponentModel.PropertyDescriptor propDesc = (System.ComponentModel.PropertyDescriptor)propWrapper.GetType().InvokeMember("CreateCustomPropertyPropertyDescriptor", BindingFlags.DeclaredOnly | BindingFlags.NonPublic | BindingFlags.InvokeMethod | BindingFlags.Instance, null, propWrapper, new object[] { prop }); if (propDesc == null) continue; if (propDesc.IsReadOnly) continue; if (!propDesc.IsBrowsable) continue; if (prop.Value is string) continue; if (prop.Value is DateTime) continue; IDTSCustomPropertyXX defaultProp; try { defaultProp = defaultTransform.CustomPropertyCollection[prop.Name]; } catch { if (prop.Name == "PreCompile" && bool.Equals(prop.Value, false)) //this property doesn't show up in the new script component we created to determine defaults, so we have to check it manually { this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath + "\\" + transform.Name, prop.Name, "True", "False")); } continue; } System.ComponentModel.ITypeDescriptorContext context = new PipelinePropertyContext(transform, propDesc); string sValue = propDesc.Converter.ConvertToString(context, prop.Value); //gets nice text descriptions for enums on component properties string sDefaultValue = propDesc.Converter.ConvertToString(context, defaultProp.Value); //gets nice text descriptions for enums on component properties if (sValue == sDefaultValue) continue; this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath + "\\" + transform.Name, prop.Name, sDefaultValue, sValue)); } defaultPipe.ComponentMetaDataCollection.RemoveObjectByID(defaultTransform.ID); } } } }