BIML enable SSIS Package Configuration

Question

I am trying to generate a SSIS package using BIML. Within that package must be the package configuration enables (like the picture below). So the package use an exsisting configuration file (option: re-use exsising).

I'am using the following version:

OS : Windows 2008 R2 Enterprise
DB : MSSQLSERVER 2012 BIDS
(Visual Studio): 2012

I tried to use the following code:

PackageConfiguration part

 <!-- SET PACKAGE CONFIGURATION (DOES NOT WORK YET!!! )  -->       
    <PackageConfigurations>
        <PackageConfiguration Name="ADWHConfig">
          <ExternalFileInput ExtrnalFilePath="D:\ADWH\SSIS_ConfigFiles\ADWHConfig.dtsConfig">    
          </ExternalFileInput>
       </PackageConfiguration>
    </PackageConfigurations>

Full Script

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <#
        var cod_process = "80010007";
        var strDestenationTable =" BLA_BLA";
    #>

    <Connections>
        <OleDbConnection Name="LocalHost.ADWH01.dwh_user"
                         ConnectionString="Data Source=.;User ID=dwh_user;Initial Catalog=ADWH01;Provider=SQLNCLI11.1;database=ADWH01;Password=dbadmin_dwh;"
                         CreatePackageConfiguration="false">
        </OleDbConnection>
    </Connections>

    <Packages>
        <Package Name="TEST" ConstraintMode="Linear" >
            <Variables>
                <Variable Name="COD_INSTANCE" DataType="Int64">0</Variable>
                <Variable Name="COD_PROCESS" DataType="Int64">0</Variable>
                <Variable Name="ROW_COUNT" DataType="Int32">0</Variable>
                <Variable Name="COD_PROCESS_STATUS" DataType="String"></Variable>

                <!-- SET VARIABLES LOAD CALENDER -->
                <Variable Name="LOAD_STRATEGY" DataType="String"></Variable>
                <Variable Name="LOAD_CALENDER" DataType="Object"></Variable>
                <Variable Name="FirstDayOfPeriod" DataType="DateTime">1/1/1900</Variable>
                <Variable Name="LastDayOfPeriod" DataType="DateTime">1/1/1900</Variable>

                <!-- SET VARIABLES LOAD SNAPSHOT -->
                <Variable Name="LOAD_SNAPSHOTS" DataType="Object"></Variable>
                <Variable Name="M_UTC_SNAPSHOT" DataType="DateTime">1/1/1900</Variable>
                <Variable Name="M_COD_SOR" DataType="Int64">0</Variable>
            </Variables>

            <!-- SET PACKAGE CONFIGURATION (DOES NOT WORK YET!!! )  -->
            <PackageConfigurations>
                <PackageConfiguration Name="ADWHConfig">
                    <ExternalFileInput ExternalFilePath="D:\ADWH\SSIS_ConfigFiles\ADWHConfig.dtsConfig"></ExternalFileInput>
                </PackageConfiguration>
            </PackageConfigurations>

            <Tasks>
                <Dataflow Name="TEST 123">
                    <Transformations>
                        <OleDbSource Name="SNAPSHOT" ConnectionName="LocalHost.ADWH01.dwh_user">
                            <ExternalTableInput Table="DSAOMA.SNAPSHOT_D_KREDIETOVEREENKOMST2"></ExternalTableInput>
                            <Columns>
                                <Column SourceColumn="M_UTC_START" TargetColumn="M_UTC_START"></Column>
                                <Column SourceColumn="M_UTC_END" TargetColumn="M_UTC_END"></Column>
                                <Column SourceColumn="IDC_CONTRACT" TargetColumn="IDC_KREDIET_CONTRACT"></Column>
                                <Column SourceColumn="AFSCHRIJVINGSCODE_EWO_WOT" TargetColumn="AFSCHRIJVINGSCODE_EWO_WOT"></Column>
                                <Column SourceColumn="CONTRACTNUMMER" TargetColumn="ContractNummer"></Column>
                                <Column SourceColumn="DATUM_AANKOOP_KREDIET" TargetColumn="Datum_Aankoop_Krediet"></Column>
                                <Column SourceColumn="RISICOKLASSE" TargetColumn="Risicoklasse"></Column>
                                <Column SourceColumn="PROVISIEKLASSE" TargetColumn="Provisieklasse"></Column>
                            </Columns>
                        </OleDbSource>
                        <DerivedColumns Name="EXPRIndicatie_EWO_WOT">
                            <Columns>
                                <Column Name="Indicatie_EWO_WOT" DataType="VarNumeric" ReplaceExisting="false">!ISNULL(AFSCHRIJVINGSCODE_EWO_WOT) ? (DT_WSTR, 255)"J" :(DT_WSTR, 255) "N"</Column>
                            </Columns>
                            <InputPath OutputPathName="SNAPSHOT.Output"></InputPath>                    
                        </DerivedColumns>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
