BIML, Stored Procedures and Return Parameters

Despite the improved logging features that have been incorporated into SQL2012+, I am a great fan of defining your own logging methods.  The most common method for this is having a package recognised by an ‘audit key’ and then using that key as a base point to record information that you want throughout the package.  For example, get the key (at the same time record the package start), then record success, failure, row counts, variable values etc. 

When you use a master package to execute children, you associate the childs execution (key) with a master execution (key) and this opens up some really great logging.  We’ll look at how to pass those keys down to the child in the next post, but for now, lets look at how we generate and store a package key in BIML.  In our environment, we’ve got a ‘master table (MASTER_EXEC) to record (master) package execution and a stored proc (MASTER_EXEC_ID_GET) to return the identity value (when ever we fire the proc).  As a minimum, we pass the package name to that proc so we can identify the package that was run.  You can see the format of the table and the creation scripts below. 

CREATE TABLE DBO.MASTER_EXEC
(
 RUN_ID INT IDENTITY(1,1)
 , JOB_NAME NVARCHAR(256)
 , START_DT DATETIME DEFAULT(GETDATE())
)CREATE PROC DBO.MASTER_EXEC_ID_GET
(
  @JOB_NAME NVARCHAR(256)
)
AS
BEGIN
 SET NOCOUNT ON;
 INSERT INTO DBO.MASTER_EXEC(JOB_NAME) VALUES(@JOB_NAME);
 RETURN IDENT_CURRENT('DBO.MASTER_EXEC');
END

If we wanted to test this proc in SSMS, we’d use some syntax like;


DECLARE @RET INT;
EXEC @RET = [dbo].[MASTER_EXEC_ID_GET] 'Here is Some TEST Name'
PRINT @RET

Now, for the BIML.  Here, we just have to define a variable in the packages scope to hold the key  (Ive used a variable called MASTER_ID), and execute the proc.  Procedure execution occurs through an ExecuteSQL task and we assign (and capture) the stored procs parameter values through the ExecuteSql tasks parameters node.


<Biml xmlns="<a href="http://schemas.varigence.com/biml.xsd"">http://schemas.varigence.com/biml.xsd"</a>>
 <Connections>
  <OleDbConnection Name="BIML_DB" ConnectionString="Provider=SQLNCLI11;Server=(myserver);Integrated Security=SSPI;Database=BIML" />
 </Connections>
 <Packages>
  <Package Name="Master_3" ConstraintMode="Linear">
   <Variables>
    <Variable Name="MASTER_ID" DataType="Int32">-1</Variable>
   </Variables>
  <Tasks>
   <ExecuteSQL Name="AUDIT_KEY" ConnectionName="BIML_DB">
    <DirectInput>exec ? = dbo.MASTER_EXEC_ID_GET ?</DirectInput>
    <Parameters>
      <Parameter VariableName="User.MASTER_ID" Name="0" DataType="Int32" Direction="ReturnValue" />
      <Parameter VariableName="System.PackageName" Name="1" DataType="AnsiString" Length="256" Direction="Input" />
    </Parameters>
  </ExecuteSQL>
 </Tasks>
 </Package>
 </Packages> 
</Biml>

You can see that the SQL statement (in BIML) mirrors what we would otherwise do in SQL.  The BIML parameter definition does not need to specify the parameters by name (rather by their ordinal position in the SQL statement) and we also specify the direction.  This mimics exactly what we would do in SSIS.

Advertisements

BIML – Importing Text Files

Here’s a BIML snippet for reading data from a text file.  If you’ve read the prior post about pushing data (from a table) to a text file, you’ll see a lot of the same constructs.  There is;

  1. A connection object (ie the FlatFileConnection) that specifies a path to a file.  The connection requires a FileFormat.
  2. A FileFormat which specifies the nature of the file (ie columns, data types, delimiters etc)
  3. The data flow object (ie the Source in the DataFlow which is a FlatFileSource).

I do not think there is too much complexity in the Connection or [DataFlow] Source.  There are really only a few attributes that need to be defined and, if you think about the nature of the task we are trying to achieve, there are all required.

