Referencing Project Parameters

gravatar

Scott Currie

This sample shows how to create and reference a project parameter using Biml

published 01.30.14

last updated 06.01.15


Share

                            


<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Connection1" ConnectionString="Data Source=.;Initial Catalog=Test;Provider=SQLNCLI11;Integrated Security=SSPI;" CreateInProject="true" />
  </Connections>
  <Projects>
    <PackageProject Name="TestProject1">
      <Parameters>
        <Parameter Name="Parameter1" DataType="String">SELECT 2</Parameter>
        <Parameter Name="Parameter2" DataType="String">Foo</Parameter>
      </Parameters>
      <Packages>
        <Package PackageName="TestPackage" />
      </Packages>
    </PackageProject>
  </Projects>
  <Packages>
    <Package Name="TestPackage" ConstraintMode="Linear">
      <Parameters>
        <Parameter Name="Parameter2" DataType="String">WHERE 1=1</Parameter>
        <Parameter Name="Parameter3" DataType="String">Bar</Parameter>
      </Parameters>
      <Tasks>
        <ExecuteSQL Name="SQL Test ADONET" ConnectionName="Connection1">
          <DirectInput>SELECT * FROM dbo.Test WHERE TestColumn2 = ? AND TestColumn3 = ? AND TestColumn4 = ?</DirectInput>
          <Expressions>
            <Expression ExternalProperty="SqlStatementSource">@[$Project::Parameter1] + " " + @[$Package::Parameter2]</Expression>
          </Expressions>
          <Parameters>
            <Parameter Name="0" DataType="String" VariableName="TestProject1.Parameter2" Length="100" />
            <!-- The next two parameters map to the same package parameter using both fully scoped and short name references -->
            <Parameter Name="1" DataType="String" VariableName="TestPackage.Parameter3" Length="100" />
            <Parameter Name="1" DataType="String" VariableName="Parameter3" Length="100" />
          </Parameters>
        </ExecuteSQL>
      </Tasks>
    </Package>
  </Packages>
</Biml>
                        

This sample shows how to both create and reference project parameters using Biml. Note that if you are using BIDSHelper, you will still need to specify PackageProject and Parameter tags even if project parameters are already created in your SSIS params file. This is required so that the Biml compiler can properly reference and type-check the parameters.

You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

Comments

gravatar

Kris

3:52pm 02.19.14

Does adding the project package parameters to the BIML code cause BIDS to create the Project parameters? It appears to create the references in the package but it seems like you will have to create the actual parameters manually.

gravatar

Scott Currie

2:06am 03.14.14

The short answer is that yes, they must be created manually in BIDSHelper, unfortunately. Here is a thread on the BIDSHelper discussion board where this is dicussed in some detail: https://bidshelper.codeplex.com/discussions/447353

gravatar

StephenL

4:06pm 11.03.14

Hi Scott (or anybody else that can help!) my question is along simular lines for Project Connection Managers which is part of the . I have defined them within this node, however, how do I reference them further down in the project (ie as a connection for an executeSQL task)?

These are connections that already exist within the Project

Many thanks in advance

gravatar

StephenL

4:10pm 11.03.14

The missing word is PackageProject - Don't know why it dissapeared!

gravatar

DSmulders

2:44pm 04.02.15

This doesn't work at all unfortunately in the new 1.7.0 beta version. It seems that 'PackageProjects' is removed, but referencing a Project parameter fails because BIML checks for the definition, and you now didn't specify it within the BIML. Renders BIMLscript completely useless for SQL 2012 as we use project params in all our packages.

gravatar

Scott Currie

3:51pm 06.01.15

StephenL and DSmulders, PackageProject now lives under a collection called Projects. This enables us to support many additional types of projects (e.g. CubeProject, TabularProject, etc.) The sample has been edited to reflect this.

gravatar

Andrei2

11:23am 08.11.16

Hello There,

I would like to reference a project parameter as the connection string for a connection. Because I want to create a project connection and to parameterize it's connection string with a project parameter. Is this posible? I was thinking at something like this:

Thank you

gravatar

Andrei2

9:05am 08.12.16

I couldn't post the code example. Check this snippet on this link: http://www.tiikoni.com/tis/view/?id=98faaa5