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:
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
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.
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
mypassword
as the project's. Also, we need to save the change in Visual Studio.Eventually, we made the SSIS package run by the above workarounds. However, we wonder:
For anything, please let us know, and we highly appreciate hints and suggestions.
<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>
<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 > -1;</DirectInput>
</AdoNetSource>
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>