What is the most interesting is the specification of the FlatFileFormat.  This defines the nature of the data that we are connecting to.  Both at a header and detail level.  For example consider the delimiter constructs for the header (HeaderRowDelimiter).  You would think that the delimiter is applicable to the entire file but this is not the case and it also needs to be specified on a column by column basis.  Finally, we also need to specify the new line on the last column.  Perhaps that’s not so intuitive since we specify the row delimiter in the header section of the Format.

So here is what we are trying to import.

image

And here is the BIML snippet to import it.


<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FlatFileConnection Name="my_import" FileFormat="my_values" FilePath="C:\temp\my_values.csv" />
    </Connections>
    <FileFormats>
        <FlatFileFormat Name="my_values" ColumnNamesInFirstDataRow="true" RowDelimiter="CRLF" HeaderRowDelimiter="Comma" CodePage="1252" IsUnicode="false">
            <Columns>
                <Column Name="ID" DataType="Int32" Delimiter="Comma" />
                <Column Name="Name" DataType="AnsiString" Length="255" Delimiter="Comma" />
                <Column Name="Value" DataType="Double" Delimiter="CRLF"  />
            </Columns>
        </FlatFileFormat>
    </FileFormats>
    <Packages>
        <Package Name="import_MyValue" ConstraintMode="Linear" ProtectionLevel="EncryptAllWithUserKey">
            <Tasks>
                <Dataflow Name="load_values">
                    <Transformations>
                        <FlatFileSource Name="src_values" ConnectionName="my_import" />
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Cleaning Your Database with BIML

Blow it away and start again 🙂

When we are working in a dynamic environment, we need to push through changes quickly and a prerequisite for this means starting from scratch.  Starting with a blank database and deploying the build from a clean state.  If you can’t drop and recreate the database, then the next best option is to clean it out, delete all the objects and recreate them as part of your build.

You can scrub tables in several ways.  The one that I propose here is a sequential (and dynamic method) that is relatively straight forward (and of course we can look into BIML for it).  The template for the pattern is given in the pseudo code below;

Assume there are tables to delete
While there are tables to delete then
  table_list <- get the list of tables
  foreach table in table_list, try to delete the table
  determine if there are (still) tables to delete

In, SSIS, this looks like the following pattern and I have included a list of variables that I used to run the package.  There some more advanced features of SSIS being used here which I will call out;

  1. We use TABLE_COUNT (initial value 1) to control the continued execution of the package (that is, the WHILE_TABLE_EXISTS container).  This container runs while the TABLE_COUNT value is greater than 1 (or my database still has tables).
  2. The TABLES_IN_DB is an object that holds a list of table names, this is the result of the SQL Command GET_TABLE_LIST.  The purpose here, is to query the database metadata in order to determine a list of names.
  3. The iterator FOREACH_RECORD_IN_TALBE_LIST enumerates over each record in the TALBES_IN_DB (assigning the table name to the variable TABLE_NAME).  Within that container, we generate what SQL to execute (ie the drop command) in the GENERATE_SQL expression by assigning it to the SQL variable.  Then we execute that variable via an Execute Command.  Since we do not want the task to fail if the command does not work (after all there may be some dependencies between tables and execution order).
  4. Finally, after the first batch of executions has run (and hopefully all the tables are deleted), we recount the tables in the database, storing the values in the TABLE_COUNT variable.  Control is then passed back to the WHILE_TABLES_EXIST to see if there are tables in the database and determine whether the process should begin again.

image

 In BIML

This process is very generic and can be applied in any database.  There’s also no need for BIML Script in code (since we do not rely on the generation of tasks which specifically rely on data).  Here is the full snippet;


<Biml xmlns="http://schemas.varigence.com/biml.xsd">

<Connections>
    <OleDbConnection Name="FM_MYSQL" ConnectionString="provider=MSDataShape;server=foodmart_mysql;uid=foodmart_admin;pwd=whats_my_password?" DelayValidation="true"  />
