6 Appendix B: Product Behavior

  • Article

The information in this specification is applicable to the following Microsoft products or supplemental software. References to product versions include updates to those products.

  • Microsoft SQL Server 2008

  • Microsoft SQL Server 2008 R2

  • Microsoft SQL Server 2012

  • Microsoft SQL Server 2014

  • Microsoft SQL Server 2016

  • Microsoft SQL Server 2017

  • Microsoft SQL Server 2019

  • Microsoft SQL Server 2022

Exceptions, if any, are noted in this section. If an update version, service pack or Knowledge Base (KB) number appears with a product name, the behavior changed in that update. The new behavior also applies to subsequent updates unless otherwise specified. If a product edition appears with the product version, behavior is different in that product edition.

Unless otherwise specified, any statement of optional behavior in this specification that is prescribed using the terms "SHOULD" or "SHOULD NOT" implies product behavior in accordance with the SHOULD or SHOULD NOT prescription. Unless otherwise specified, the term "MAY" implies that the product does not follow the prescription.

<1> Section 2: Integration Services provides user options for encryption. The default is that sensitive properties are not included in the clear text file. Options are provided to include sensitive information in the structure file in encrypted form.

<2> Section 2.1.2: In the Integration Services system, elements can appear in any order within the <xs:sequence> tags. The Integration Services user interface generates package files that do not adhere to the sequence that is stated in the XSD.

<3> Section 2.4.3.2: In Microsoft implementations, the InnerObject element is always empty.

<4> Section 2.4.4.2.1.1: The value of the RowDelimiter property is not used by Integration Services.

<5> Section 2.4.4.2.1.1: The RetainData enumeration value is not used by Integration Services.

<6> Section 2.4.4.2.3: The SMO object model is available only on the SQL Server database.

<7> Section 2.4.5: The value of this property is not processed by Integration Services.

<8> Section 2.4.5: The value of this property is not processed by Integration Services. By default, the Integration Services UI places the domain-qualified logon name of the package creator in this field.

<9> Section 2.4.5: The value of this property is not processed by Integration Services.

<10> Section 2.4.5: This field is for documentation and is not processed by Integration Services.

<11> Section 2.4.5: The VersionBuild value is incremented every time the package is saved when operating in the Integration Services UI or through the Integration Services runtime object model.

<12> Section 2.4.5: In Microsoft implementations, the VersionGUID value changes every time the package is saved.

<13> Section 2.4.5: The Integration Services UI sets this property to the version of the installed version of SQL Server that is present on the local machine.

<14> Section 2.5: This integer value is not currently used by the Integration Services system.

<15> Section 2.5.3: Integration Services sets the ExecutionLocation value to 0.

<16> Section 2.5.3: Integration Services sets the ExecutionAddress value to EMPTY.

<17> Section 2.5.3: Integration Services sets MaxConcurrent to 1.

<18> Section 2.5.3: The LockName property is left blank by Integration Services. If a value is present, it is ignored by Integration Services.

<19> Section 2.7.1.1: In Microsoft implementations, the default for the defaultBufferMaxRows attribute value is 10,000 rows.

<20> Section 2.7.1.1: In Microsoft implementations, the default value for the engineThreads attribute is 5. The data flow engine can, in some circumstances, exceed the engineThreads value. If the value is set higher than the data flow engine needs, the data flow engine only uses the number of threads that it needs.

<21> Section 2.7.1.1: In Microsoft implementations, the default for the defaultBufferSize attribute is 10 megabytes.

<22> Section 2.7.1.1: In Microsoft implementations, the default value that is used for the BLOBTempStoragePath attribute value is the folder that the Windows TMP environment variable points to.

<23> Section 2.7.1.1: In Microsoft implementations, the default value that is used for the bufferTempStoragePath attribute value is the folder that the Windows TEMP environment variable points to.

<24> Section 2.7.1.1.1.1: In Microsoft implementations, the validateExternalMetadata attribute specifies whether the Visual Studio UI validates column data against its external data source at design time.

TRUE specifies that the UI validates column data against external data sources at design time.

