Schema Definitions from an ODBC Text Source

Recently, I’ve worked on disjointed migration project where things just didn’t work the way you expect.  For example, you usually like to access data direct from its source (direct from a relational database) but in this situation, this was just not allowed and we were given a load of text files (hundreds) to load.  Perhaps not so bad, you may say however, unfortunately, the documentation around those files was incomplete and incorrect.  It seems that we spent for too much time confirming valid formats and playing too and fro with vendors – Its not really value adding and lets face it, its not really a good use of time to transfer and manually configure text import.  So what could we do?  All files were deposited in a directory, so why not use the Schema collections from an ODBC source?

The idea is to setup an ODBC source that points to the directory with files in it, create a connection to that source and then use the meta data of that connection to iterate over each table in the source and get the column information from that table.  Note that for a text driver, each file represents a table and that’s why I refer to tables above.

1. Setting Up the Driver

I think this is relatively straight forward so I’ll just add the key points with a word of warning.  There is no x64 bit driver installed by default for windows, so you may want to download the Access one from here if you need to.  Access the ODBC drivers from your settings (just type ODBC in the search bar and then choose to setup a connection or access ODBC sources), then just add a new source and choose the text driver.

image

When you click Finish you’ll be presented with the window below where you can specify the directory you want to use and give the source a name (see below).  There’s no need to look at options, but if you want to, you can specify specific extensions but for now lets assume that everything in the folder is for import.

image

2. Data Access

As a side note, after you’ve set up the driver, you can now import the data as a table into Excel.  The menu path is (Data Tab) Get External Data > From Other Sources > Microsoft Query.  I know you could import the file directly (ie Get External Data > From Text) however, you want to use the text driver and text your ODBC source.  You’ll notice that this source is available as a database (see below), and each file in the directory is presented as a table.  The source that I previously created is shown below and you can see the following list of tables.

image image

3. Using the ODBC Shema Object

As a general rule, when we create a database connection in code, the connection object is accompanied by a schema and we can use this to interrogate  the source.  As you can see from the snippet, all we do is acquire the “tables” schema (ie list the tables), then iterate over each table to get the columns in that table.  The sample output this shown below and, as you can see, it defines the column names and types.

            OdbcConnection _con = new OdbcConnection("DSN=biml_text_files");
_con.Open();

DataTable _schema = _con.GetSchema();
DataTable _restrictions = _con.GetSchema("Restrictions");
DataTable _tables = _con.GetSchema("Tables");

foreach (DataRow _tablerow in _tables.Rows)
{
Console.WriteLine("{0}", _tablerow["TABLE_NAME"]);

string[] _table_restrictions = { null, null, _tablerow["TABLE_NAME"].ToString() };
DataTable _columns = _con.GetSchema("Columns", _table_restrictions);
foreach (DataRow _colrow in _columns.Rows)
{
Console.WriteLine("\t{0},{1},{2}", _colrow["COLUMN_NAME"], _colrow["TYPE_NAME"], _colrow["COLUMN_SIZE"]);

}

}


_con.Close();

image

Actually, you don’t even need to go to this trouble.  You could just get the columns schema without the restrictions which will give you a complete list of all columns for every table.  However, in our implementation, the data was sent back to a database so others could use it.

Conclusion

Working with the Schema Information objects allows us derive information about the source.  Its conceptually similar to the INFORMATION_SCHEMA tables in most databases however, when we apply this to text files, we can use that meta-data to build our ETL.

Advertisements

BIML Annotations

BIML offers a great method to classify and manage code through annotations.  Basically, an annotation is just a tag that provides the ability to include text into our BIML code.  No big deal right?  Well perhaps not, however, if we think about how we can use this feature a little deeper, we’ll see there’s some very powerful capability just under the covers. 

