11 May 2017

Create SSIS Packages with BIML

The BIML (Business Intelligence Markup Language) allows the automatic creation of SSIS identical packages by simplifying the set of repetitive tasks performed in the extraction process of different source systems. Thus, it reduces the time of extraction of the sources and increases coherence between all packages. Four phases can define this process:

>     Designing template package phase;

>     Creating static BIML file phase;

>     Loading metadata phase;

>     Creating dynamic BIML file phase.

These phases depend on the use of four tools:



1.1.  Designing template package phase

Although there are different design methodologies, this stage is essential to adapt the way of drawing. The flexible result is set to the desired extraction. It helps the Visual Studio by creating the design and all the package template settings. You must pay attention to the following points:

>     Identify all changes that are performed when manual repetition occurs;

>     Create variables to simplify the dynamics of support, if necessary.

1.2.     Creating Static File BIML Phase

This phase consists of two sub phases. First, the mist is used to convert the package dtsx in BIML code and then link together BIML different files into a single file. Concluded these two sub phases, it is important to place the output file in Visual Studio and search for faults in order to reduce any faults in the code.

1.2.1.      MIST

>     Create a new project



>     Import template package



>     View information generated on the package loaded



1.2.2.      Connection / Link



>     Link the different source TXT MIST

>     Check for errors in the static code BIML



1.3.     Loading metadata and tables phase

One can resort to excel to create a file that features the packages and their content in order to extract through different parameters. Thus, it is important that all dynamics that have been identified in Phase 1 are mirrored in the metadata file loading. In the case of extraction txt files, for example, you must take into account all the settings for connections associated with SSIS connectors for this type of source.



The main features are:

>     Create BIML configuration tables;

>     Describe characteristics of sources;

>     Generate insert commands and create in order to optimize the loading of files and quickness of the process.

1.4.     Creating dynamic BIML file phase

This phase is used in Visual Studio to modify the BIML file manually:

>     Add code (C # .NET) to create dynamics;

>     Place the variables C # in the parts that have to be changed from package to package;

>     Generate dynamic BIML packages;



1.5.     Pratical Example

>     Tables with data packages (SQL) loaded through Excel data



>     Tables with data from the information packages (SQL) loaded through Excel data



>     Generate packages



>     Result after creating packages

Results of the two packages created by the BIML



Derived Column result of the two packages as an example of dynamic changes that are parameterized by dynamic insertion (C #)




1.6.     Conclusion

In summary, here you can see a scheme of packages execution BIML model:


     Rita Grencho

       Hugo Silva