FALSE specifies that the UI does not validate external metadata against its external data source at design time.

<25> Section 2.7.1.1.1.1: The Integration Services system does not process the contents of the contactInfo attribute value.

<26> Section 2.7.1.1.1.1.1.1.2:  A value of 0 for the MaxMemoryUsageproperty element value indicates that the system dynamically sets the memory usage.

<27> Section 2.7.1.1.1.1.1.1.2: In Microsoft implementations, a value of 0 for the MaxMemoryUsage attribute specifies that the cache size has no limit.

<28> Section 2.7.1.1.1.1.1.1.2: Microsoft implementations require this value to be a valid SQL Server identifier name.

<29> Section 2.7.1.1.1.1.1.1.2: The ReferenceMetadataXml attribute value is not used by Integration Services.

<30> Section 2.7.1.1.1.1.1.1.2: The ReferenceMetadataXml attribute value is not used by Integration Services.

<31> Section 2.7.1.1.1.1.1.1.2: In Microsoft implementations, the VSTAProjectName attribute is the project name of the Visual Studio project.

<32> Section 2.7.1.1.1.1.1.1.2: In Microsoft implementations, a value of 0 for the MaximumThreads attribute specifies that the maximum number of threads is unlimited.

<33> Section 2.7.1.1.1.1.1.1.2: In Microsoft implementations, the value of 0 for the BatchSize attribute indicates that the batch size is set to the internal buffer size.

<34> Section 2.7.1.1.1.1.1.1.2: In Microsoft implementations, the value of 0 for the CommandTimeout attribute indicates that there is no timeout.

<35> Section 2.7.1.1.1.1.1.1.2: In Microsoft implementations, the value of 0 for the CommandTimeout attribute indicates that there is no timeout.

<36> Section 2.7.1.1.1.1.1.1.2: The FastLoadOptions property applies to Microsoft products such as Microsoft SQL Server and Microsoft Office Excel. It might not be applicable to servers or to applications from other vendors.

<37> Section 2.7.1.1.1.1.1.1.2: In Microsoft implementations, the value of 0 for the CommandTimeout attribute indicates that there is no timeout.

<38> Section 2.7.1.1.1.1.1.1.2: The FastLoadOptions property applies to Microsoft products such as SQL Server and Office Excel. It might not be applicable to servers or to applications from other vendors.

<39> Section 2.7.1.1.1.1.1.1.2: The contents of the ObjectRef string apply only to the Microsoft Object Model, AMO.

<40> Section 2.7.1.1.1.1.1.1.2: For an explanation of Analysis Services processing types, see [MSDN-POS].

<41> Section 2.7.1.1.1.1.1.1.2: The contents of the ObjectRef string apply only to the Microsoft Object Model, AMO.

<42> Section 2.7.1.1.1.1.1.1.2: For an explanation of Analysis Services processing types, see [MSDN-POS].

<43> Section 2.7.1.1.1.1.1.1.2: In Microsoft implementations, the value -1 indicates that no value is assigned to the BulkInsertFirstRow attribute.

<44> Section 2.7.1.1.1.1.1.1.2: In Microsoft implementations, if the LastRow attribute is set to -1, the value is not set.

<45> Section 2.7.1.1.1.1.1.1.2: In Microsoft implementations, the value 0 for the Timout attribute indicates that SQL Server does not time out.

<46> Section 2.7.1.1.1.1.1.1.2: In Microsoft implementations, when the MaxInsertCommitSize attribute value is set to 0, all rows are inserted in a single batch.

<47> Section 2.7.1.1.1.1.1.1.2: In Microsoft implementations, the value of 0 for the CommandTimeout attribute indicates that there is no timeout.

<48> Section 2.7.1.1.1.1.1.1.2: For more information about how Integration Services converts external types to strings, see [MSDN-ADONETSRC].

<49> Section 2.7.1.1.1.1.1.1.2: In Microsoft implementations, the value of 0 for the CommandTimeout attribute indicates that there is no timeout.

<50> Section 2.7.1.1.1.1.1.1.2: In Microsoft implementations, the value of 0 for the CommandTimeout attribute indicates that there is no timeout.