</Connections>

    <Packages>
        <Package Name="01-Clean_MySQL" ProtectionLevel="EncryptAllWithUserKey" ConstraintMode="Linear">

            <!-- these variables are created in the packages scope -->
            <Variables>
                <Variable Name="TABLES_IN_DB" DataType="Object">
                </Variable>
                <Variable Name="TABLE_COUNT" DataType="Int32">1</Variable>
            </Variables>

            <Tasks>

                <!-- the first container (while records exists) note the evaluation expresssion -->
                <ForLoop Name="WHILE TABLES EXIST" ConstraintMode="Linear">
                    <LoopTestExpression>@TABLE_COUNT>=1</LoopTestExpression>

                    <!-- tasks within the container are contained in a tasks tag -->
                    <Tasks>

                        <!-- get the list of table (names) .. note the record set is assigned to the variable TABLES_IN_DB -->
                        <ExecuteSQL Name="GET_TABLE_LIST" ResultSet="Full" ConnectionName="FM_MYSQL">
                            <DirectInput>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='foodmart';</DirectInput>
                            <Results>
                                <Result Name="0" VariableName="User.TABLES_IN_DB" />
                            </Results>
                        </ExecuteSQL>

                        <!-- Iterate over each record in the TABLES_IN_DB variable
                            Note how we assign the current record to the TABLE_NAME variable -->
                        <ForEachAdoLoop Name="FOREACH_RECORD_IN_TABLE_LIST" SourceVariableName="User.TABLES_IN_DB" ConstraintMode="Linear">
                            <Variables>
                                <Variable Name="TABLE_NAME" DataType="String">
                                </Variable>
                                <Variable Name="SQL" DataType="String">
                                </Variable>
                            </Variables>
                            <VariableMappings>
                                <VariableMapping Name="0" VariableName="User.TABLE_NAME" />
                            </VariableMappings>

                            <Tasks>

                                <!-- Generate the SQL Statement –>
                                <Expression Name="GENERATE SQL" Expression="@[User::SQL]=&quot;DROP TABLE foodmart.&quot; + @[User::TABLE_NAME]"/>
                                <!-- Execute the SQL Statement (based on the user variable (SQL)) –>

                                <ExecuteSQL Name="DELETE TABLE" ConnectionName="FM_MYSQL" DelayValidation="true" FailPackageOnFailure="false">
                                    <VariableInput VariableName="User.SQL" />
                                </ExecuteSQL>

                            </Tasks>

                        </ForEachAdoLoop>

                        <!-- Recount Tables and store in the variable (which is passed back to the parent container)  -->
                        <ExecuteSQL Name="GET_TABLES_COUNT" ResultSet="SingleRow" ConnectionName="FM_MYSQL">
                            <DirectInput>SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='foodmart';</DirectInput>
                            <Results>
                                <Result Name="0" VariableName="User.TABLE_COUNT" />
                            </Results>
                        </ExecuteSQL>

                    </Tasks>
                </ForLoop>
            </Tasks>
        </Package>
    </Packages>

</Biml>

You might notice that I am using an MYSQL ODBC database connection.  Other features are commented in the code.

As stated, there is no reliance on BIML Script here, so you may ask ‘Why use BIML?’.  The reason for this is that the snippet includes samples of a few controls and how they are configured in different circumstances.

Biml XV – ForEach Record in the DataSet (Control Flow)

Our prior posts showed techniques using the OLEDB Command to iterate over each row in the dataflow (see Biml XII and Biml XIII). Using that task we fire an SQL statement for each row in the dataflow. Column (field) values can be passed to (and returned from) the command so the technique may be handy when a single (and arguably simple) statement is required.

If we wish to embed more logic, tasks and more precise management into the iterative process, a different approach may be required. We could use a for loop task as an iterator. This operates in the control flow (as opposed to the OLEDB Command being a data flow task) and imbeds iteration in its own object container. Of course, we can still access field values from the dataset and obtain return values however, the use of container may improve usability (for SSIS control) because it can hold multiple tasks, implement precedence constraints and execute in entirety for each row that in a dataset.

This post looks at how to implement this solution in Biml.

The Package

The basic implementation of this technique of iteration is shown in the package below.

We don’t go deeply into the package design (since we’ll be creating it in Biml) however, there are a few key takeaways that we should note about the package and the variables used in the technique;

The first Execute SQL task (read_dim) assigns the results of a query (the recordset) into a variable called sub_cat_results. The type of the variable is a System.Object and that variable is in the scope of the package.

The recordset is simple (based on adventure works) and just returns ID’s and Names for product subcategories.

select ProductSubcategoryID  as sub_cat_id
, Name as sub_cat_name
from Production.ProductSubcategory;
The object of the next task (a for loop container titled for_each_record) is to iterate over each record in the dataset. As each ‘new’ row is encountered, field values for the row are assigned to the variables sub_cat_id and sub_cat_name. This assignment occurs in the outer part of the task so that those values can be accessed by other tasks within the bounds of the loop.