Consider for example, using BIML Script to define a large number of packages using a standard template from different source systems.  We may want to ‘tag’ each package with a tag that specifies the system.  We could a that tag to specify the system within each package and then use the value of the tag later in our script (say for example creating a master package that groups all the child packages of one system).

Lets look at a simple example which follows this scenario. In the script below, I generate 4 packages (which don’t do anything) and have an annotation called ENV in each of them.  The annotation has the Value ‘Test’.  You’ll also notice that I’ve specified this script as tier 1 (meaning that its executed first).

<#@ template language="C#" tier="1" #>
<# string _env="Test"; #>
  <Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
      <# for(int i=1;i<5;i++){ #>
        <Package Name="Package<#= i.ToString() #>" ConstraintMode="Linear">
          <Annotations>
            <Annotation Tag="ENV"><#= _env #></Annotation>
          </Annotations>
        </Package>
        <#} #>
    </Packages>
  </Biml>

The annotation tag (‘ENV’) is designed to add context to the package when the Biml Script is compiled/compiled.  You can’t see this if your using BIDS helper however I’ll show the expanded BIML is shown below.  Notice that each package has the tag ENV.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Packages>
	<Package Name="Package1" ConstraintMode="Linear">
		<Annotations>
			<Annotation Tag="ENV">Test</Annotation>
            </Annotations>
	</Package>
        <Package Name="Package2" ConstraintMode="Linear">
		<Annotations>
		<Annotation Tag="ENV">Test</Annotation>
            </Annotations>
		</Package>
        		<Package Name="Package3" ConstraintMode="Linear">
			<Annotations>
				<Annotation Tag="ENV">Test</Annotation>
            </Annotations>
		</Package>
        		<Package Name="Package4" ConstraintMode="Linear">
			<Annotations>
				<Annotation Tag="ENV">Test</Annotation>
            </Annotations>
		</Package>
            </Packages>
</Biml>

