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 different steps you’d need to take in order 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 the repository from https://github.com/shannonlowder/Biml.git.
Setting up the BimlExpress Project
Using the Interrogator in BimlExpress is different than any other example you’ve ever seen. I wanted to share code between my BimlExpress and BimlStudio projects. My solution was to use symbolic links. Since setting symbolic links will require Administrative privileges, please open and review the code in <repository>\Interrogator\BimlExpress Project\CreateSymbolicLinks.bat. You’ll see this batch file requires one argument. This argument is the path to the Interrogator code.
In my case, I store all my repositories to C:\Repositories. So the path to the Interrogator code for my machine is C:\Repositories\Biml\Interrogator. Do not include the trailing slash.
You’ll see the batch file uses this part of the path to set up the links based on your environment. You create links to all the biml files, and then a link to the code folder where the cs files live. After running this script you’ll be able to work with the interrogator in BimlExpress. Any changes you make to the files are shared with the BimlStudio project. This is how I can work in either environment and then change to the other and not have to code the same thing twice!
Now, to set up these links for your development environment, open a command prompt in Administrative mode. change to <repository>\Interrogator\BimlExpress Project and run CreateSymbolicLinks.bat “<repository>\Interrogator”
You’ll see the links created successfully on screen. After that, close the command window, and open <repository>\Interrogator\BimlExpress Project\BimlExpress\BimlExpress.sln.
BimlExpress Project Overview/ 00_GetOutput.biml
If you’ve seen the layout in BimlStudi
o, the BimlExpress Solution’s Miscellaneous folder will look similar to the Utilities folder in BimlStudio. There are two exceptions. The code files appear in the Miscellaneous folder, rather than in a code folder, and you have two extra Biml files: 00_GetOutput.biml and output.biml.
The extra two Biml files are helpful in seeing what is going on between the Biml code and the emitted Project Connections and Packages. 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.
00_Configuration.biml
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 in search of 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 are going to set up connections in the Relational folder we’ll use in subsequent steps. When the Biml compiler goes to reference 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 as well as 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 as well 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
In order to create Packages that read those flat files and write the output to a table, you have to build references to your database, schemas and tables. The code to build 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 to 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 Generate SSIS Packages.
Output.biml will have all the biml used to create the packages that now appear in the SSIS Packages folder.
That’s it!
If you have any questions, please feel free to comment below. If you find any bugs, please submit those via GitHub.