The Biml Interrogator demo was designed to show Biml Developers how to extract useful metadata from sources that do not natively support it. Originally it was designed for delimited text files and Excel files, but it could be used for other sources with a little work. My demos walk you through the steps you’d need to take to set up a project around these semi-structured data sources. You may need to alter these projects to fit your exact needs. If you’re having problems applying these examples to your environment, feel free to ask.
To get started with my Interrogator, clone my BimlDemos repository. I’ve recently relocated it to Azure DevOps: https://toyboxcreations.visualstudio.com/BimlDemos/_git/BimlDemos. When I moved the repository from GitHub to Azure, I also removed the BimlStudio code. I don’t plan to add that back in for a while; if you need to use the interrogator through BimlStudio, email me, and we can talk!
Open the project in Visual Studio with the Biml Express add-in. The code files appear in the Miscellaneous folder. Anytime you want to see the Biml generated from my scripts, simply ctrl+click 00_GetOuput in addition to the Biml Script(s) you want to test. 00_GetOutput will take the compiled, and flattened Biml generated and write it out to output.biml. To get this to work in your environment, you will need to update the value of OutputBimlFilePath in 00_Configuration to match the file location for your output.biml file.
In my case this location is C:\Repositories\Biml\Interrogator\BimlExpress Project\BimlExpress\output.biml. If you are working from a different location, you can right-click on output.biml in the solution explorer and click properties. This will open the properties window for this file. Copy the value of Full Path from this window to 00_Configuration, and then save the file. 00_GetOutput will then update your output file every time you run it.
This file stores values used in later scripts. If you have experience in c# application development, this would be your app.config or ini file.
- ExtractServer is your development SQL server instance. In my case, I develop on localhost, then promote to another machine for integration testing. This machine will be used to create tables that are then used to build the packages.
- ExtractDatabase is the database on ExtractServer that will hold those tables.
- Lines 10-17 will create ExtractDatabase on ExtractServer, if it doesn’t exist yet.
- SourceFolder is the folder that will be scanned for flat files to interrogate. Feel free to point this at any folder where you store your flat files.
- OutputBimlFilePath is the location of your output.biml file mentioned in the last section.
- ConnectionGuids is used to store a list of GUIDs that will uniquely identify connections in packages.
Building Biml Connections
Once the configuration file is updated for your environment, open 01_Connections-ADONET.biml and 01_Connections-OLEDB.biml. These scripts will set up connections in the Relational folder we’ll use in subsequent steps. When the Biml compiler references a database object, it has to have a connection object defined to do so.
The ADONET script will create an ADO.Net connection to our Extract database referenced in the configuration file and an ADO.Net connection to AdventuresWorks 2014 on your local instance. The AdventureWorks connection is used by script 04_Tables_SQL-ALL.biml. If you don’t want to run that demo, or if you do not have AdventureWorks set up on your local instance, feel free to comment this part of the script out by wrapping the Adventureworks connection in XML comment tags:
The OLEDB version sets up an OLEDB connection to our Extract database, as well as two connections to an Excel file in our testdata folder. This Excel connection and file are used in the 04_Tables_Excel.biml demo in BimlStudio, but that’s not ready for BimlExpress yet. Feel free to comment that out if you are not going to run the Excel demo.
To see the Connections’ Biml, Ctrl+click 00_GetOutput, 01_Connections-ADONET, and 01_Connections-OLEDB. Then right-click in the highlighted area and choose Generate SSIS Packages. Once the output window reports Biml expansion completed, open output.biml, and you’ll see the two AdoNetConnection nodes have taken the variables ExtractServer and ExtractDatabase, and filled those in. You’ll also see the OleDbConnection has filled in those same variables.
Building File Formats
After building connections, you need to build the file formats. 01_FileFormats.biml calls the interrogator class I built and tries to guess the structure of the files. The script takes that information and then builds the Biml structure representing the layout of any csv or txt files it finds in SourceFolder. You can run this like the connections, Ctrl-click 00_GetOutput and 01_FileFormats, then right-click on the highlighted area and choose Generate SSIS Packages. You can then see the FileFormat nodes in output.biml.
After you have a biml representation of FileFormats, you can then generate the connections for the FlatFiles. FlatFile connections have a required attribute for FileFormat. To see the code for the connections, you have to select 00_GetOutput, 01_FileFormats, and 02_Connections-FlatFiles. Then output will contain both the FileFormats and Connections to the flat files!
Build Biml Destination Reference Objects
To create Packages that read those flat files and write the output to a table, you must build references to your database, schemas, and tables. The code to build a database and schema is simple. To see them generated, include 02_Databases and 03_Schemas when using 00_GetOutput.
Writing the tables in Biml is more interesting since it uses the Interrogator again to read the file structures. To see the tables generated, you will need to include 01_Connections_ADONET, 02_Databases, 03_Schemas, and 04_Tables_FlatFiles when running 00_GetOutput.
Building out your packages
All the scripts to this point are dependencies on building packages. You’ll need connections to databases, file format definitions, connections to files, database, schema, and table definitions. If any are missing, you won’t get the packages you need. We will build two types of packages; the first is a package you can hand off to a DBA to deploy needed structures on a target server.
05_Packages_CreateObjects will build the Extract Schema and then create tables in the Extract Schema to land your data.
06_Packages_ExtractFlatFiles builds packages to read the flat files and write the data to those tables in the Extract Schema.
To create these packages and see the Biml used to create them, ctrl-click 00_GetOutput, 01_Connections-ADONET, 01_Connections-OLEDB, 01_FileFormats, 02_Connections-FlatFiles, 02_Databases, 03_Schemas, 04_Tables_FlatFiles, 05_Packages_CreateObjects, and 06_Packages_ExtractFlatFiles, then right-click in the highlighted area and choose to Generate SSIS Packages.
Output.biml will have all the biml used to create the packages that now appear in the SSIS Packages folder.
If you have any questions, please feel free to comment below. If you find any bugs, please submit those via GitHub.