</Packages>
</Biml>

But this does not work when i am generating the package. Please can you help me fixt this problem. I tried this by following the website http://www.cathrinewilhelmsen.net/2014/06/17/package-configurations-and-connection-managers-in-biml/ to achief that.

You will need to explain what is happening... what errors you get... anything you have tried ... — BIDeveloper

billinkc billinkc · Accepted Answer · 2016-06-21T13:22:42

I slimmed down your Biml but was able to generate a package just fine. What it doesn't do, and perhaps that is your issue, is it doesn't actually create the .dtsconfig file. That onus is on you.

Here's my sample dtsconfig

<?xml version="1.0"?>
<DTSConfiguration>
    <DTSConfigurationHeading>
        <DTSConfigurationFileInfo GeneratedBy="billinkc" GeneratedFromPackageName="so_37936760" GeneratedFromPackageID="{7E71E2C4-B411-4CF7-9A18-30A021832B5B}" GeneratedDate="6/21/2016 8:07:55 AM"/>
    </DTSConfigurationHeading>
    <Configuration ConfiguredType="Property" Path="\Package.Variables[User::COD_INSTANCE].Properties[Value]" ValueType="Int64">
        <ConfiguredValue>1</ConfiguredValue>
    </Configuration>
</DTSConfiguration>

I am simply assigning a value of 1 to the COD_INSTANCE variable's Value property.

BIDS Helper let's me know there is a configuration on this variable by shading a corner blue

My version of your biml only has properties changed to match my local machine (and I removed the body of the dataflow), the guts of the configuration remain the same.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
        <Connections>
        <OleDbConnection Name="LocalHost.ADWH01.dwh_user"
            ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI11.0;Integrated Security=SSPI;"
            CreatePackageConfiguration="false">
        </OleDbConnection>
    </Connections>
    <Packages>
        <Package Name="so_37936760" ConstraintMode="Linear" >

            <Variables>
                <Variable Name="COD_INSTANCE" DataType="Int64">0</Variable>
                <Variable Name="COD_PROCESS" DataType="Int64">0</Variable>
                <Variable Name="ROW_COUNT" DataType="Int32">0</Variable>
                <Variable Name="COD_PROCESS_STATUS" DataType="String"></Variable>

                <!-- SET VARIABLES LOAD CALENDER -->
                <Variable Name="LOAD_STRATEGY" DataType="String"></Variable>
                <Variable Name="LOAD_CALENDER" DataType="Object"></Variable>
                <Variable Name="FirstDayOfPeriod" DataType="DateTime">1/1/1900</Variable>
                <Variable Name="LastDayOfPeriod" DataType="DateTime">1/1/1900</Variable>

                <!-- SET VARIABLES LOAD SNAPSHOT -->
                <Variable Name="LOAD_SNAPSHOTS" DataType="Object"></Variable>
                <Variable Name="M_UTC_SNAPSHOT" DataType="DateTime">1/1/1900</Variable>
                <Variable Name="M_COD_SOR" DataType="Int64">0</Variable>
            </Variables>

            <!-- SET PACKAGE CONFIGURATION (DOES NOT WORK YET!!! )  -->
            <PackageConfigurations>
                <PackageConfiguration Name="ADWHConfig">
                    <ExternalFileInput ExternalFilePath="c:\ssisdata\so\so_37936760.dtsConfig"></ExternalFileInput>
                </PackageConfiguration>
            </PackageConfigurations>

            <Tasks>
                <Dataflow Name="TEST 123">

                </Dataflow>
            </Tasks>
        </Package>
</Packages>    
</Biml>

The results are the same whether I'm in Package Deployment Model or Project Deployment Model - which surprised me. I figured Project Deployment Model would have nixed the ability to use package configuration.

BIML enable SSIS Package Configuration

2 Answers