<51> Section 2.7.1.1.1.1.3.1.3: In Microsoft implementations, a value of -1 for the DestinationColumn property indicates that the column is not mapped to an output column.

<52> Section 2.7.1.1.1.1.4.1.1: In Microsoft implementations, a value of -1 for the SourceColumn attribute indicates that the column is not used for pivoting.

<53> Section 2.7.1.1.1.1.5: In Microsoft implementations, the list of components for the PipelineComponentClassIDEnum value of "{2E42D45B-F83C-400F-8D77-61DDE6A7DF29}" can be distinguished by the UserComponentTypeName attribute value on the component element.

<54> Section 2.7.1.1.1.1.6.5: This value is assigned by the Integration Services engine when a column is created and is used internally by the Integration Services engine to track the relationships between input columns and output columns that are downstream from this column and that reference this column.

<55> Section 2.7.1.5.1.1.1: In Microsoft implementations, the value 0 for BatchSize indicates default behavior, which is that all rows in the data file are processed as one transaction.

<56> Section 2.7.1.5.1.1.1: In Microsoft implementations, the value 0 for LastRow indicates default behavior, which is to process to the end of the file.

<57> Section 2.7.1.5.1.1.1.1: In SQL Server, for the DTSBulkInsert_DataFileType_Native enumeration value, the data file to load is created by bulk copying SQL Server data by using the bcp utility. For more information about the bcp utility, see [MSDN-BCPU].

<58> Section 2.7.1.5.1.1.1.1: In SQL Server, for the DTSBulkInsert_DataFileType_WideNative enumeration value, the data file to load is created by bulk copying SQL Server data by using the bcp utility.

<59> Section 2.7.1.5.1.1.1.2: The Bulk Insert Task works only in conjunction with SQL Server as the server. When the CodePageType attribute is set to the value "ACP", the code page is converted from the ANSI code page to the SQL Server code page.

<60> Section 2.7.1.5.1.1.1.2: The Bulk Insert Task works only in conjunction with SQL Server as the server. When the CodePageType attribute is set to the value "OEM", the code page is converted to the SQL Server code page.

<61> Section 2.7.1.6: In Microsoft implementations, this string is in the Data Mining Extensions (DMX) [MSDN-DMXR] language.

<62> Section 2.7.1.7.1.1.1: The contents of the DataSourcesType type are dynamically generated by Integration Services and contain a reference to each ConnectionManager in the Integration Services Package file. If any contents are present in this element in the package file, the content is ignored and dynamically regenerated as just stated.

<63> Section 2.7.1.7.1.1.2: Integration Services requires the ProfileMode element to be set to the value "Exact". Fast mode is not supported.

<64> Section 2.7.1.7.1.1.2.2.2: Integration Services sets the FrequentValueThreshold value to 0.001 by default if the FrequentValueThreshold value is not specified.

<65> Section 2.7.1.7.1.1.2.2.2: In Microsoft implementations, if the MaxNumberOfPatterns element is not specified, the default value is 10.

<66> Section 2.7.1.7.1.1.2.2.2: In Microsoft implementations, if the PercentageDataCoverageDesired element is not specified, the default value is 95.

<67> Section 2.7.1.7.1.1.2.2.2: In Microsoft implementations, if the CaseSensitive element is not specified, the default value is FALSE.

<68> Section 2.7.1.7.1.1.2.2.2: By default, in Microsoft implementations, the list of Delimiter elements contains the following characters: the space, horizontal tab (\t), new line (\n), and carriage return (\r). Additional Delimiter elements can be specified, but the default delimiters cannot be removed.

<69> Section 2.7.1.7.1.1.2.2.2: By default, in Microsoft implementations, the list of symbols contains the following characters: , . ; : - " ' ` ~ = & / \ @ ! ? ( ) < > [ ] { } | # * ^ %

<70> Section 2.7.1.7.1.1.2.2.2: In Microsoft implementations, the default value of 0.95 is used for the KeyStrengthThreshold element value if the KeyStrengthThreshold element value is not specified.