Additionally the variables (sub_cat_id and sub_cat_name) are scoped so that they only exist within the loop container. There is really no requirement to do this (and they could exist in the package scope) however such a design policy may make the package more concise and modular. You can read more about variable scoping here.

The loop container contains its own tasks which (of course) can access variables in the package and the loop container. We’ve used Execute SQL tasks in this example however, we are not limited to only these tasks.

In our simple example, our select_id task queries task is shown as;

select *
from [Production].[ProductSubcategory]
where ProductSubcategoryID = ?;

Naturally the parameter value passed is held in the sub_cat_id variable.

We have not implemented any precedence constraints in the for loop container – it is kept simple for the purposes of demonstrations.

The biml

The entire biml for this package is shown below. The key notes on the biml implementation are;

The variable sub_cat_results is defined as a child of the package. Since we want to limit the scope of the sub_cat_id and sub_cat_name variables to the loop container, they are defined as children of that task (ForEachAdoLoop).
The first SQL task (read_dim) needs to return a recordset and assign it to the variable sub_cat_results. In order to do this, we need to set the ResultSet property of the task (to Full) and configure the Results tag of the task.
Our iterator (see the tag ForEachAdoLoop) has properties which specifies the variable that holds a recordset (sub_cat_results) and the precedence constraint that is applicable to the task. Of course that constraint can be overwritten on a task by task basis.
As stated above, the variables that are to be used with the loop container are defined with the container (see the Variables child tag). Had these variables been defined as children of the package, they would not need to be defined at the task (loop task) level.

However, note the naming convention where the variables are used within the package (ie where called). The variable mapping within the loop refers to the sub_cat_id variable in a fully qualified manner (iteration_01.for_each_record.User.sub_cat_id). We could also create the package without fully qualification (ie User.sub_cat_id) if we wish.

The tasks within the loop container are held within a tasks tag. This is exactly the same as tasks are defined within the package and so we may liken the containers definition to that of the package.

The Biml

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    
	<Connections>
        <OleDbConnection Name="adventure_works" ConnectionString="Provider=SQLOLEDB;Data Source=.\SQL2012;Integrated Security=SSPI;Initial Catalog=AdventureWorks2014" />
    </Connections>
	
    <Packages>
        <Package Name="iteration_01" ConstraintMode="Linear">
			
			<Variables>
				<Variable Name="sub_cat_results" DataType="Object" />
            </Variables>
            
			<Tasks>
				
				<!-- Get the DataSet Object -->
                <ExecuteSQL Name="read_dim" ConnectionName="adventure_works" ResultSet="Full">
					<DirectInput>select ProductSubcategoryID  as sub_cat_id, Name as sub_cat_name from Production.ProductSubcategory; </DirectInput>
					    <Results>
					    	<Result Name="0" VariableName="User.sub_cat_results" />
					    </Results>
                </ExecuteSQL>
				
				<!-- iterate over each row in the data set -->
                <ForEachAdoLoop Name="for_each_record" SourceVariableName="User.sub_cat_results" ConstraintMode="Parallel">
					<Variables>
						<Variable Name="sub_cat_id" DataType="Int32">0</Variable>
						<Variable Name="sub_cat_name" DataType="String" />
            		</Variables>
                    <VariableMappings>
							<VariableMapping Name="0" VariableName="iteration_01.for_each_record.User.sub_cat_id" />
							<VariableMapping Name="1" VariableName="iteration_01.for_each_record.User.sub_cat_name" />
                    </VariableMappings>
					
					<!-- execute some tasks using the variables passed from the dataset -->
					<Tasks>

						<ExecuteSQL Name="select_id" ConnectionName="adventure_works">
							<DirectInput>select * from [Production].[ProductSubcategory] where ProductSubcategoryID = ?;</DirectInput>
							<Parameters>
								<Parameter Name="sub_cat_id" DataType="Int32" Direction="Input" VariableName="iteration_01.for_each_record.User.sub_cat_id" />
                            </Parameters>
                        </ExecuteSQL>

						<ExecuteSQL Name="select_name" ConnectionName="adventure_works">
							<DirectInput>select * from [Production].[ProductSubcategory] where [Name] = ?;</DirectInput>
							<Parameters>
								<Parameter Name="sub_cat_name" DataType="String" Length="50" Direction="Input" VariableName="iteration_01.for_each_record.User.sub_cat_name" />
                            </Parameters>
                        </ExecuteSQL>
						
						</Tasks>
						
                </ForEachAdoLoop>
            
			</Tasks>
        
			</Package>
    </Packages>
