This is a follow up post on the first post my colleague and owner (Louw Fouche) of sabigdata, wrote around BIML and generating SSIS packages. This article will focus on some of the insights we gained using BIML for Source to Staging SSIS package generation and give some use full tips. At the end of the article there will be url links to articles which will get you started in using BIML if you are not familiar with the language, concepts and syntax.
In the project we are involved in we needed to import +- 60 tables from four source databases into the staging area. We created a meta data table containing all the tables, column names, data type and lengths of all the columns. The below bullet points are some of the insights we gained.
- In the BIML script there are C# code to retrieve the meta data from the database and do some work with the dataset. Using a C# Console application made the debugging of the code much easier and I would definitely recommend it.
- There are some functionality available in the Visual Studio 2010 BIDSHelper version which is not available in the Visual Studio 2013 BIDSHelper version. Package parameters, Project Parameters and Project Connection Managers does not generate in VS 2013. As a workaround the packages were created in Visual Studio 2010 then deployed to the SQL 2014 SSIS.Catalog. You then create a VS 2013 SSIS project, choose Integration Services Catalog and import the SSIS packages into the development environment.
- We created a separate .BIML file containing some C# variables that is used by all the other BIML files. This is useful when you need to do the source to staging packages for more than one database. The BIML scripts are more generic and you only need to change some C# variables and then generate packages for different sources.
Using BIML saved us a lot of time on the project plan and you can you can easily add more functionality and quality to your SSIS packages.
Follow these links to get started using BIML :