I have hundreds of BIML scripts and I have to convert each into SSIS package. The only process I figured out is to manually right click the biml file and convert it click the Generate SSIS Package. (Please follow the link to visualize it). How do I automate this process. In other words, how can I programmatically convert all the biml scripts into their corresponding SSIS packages..
Is there an automation tool to convert BIML to SSIS Package?
163 Views Asked by PrasBas At
2
There are 2 best solutions below
0
On
There are 3 tools for transforming Biml into DTSX packages: BimlExpress, BimlStudio (formerly known as Mist) and the precursor to BimlExpress - BidsHelper. That product no longer exists and the Biml bits in it and has been rebranded.
Under the covers, BimlStudio is going to invoke Bimlc.exe which is the Biml Compiler and that is how scripts become packages. Buy it outright or rent it monthly, depending on your needs. This is your only choice for unattended/automatic/automated builds.
BimlExpress is the free tool that can also transform scripts into packages. It requires mouse clicks to build packages.
The big difference between the two, for the beginner at least, is convenience. I have ScriptedPackageA and ScriptedTablesB which makes Package1. In BimlStudio, I can set the properties so that one is live (tables) and just evaluate/expand the Package script. In BimlExpress, I need to shift/control click the scripts I want to be compiled/referenced.
Also if you have hundreds of Biml scripts... you might not have understood the idea behind Biml. For reference, I have about about 7000 .biml files on my machine but I bet I have less than 30 describing package patterns. The only way I'm at my large number of Biml files is that I have scripted a number of databases with an file per table.
Generally speaking, you want to distill your approach down to distinct patterns and then throw your metadata against it. How many ways can you have a package that loads from file to database?
You should be able to select multiple files, right click and generate them all at the same time.
You can also reference one biml script from another. So you can have your main entry point which contains a
<packages>element and then reference other scripts within that which define each package.Finally, if you have biml studio, this comes with a command line utility which would allow you to do it programmatically.