Search code examples
bidsssis-2012biml

BIML never generate Package ProtectionLevel properly


I have set my project level protection level to EncryptSensitiveWithPassword and in all my packages that I generate using BIML I have code below

<Package Name ="MyPackage" ProtectionLevel="EncryptSensitiveWithPassword" PackagePassword="mypass">

For some reason I have to set ProtectionLevel to EncryptSensitiveWithPassword manually after every time I generate package using BIDS Helper. This is a painful job. I have no clue why should not work. Any clues?

I am using 1.7.0 version of BIDSHelper downloaded from Codeplex and SQL Server Data Tools version 2012 (as you might already know the confusing part is that VS version is 2010)

Update: Below is the new structure of biml recommended by iamdave which is not generating any ouput in the ProjectSubPath and the ExceptionDataMigrator package is still generating witth default packlage protection

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Projects>
        <PackageProject ProjectSubpath="bimlOutput"
                Name = "Solution/Project"
                ProtectionLevel = "EncryptSensitiveWithPassword"
                ProjectPassword = "password"
                >
            <Parameters>
                <Parameter Name="sTestParam" DataType="String">This is project parameter</Parameter>
            </Parameters>
            <Packages>
                <Package PackageName="ExceptionDataMigrator">

                </Package>
            </Packages>

        </PackageProject>
    </Projects>
    <Connections>
        <Connection Name="Staging" CreateInProject="true" ConnectionString="Data Source=b.database.windows.net;User ID=h;Initial Catalog=Migration_Staging;Password=z;Provider=SQLNCLI11.1;Auto Translate=False;" RetainSameConnection="true" />
        <Connection Name="Reporting" CreateInProject="true" ConnectionString="Data Source=Dev2;Initial Catalog=ng;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=false;" RetainSameConnection="true" />
        <Connection Name="SalesForceDB" CreateInProject="true" ConnectionString="Data Source=db;Initial Catalog=SalesForce;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=false;" />
    </Connections>
    <Packages>

    <Package Name="ExceptionDataMigrator" ConstraintMode="Linear" ProtectionLevel="EncryptSensitiveWithPassword" PackagePassword="password">
        <Parameters>

Solution

  • Are you creating the packages in the same VS project as your BIML files or as a whole new solution? If I remember correctly I had a similar problem and resolved it by specifying a ProjectSubPath within the <PackageProject> node of <Projects>, as the packages being created within my BIML project were inheriting the protection level of the BIML project:

    <Projects>
        <PackageProject
                ProjectSubPath = "Folder to save new solution"
                Name = "Solution/Project Name"
                ProtectionLevel = "EncryptSensitiveWithPassword"
                ProjectPassword = "Password"
                >
            <Parameters>
                <!-- Add Parameters here -->
            </Parameters>
            <Packages>
                <Package PackageName = "Package 1" />
            </Packages>
        </PackageProject>
    </Projects>
    

    Be VERY CAREFUL where you point that ProjectSubPath as it will completely delete any folder that already exists at that address, regardless of what it contains, then recreate it and create your new solution within.