Skip to main content

Creating a Basic Package Using Biml

Why This Pattern Matters​

The shortest path from Biml to a working SSIS package goes through three steps: add a Biml file to the project, paste in the Biml that describes the package, and choose Expand Biml File. Walking through that loop once with a tiny example is the easiest way to confirm the toolchain is working before moving on to more interesting Biml.

Add a Biml File​

In an Integration Services project, right-click the project node in Solution Explorer and choose Add New Biml File. A new Biml file appears in the Miscellaneous folder, named 'BimlScript.biml' (with an incrementing suffix if other files already use that name). Renaming the file makes its purpose clearer; for this example, rename it to 'BasicPackage.biml'.

Double-clicking the file opens the XML editor in Visual Studio. Typing an opening angle bracket triggers Intellisense for valid Biml elements. If Intellisense does not appear, the troubleshooting steps in the BIDS Helper documentation cover the usual configuration problems.

Paste in the Biml​

Replace the contents of the file with the Biml below. The example defines one connection and one package. The package contains a single dataflow task with an OLE DB Source feeding a Multicast.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection Name="OperationsDB" ConnectionString="Server=.;Initial Catalog=OperationsDB;Integrated Security=SSPI;Provider=SQLNCLI11" />
  </Connections>
  <Packages>
    <Package Name="Biml Sample" AutoCreateConfigurationsType="None" ConstraintMode="Linear">
      <Tasks>
        <Dataflow Name="Extract Table List">
          <Transformations>
            <OleDbSource Name="Get Table List" ConnectionName="OperationsDB">
              <DirectInput>SELECT * FROM sys.tables</DirectInput>
            </OleDbSource>
            <Multicast Name="Multicast" />
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

The 'Connections' section defines an OLE DB connection that points at a sample database named 'OperationsDB'. The 'Packages' section defines a single package with one dataflow task. The dataflow contains two components: an OLE DB Source that reads the list of tables in the source database, and a Multicast that branches the rows.

Expand the Biml​

Right-click the Biml file and choose Expand Biml File. A new package appears in the SSIS Packages folder named 'Biml Sample.dtsx'. Opening the generated package in the SSIS designer shows the dataflow with the source and the multicast, exactly as described in the Biml.

What to Try Next​

This walkthrough covers the manual loop. Subsequent walkthroughs add BimlScript code nuggets so the same template can produce many packages from metadata, define tables in Biml, and control the order of execution in the control flow.