<71> Section 2.7.1.7.1.1.2.2.2: Integration Services supports the value FALSE only for the VerifyOutputInFastMode element.

<72> Section 2.7.1.7.1.1.2.2.2: In Microsoft implementations, the default value for the MaxNumberOfViolations element is 100.

<73> Section 2.7.1.7.1.1.2.2.2: In Microsoft implementations, the default value of 0.95 is used for the value of the FDStrengthThreshold element if the value of the FDStrengthThreshold element is not specified.

<74> Section 2.7.1.7.1.1.2.2.2: Integration Services supports the value FALSE only for the VerifyOutputInFastMode element.

<75> Section 2.7.1.7.1.1.2.2.2: In Microsoft implementations, the default value for the MaxNumberOfViolations element is 100.

<76> Section 2.7.1.7.1.1.2.2.2: Microsoft implementations require the SupersetDataSourceID attribute to be equal to the SubsetDataSourceID attribute.

<77> Section 2.7.1.7.1.1.2.2.2: In Microsoft implementations, the default value of 0.95 is used for the value of the InclusionStrengthThreshold element if the value of the InclusionStrengthThreshold element is not specified.

<78> Section 2.7.1.7.1.1.2.2.2: In Microsoft implementations, the default value of 0.95 is used for the value of the SupersetColumnsKeyThreshold element if the value of the SupersetColumnsKeyThreshold element is not specified.

<79> Section 2.7.1.7.1.1.2.2.2: Integration Services supports the value FALSE only for the VerifyOutputInFastMode element.

<80> Section 2.7.1.7.1.1.2.2.2: In Microsoft implementations, the default value for the MaxNumberOfViolations element is 100.

<81> Section 2.7.1.7.1.1.3: In Microsoft implementations, the contents of the DataProfileOutputType complex type are not used in the package file. If the DataProfileOutputType complex type is present, any content is ignored.

<82> Section 2.7.1.7.1.1.3: In Microsoft implementations, the Profiles element is enforced to be EMPTY. Any content that is present is ignored during package file processing.

<83> Section 2.7.1.8.1.1.1: In Microsoft implementations, every time the package is saved, a new GUID value is generated for the PackageVersionGUID attribute.

<84> Section 2.7.1.8.1.1.1: In Microsoft implementations, the SQLServer attribute value is the name of a SQL Server instance.

<85> Section 2.7.1.8.1.1.1: In Microsoft implementations, the database username is a specific SQL Server username. The SQLUsername attribute value is used only if the WindowsAuthentication attribute has a value of "False".

<86> Section 2.7.1.8.1.1.1.1: In Microsoft implementations, the "SQLServer" enumeration value specifies that the DTS 2000 Package file is stored on a SQL Server database server.

<87> Section 2.7.1.14.1.1.1: The DTSMessageLineageID attribute value is not used by the Integration Services system. Any value given for this attribute is ignored by Integration Services.

<88> Section 2.7.1.14.1.1.1: In Microsoft implementations, the Use2000Format attribute specifies whether the message is formatted with the SQL Server 2000 format.

"True" specifies that the message is formatted with the SQL Server 2000 format.

"False" specifies that the message is not formatted with the SQL Server 2000 format.

<89> Section 2.7.1.15: In Microsoft implementations, the VSTAMajorVersion integer value specifies the Visual Studio Tools for Applications (VSTA) major version number.

<90> Section 2.7.1.15: In Microsoft implementations, the VSTAMinorVersion integer value specifies the VSTA minor version number.

<91> Section 2.7.1.15.3: This string value is used by Visual Studio for supporting the debugging environment and is not set or interpreted by Integration Services.

<92> Section 2.7.1.22: Database logins are the logins authorized to the SQL Server instance.

<93> Section 2.8.5.1: The content of the EventID Property element value is ignored by the Integration Services system.

<94> Section 2.9.1: The content of the Description string is not processed by the Integration Services system.

<95> Section 2.9.1: The ObjectName value is not processed by Integration Services.

<96> Section 2.9.9: The InnerObjectObjectDataType type is empty for all standard components that ship with the Integration Services product.