</Biml>

BIML I – Getting Started with BIML

Traditional SSIS development requires the ETL developer to create a package and then manually add and configure tasks. While this method of visual development is fine, it suffers from reproducibility and maintenance overhead. For example, imagine (a very contrived scenario) where you create a package with a single ‘Execute SQL Task‘ and configure it to fire a procedure. Simple enough but now suppose you want to create a new package and fire a different proc – here you have to manually go in and follow all the same steps again. Perhaps not so bad but what if you create 10 packages and then decide that the Execute SQL Task should be prefixed EXSQL, you would have to manually go into each package and rename the task. This could be very cumbersome and it (by nature) carries a maintenance burden (or maintenance overhead).

BIML (Business Intelligence Markup Language) is an XML specification that (amongst other things) allows you to specify the creation SSIS packages. One of the great things about BIML is that it can also include code snippets that amplifies the ability to generate code. This first post will form part of a series that show how to use BIML to create packages and what type of outcome we can achieve. But (of course) first things first so let’s start with a simple goal, so let’s look at using BIML to generate a package that executes an SQL command.

Before we continue, a quick revisit my above comments about maintainability for a moment.

An SSIS package is really just an XML file right, so why can’t I just edit that?

If you’ve ever played around with an SSIS package, you’ll probably know that it is an XML file. You can see this by viewing the code of your packages as shown below (right click on the package and view code). So you may think that you can generate or alter this pretty easily.

However, I have found this XML to be very finicky. Suppose you wanted to redefine a connection by editing script (something which should be simple you’d think and I’ve discussed here). You would expect that you could just examine the code, find the connection string, alter it and save it. But I’ve never had any success in that approach. When you try to open the file again (using the visual designer) it is invalid so your pretty much helpless with the changes you tried to make (sorry but there’s no comeback). That’s only to change an existing package so the creation of a package is (IMO of course) is just not possible. You could use an API but that just seems out of reach for most.

Of course, newer versions of SSIS have project wide objects (like connections). Using these would naturally require less maintenance however were are still faced with task management (altering and creation) and package development. Further, if we want to define a group of packages by meta-data (say for example a list of tables to extract), there is no current way to do it in SSIS. A much simpler approach is BIML with give us a generic specification to development.

What do I Need to get running with BIML?

BIML is available when BIDS helper is installed. BIDS Helper is a community addin for SQL Server Data Tools (SSDT and BIDS) and is available on code plex. If you haven’t heard of BIDS helper before, take some time to look around the features. It really is a must have for business intelligence development in the Microsoft space.

Once BIDS helper is installed, you can add a new BIML file by right clicking on the Project (or SSIS Packages) node and selecting ‘Add New Biml File‘. The file is created in the Miscellaneous Folder (as shown below).

 

A single BIML file can contain the code that creates many packages (as will become apparent) but for now would we will just work with the basics and use it to create a package that fires an SQL statement.

Adding Code

Opening the BimlScript file shows the XML in the file (we should probably rename it to give it a decent name but we will not worry about it this time). When you start, the file just contains the root node (as below).

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
</Biml>

What BIML now allows us to do is create objects within the root (and later other nodes). For example, since we want to create a package, we naturally expect that we need to add a package child. Naming is pretty intuitive since it follows the SSIS object names. I’ve already hinted that we can use the same file to create multiple packages so it seems natural that a package tag should be embedded within a packages tag. Something like the following.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package></Package>
  </Packages>
</Biml>

Actually, I did cheat a little (and so can you). The intellisense functionality lets you know what tags are allowed at the position you are. Remember in XML that a tag is just an identifier for an object so since I want to do something in within by BIML tag (where else would I put it), I can just open a tag and see what options are available. I want to create a package (so a search for package shows I can add packages and of course within that, I can add a package).

Validation and Creation