Now, I can now use that tag in other BIML scripts.  So, (for example) suppose I want a master package that execute my ‘Test’ packages.  All I would have to do is iterate over each of my packages (in the solution) and test each to see if they belonged to the ‘Test’ ENV(environment).  If they did contain that ENV value, I just include them in an execute package task.  Here’s how the second script looks;

 
<#@ template language="C#" tier="2" #>
  <Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
      <Package Name="MASTER" ConstraintMode="Linear">
        <Tasks>
          <# foreach (var _package in RootNode.Packages){
					   if (_package.GetTag("ENV")=="Test"){#>
          <ExecutePackage Name="Run <#= _package.Name #>">
            <Package PackageName="<#= _package.Name #>" />
          </ExecutePackage>
          <#} } #>
        </Tasks>
      </Package>
    </Packages>
  </Biml>

In the code above, you can see how I reference the a packages tag using the GetTag method.  Also note that the tier for this package is 2 so its executed after the prior script.  Once compiled, the output is a master package that executes each child (as below);

image

Of course this is a really simple example and you may suggest you could achieve something similar using the package name.  For example you could iterate over each package using the foreach (var _package in RootNode.Packages) above and reference _package.Name. 

This approach may work (of course) however, it relies on your package name fully encapsulating the functionality of all possible annotations – a process that could be very messy.  Suppose, I wanted to include creator, ‘ETL Stage’, ETL Version etc, soon my package name would be unmanageable.

If you are using BIDS helper, you may also consider using a different visual studio project for each ‘package type’, purpose and environment.  Again, this solution may work however, lets consider our original proposition.  We are using exactly the same pattern with different meta-data (that is, the environment).  Wouldn’t it make things simple to maintain this within the one solution?

This is where MIST can really help with development.  I use the same BIML script to generate multiple packages which are all held within the same solution, they are then compiled into different Visual Studio solutions.  Put simply, MIST allows me to keep everything in the one place.

Building the Master Child Execution with BIML

In a previous post we looked at using BIML to execute a stored proc in order to assign a return value to a variable in BIDS.  The use case for this is pretty simple, when we execute a package, we want to get an ID for the execution and then record execution information against that ID.  When we execute packages through a master and child arrangement, we need to do two things.  Firstly, the master package gets an execution id (to record the batch) and secondly, each child package execution gets its own id (relative to the parent) to record its own execution.  This is the classic application of header / detail relationships as seen in the following diagram.

image

If you’d like to build this in your environment, here’s the SQL (I’ve used an ETL_FRAMEWORK schema and included some procs to return return and audit_id)

USE [EXAMPLES]
GO

/* Header Table */
CREATE TABLE [ETL_FRAMEWORK].[MASTER_EXEC](
	[RUN_ID] [int] IDENTITY(1,1) NOT NULL,
	[JOB_NAME] [nvarchar](256) NULL,
	[START_DT] [datetime] NULL DEFAULT (getdate()),
 CONSTRAINT [pk_etl_framework_master_exec] PRIMARY KEY CLUSTERED ([RUN_ID])
);

/* child table */
CREATE TABLE [ETL_FRAMEWORK].[MASTER_CHILD_EXEC](
	[RUN_ID] [int] NULL,
	[CHILD_ID] [int] IDENTITY(1,1) NOT NULL,
	[JOB_NAME] [nvarchar](256) NULL,
	[START_DT] [datetime] NULL DEFAULT (getdate()),
 CONSTRAINT [pk_etl_framework_master_child_exec] PRIMARY KEY CLUSTERED ([CHILD_ID]) 
 );

ALTER TABLE [ETL_FRAMEWORK].[MASTER_CHILD_EXEC]  WITH CHECK ADD  CONSTRAINT [fk_etl_framework_master_child_exec_child_id] FOREIGN KEY([RUN_ID])
REFERENCES [ETL_FRAMEWORK].[MASTER_EXEC] ([RUN_ID])
ON DELETE CASCADE
;

ALTER TABLE [ETL_FRAMEWORK].[MASTER_CHILD_EXEC] CHECK CONSTRAINT [fk_etl_framework_master_child_exec_child_id]
;


/* proc for master_id */
CREATE PROC [ETL_FRAMEWORK].[MASTER_EXEC_ID_GET]
(
  @JOB_NAME NVARCHAR(256)
)
AS
BEGIN
 SET NOCOUNT ON;
 INSERT INTO ETL_FRAMEWORK.MASTER_EXEC(JOB_NAME) VALUES(@JOB_NAME);
 RETURN IDENT_CURRENT('ETL_FRAMEWORK.MASTER_EXEC');
END;

/* proc for child_id (requires master_id) */
CREATE PROC [ETL_FRAMEWORK].[MASTER_CHILD_EXEC_ID_GET]
(
  @RUN_ID INT 
  , @JOB_NAME NVARCHAR(256)
)
AS
BEGIN
 SET NOCOUNT ON;
 INSERT INTO ETL_FRAMEWORK.MASTER_CHILD_EXEC(RUN_ID, JOB_NAME) VALUES(@RUN_ID, @JOB_NAME);
 RETURN IDENT_CURRENT('ETL_FRAMEWORK.MASTER_CHILD_EXEC');
END;

/****************************************************/
/*	Want an example of how it runs ? Try this		*/
/****************************************************/
declare @master_id int; 
declare @child_id int ;;
exec @master_id = [ETL_FRAMEWORK].[MASTER_EXEC_ID_GET] 'TEST'
print @master_id;
exec @child_id = [ETL_FRAMEWORK].[MASTER_CHILD_EXEC_ID_GET] @master_id, 'TEST CHILD';
print @child_id ;

Now to BIML

Our solution is pretty simple – the parent fires first, gaining a id (fire the proc [ETL_FRAMEWORK].[MASTER_EXEC_ID_GET] ) and then passes this variable to the child package.  In the child package, we’ve registered MASTER_AUDIT_KEY as a parameter with the execute package task passing the variable down to the child.  We can see this in the two images below;

image

image

You’ll also notice that I have 2 biml files to build the package(s), these are provided in full as;

00_CHILDREN

<#@ template  language="C#" tier="1" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="MetaData" ConnectionString="Provider=SQLOLEDB;Data Source=localhost\SQL2014;Integrated Security=SSPI;Initial Catalog=EXAMPLES" />
  </Connections>
  <Packages>
    <Package Name="Child01" ConstraintMode="Linear" ProtectionLevel="EncryptSensitiveWithUserKey" >
      <Parameters>
        <Parameter DataType="Int32" Name="MASTER_AUDIT_KEY">-1</Parameter>
      </Parameters>
      <Variables>
        <Variable DataType="Int32" Name="CHILD_AUDIT_KEY">-1</Variable>
      </Variables>
      <Tasks>
        <ExecuteSQL Name="CHILD_KEY_GET" ConnectionName="MetaData">
          <DirectInput>exec ? = [ETL_FRAMEWORK].[MASTER_CHILD_EXEC_ID_GET] ?, ?</DirectInput>
          <Parameters>
            <Parameter VariableName="User.CHILD_AUDIT_KEY" Name="0" DataType="Int32" Direction="ReturnValue" />
            <Parameter VariableName="MASTER_AUDIT_KEY" Name="1" DataType="Int32" Direction="Input" />
            <Parameter VariableName="System.PackageName" Name="2" DataType="String" Length="50" Direction="Input" />
          </Parameters>
        </ExecuteSQL>
      </Tasks>
    </Package>
  </Packages>
</Biml>

01_MASTER

<#@ template  language="C#" tier="2" #>
  <Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages >
      <Package Name="Master" ConstraintMode="Linear" ProtectionLevel="EncryptSensitiveWithUserKey" >
        <Variables>
          <Variable DataType="Int32" Name="MASTER_AUDIT_KEY">-1</Variable>
        </Variables>
        <Tasks>
          <ExecuteSQL Name="MASTER_KEY_GET" ConnectionName="MetaData">
            <DirectInput>exec ? = [ETL_FRAMEWORK].[MASTER_EXEC_ID_GET] ?</DirectInput>
            <Parameters>
              <Parameter VariableName="User.MASTER_AUDIT_KEY" Name="0" DataType="Int32" Direction="ReturnValue" />
              <Parameter VariableName="System.PackageName" Name="1"  DataType="String" Length="50" Direction="Input" />
            </Parameters>
          </ExecuteSQL>
          <ExecutePackage Name="Exec Child01"  >
            <ExternalProjectPackage Package="Child01.dtsx" />
            <ParameterBindings>
              <ParameterBinding VariableName="User.MASTER_AUDIT_KEY" Name="MASTER_AUDIT_KEY" />
            </ParameterBindings>
          </ExecutePackage>
        </Tasks>
      </Package>
    </Packages>
  </Biml>

There are a few things to remember here so I’ll break each out into its own paragraph.  I’ve been over some of these points before but they are worth inclusion nevertheless.

The 2 BIML files can be executed together by selecting both, right clicking and selecting the ‘Generate SSIS Packages’ from the popup window.  The ability to do this is required because a biml snippet (for example the connection string) is defined in one code file and referenced in the other.  Naturally, the execution order is important (note the connection is defined in child) so we want to compile that (child) package first.  The use of the keyword ‘tier’ in the directive of the file determines the order of compilation for selected packages.

We can define a variable or parameter within in required scope through the tree structure of the XML document.  I’ve scoped these variables to the package (they are declared directly under the package node), however, if I wanted to restrict the scope of a variable to a container, I could declare it within the containers XML nodes.

We pass the parameter from the master package to the child through the parameter bindings tag of the ExecutePackage task.  Its pretty self explanatory (when you look at the code) but this is the focus of the post so its probably worth at least calling it out 🙂

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.

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.