Roundtrips between Mist and Visual Studio

If your primary SSIS development platform continues to be Visual Studio (BIDS or SSDT), but you would like to leverage Biml, BimlScript, Mist (from Varigence), and code Transformers, then it’s helpful to understand how to make roundtrips between Visual Studio and Mist.

Start by opening Mist 3.4, and using the helpful ‘Import’ feature. With this capability, you can load DTSX packages into Mist and have it auto generalte your Biml files. In this example, we’ll use a single package called: ‘DatabaseMetadata.dtsx loaded from Visual Studio’s project folder:

Once the package is imported you will have 3 ‘Biml’ files. One file for each of the connections, and one for the actual package:

You can double click any of the Biml files to view the contents. Note that 2 tabs automatically open in Mist. The first is a ‘form’ or ‘graphical’ representation of the object, while the other has the actual Biml code. When viewing the Biml code, notice how compact and readable it is when compared to a DTSX file:

As an interesting point of comparison, all three of the generated Biml files total only 161 lines of code. The original DTSX file in Visual Studio actually had 2109 lines of code as shown in part below:

Now that you are working in Mist, you can directly edit the Biml files, make changes using script based Transformers (see more at: http://bimlscript.com/Walkthrough/Details/68 ), emit (export) to other versions of BIDS or SSDT, and even work with external XML based tools.

In the event you have multiple SSIS packages, Mist Transformers are especially helpful. This is because you can make the same change to 2, 10, or hundreds of SSIS package using a single script. For example, let’s say you would like to add 2 variables to all of the packages within your Mist project. The following Transformer will do just that:

Once you are ready to move back to Visual Studio, you can right click the package file, and choose ‘Build’:

This step will create an ‘Output’ folder under the Mist project folder structure, containing a new Visual Studio project:

Alternately (instead of creating a new project), you can cut and paste individual Biml files directly into an existing Visual Studio project. To do this:

Create 3 Biml files in your BIDS or SSDT project. This can be done by right clicking the SSIS Packages container, and clicking Add New Biml File:

Note that if you do not see the “Add New Biml File” option, you’ll need to install ‘Bids Helper’ (available in CodePlex: http://bidshelper.codeplex.com/ ).

For ease of use, rename the 3 Biml files to reflect the logical names provided in Mist (note: they do not need to be exact as it’s just a mnemonic):

Copy and paste the contents of these 3 Biml files from Mist to the corresponding Visual Studio (Biml) files. At this point you can still work directly with the Biml files:

Now you are ready to re-generate the SSIS package. To do this:

Optionally rename the package inside the Biml file (otherwise, in this case, you would overwrite the original package in Visual Studio)

Hold down [Shift] and click on the 3 Biml files, and then right click the selection and choose ‘Generate SSIS Packages’:

If there is a package name conflict, Bids Helper will prompt you to verify that it’s ok to ‘overwrite’:

Note that the re-created package has been automatically re-drawn as it was re-created programmatically (i.e. no longer ‘hand-crafted’)

You are back where you started, but now with the potential to leverage Biml, BimlScript, Mist, and associated toolsets.

In conclusion, being able to ’roundtrip’ into the world of Biml and Mist provides many options for creating and maintaining SSIS packages. The more packages you need to manage, the greater the advantage when using the Biml related tools. Even if Biml is only a future consideration, the above exercise ensures that your SSIS packages are Biml compliant, and ready for the Biml ecosystem when you find it advantageous.