Back to our package snippet. We’ve got the packages declaration (as above) to specify a package within a packages node. You’d expect there is a way to validate the XML to be sure it has the required information to create packages? Well, just right click on the (explorer solution) and select ‘Check Biml for Errors‘. A dialog opens with a list of errors.

 

If we think about it, it makes sense doesn’t it? Of course we need to give our packages a name! In contrast, the error for ConstraintMode is something that we take for granted when we generate packages visually. We define the order of tasks in a package through constraints (or connectors). However Biml has no indication and so the layout of tasks other than the order in which they appear in XML. How they relate to each other must be defined in the package declaration and this is the purpose of the ConstraintMode property. There are two allowable values for this property (either parallel or linear) which (as the name suggests) specifies that tasks will not be connected or connected sequentially.

So in order to create a package, we update the Biml code as (note that since we are only having one task in this package the constraint mode is irrelevant);

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
  <Package ConstraintMode="Linear" Name="package_01"></Package>
</Packages>
</Biml>

We now have everything that we need to generate packages from the script. Simply right click on the Biml file and select the Generate SSIS Packages (as above). You’ll see a new packages create under the SIS packages node in solution explorer. Of course there are no tasks in our package.

Adding the Execute SQL task to the Package.

We already seen that the XML nodes in naming conventions are very similar to that of SSIS (both in structure and definition). If we think about adding an Execute SQL task to the package, then, there would be a few points we could make (or at least assume);

  1. a task would be embedded within a tasks node
  2. the task would probably be defined by name (or a task type)
  3. an execute SQL task requires a connection
  4. and execute SQL task requires a command
  5. the connection would be required before the command.

Turns out that the Biml to add this task to our package is pretty straightforward. Of course you can use the IntelliSense to suggest what nodes are available and, when you look at the code and think about what you’re trying to do it seems to make sense (that is, create a connection, create a package, at a task to the package uses a connection and has some syntax).

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection Name="SQL01" ConnectionString="Provider=SQLNCLI10.1;Data Source=.;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW"/>
  </Connections>
  <Packages>
    <Package ConstraintMode="Linear" Name="package_01">
      <Tasks>
        <ExecuteSQL Name="fire_proc_01" ConnectionName="SQL01" >
          <DirectInput>Select 1</DirectInput>
        </ExecuteSQL>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Perhaps the most interesting thing about this snippet is the connection object. We’d expect it to be embedded within connections however, the location as a root node gives us an advantage of reuse, so adding another task with the package or additional packages and tasks can reuse that connection. For example, to create 2 packages with 2 Execute SQL commands in each (which require the first to complete and then the next), we could write;

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection Name="SQL01" ConnectionString="Provider=SQLNCLI10.1;… (as above) "/>
  </Connections>
  <Packages>
    <Package ConstraintMode="Linear" Name="package_01">
      <Tasks>
        <ExecuteSQL Name="SQL_CMD_1" ConnectionName="SQL01" >
          <DirectInput>Select 1</DirectInput>
        </ExecuteSQL>
        <ExecuteSQL Name="SQL_CMD_2" ConnectionName="SQL01" >
          <DirectInput>Select 1</DirectInput>
        </ExecuteSQL>
      </Tasks>
    </Package>
    <Package ConstraintMode="Parallel" Name="package_02">
      <Tasks>
        <ExecuteSQL Name="SQL_CMD_1" ConnectionName="SQL01" >
          <DirectInput>Select 1</DirectInput>
        </ExecuteSQL>
        <ExecuteSQL Name="SQL_CMD_2" ConnectionName="SQL01" >
          <DirectInput>Select 1</DirectInput>
        </ExecuteSQL>
      </Tasks>
    </Package>
  </Packages>
</Biml>
This produces package_01 which looks like and we can see how the specification of ConstraintMode has caused SQL_CMD_1 to link to SQL_CMD_2.

 

Note the output from the second package definition (where ConstratinMode is Parallel) does not introduce precedence constraints between the 2 tasks.

Conclusion

This post has looked at using Biml to create packages in SSIS. It’s a pretty simple example, but we can see how the XML biml can be used to automatically create (and recreate) packages. If we wanted to change a property (say for example the SQL syntax), we could just change it in the code and regenerate the packages.

In later posts, we’ll extend this functional knowledge for more complicated scenarios.