ssis - BimlStudio built a project, after adding it to Visual Studio, we got errors between project and package regarding protect

admin管理员组

文章数量:1312768

We use BimlStudio to build an SSIS project that includes multiple packages. However, after adding the output project to Visual Studio for test running, it always has errors about the disagreements between the project and the packages.

To simplify the presentation, we use the following minimized example of one project containing on package only. For both the project and the package, we set it to "EncryptSensitiveWithPassword" and the same password.

BimlStudio building was successful, and we added the generated project in output\ as an existing project to a Visual Studio empty solution of Integration Services.

Then we got confused in the following operations, and please point out if we missed something:

1. When adding the generated output to Visual Studio as an existing project, we did not get any prompt asking for password. We remember, Visual Studio always asks for password when opening a SSIS project with password encryption.
2. We can open the package in Visual Studio's Design view, however, when press the "Start" button, we got the following error from "Microsoft.SqlServer.Dts.Runtime.DtsInvalidProjectPasswordOrDataException" saying "The encrypted data in project manifest failed to load. The project manifest is corrupted or if the project is password protected, the specified password is incorrect."

Severity    Code    Description Project File    Line    Suppression State   Details
Error       Microsoft.SqlServer.Dts.Runtime.DtsInvalidProjectPasswordOrDataException: The encrypted data in project manifest failed to load. The project manifest is corrupted or if the project is password protected, the specified password is incorrect.
   at Microsoft.SqlServer.Dts.Runtime.Project.EncryptXmlNode(XmlNode xmlNode, XmlDocument xmlDocument)
   at Microsoft.SqlServer.Dts.Runtime.Project.SaveConnectionManagerToXML(ConnectionManager connectionManager, IDTSEvents events)
   at Microsoft.SqlServer.Dts.Runtime.Project.SaveConnectionManagers(IProjectStorage storage)
   at Microsoft.SqlServer.Dts.Runtime.Project.SaveTo(IProjectStorage storage)
   at Microsoft.SqlServer.Dts.Runtime.Project.Save()
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow)
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow)           0       

3. To workaround the above error, we manually open the Properties of the project and set the password to mypassword again. We also need to "Save All" in the Visual Studio.

4. When trying to run again, we got another error saying the package has a different password than the project.

Severity    Code    Description Project File    Line    Suppression State   Details
Error       Project consistency check failed. The following inconsistencies were detected:
    etl-biml__catalog_product_entity_int.dtsx has a different password than the project.            0       

5. To workaround the above error, we manually open the Properties of the package, and set the package's password to the same mypassword as the project's. Also, we need to save the change in Visual Studio.

Our Question

Eventually, we made the SSIS package run by the above workarounds. However, we wonder:

• Could it be that the passwords in Biml source code did not make it into the generated output? Please also point out if we missed anything.
• When it comes to a large project of 50 packages, it would be impractical to manually changing all the packages' properties. So, we need help.

For anything, please let us know, and we highly appreciate hints and suggestions.

Source Code

• The Project

<Biml xmlns=".xsd">
  <Projects>
    <PackageProject Name="etl-biml-studio-eservices-proj" ProtectionLevel="EncryptSensitiveWithPassword" ProjectPassword="mypassword">
      <Packages>
        <Package PackageName="etl-biml__catalog_product_entity_int" />
      </Packages>
      <Connections>
        <Connection ConnectionName="Source" />
        <Connection ConnectionName="Target" />
        <Connection ConnectionName="Meta-MSSQL" />
      </Connections>
    </PackageProject>
  </Projects>
</Biml>

• The package:

<Biml xmlns=".xsd">
  <Packages>
    <Package Name="etl-biml__catalog_product_entity_int" Language="None" ConstraintMode="LinearOnCompletion" ProtectionLevel="EncryptSensitiveWithPassword" PackagePassword="mypassword">
      <Tasks>
        <Dataflow Name="Data Flow" DelayValidation="true">
          <Transformations>
            <AdoNetSource Name="Source extract" Timeout="0" ConnectionName="Source">
              <DirectInput>select 
    `value_id`
  , `attribute_id`
  , `store_id`
  , `row_id`
  , `value`
 from catalog_product_entity_int
 where 
   value_id &gt; -1;</DirectInput>
            </AdoNetSource>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

本文标签：