How to get list of output columns inside BIML script - column based generation

2
votes

I am working on migrating existing SSIS package generation framework to BIML, and faced with the task of migrating the following logic.
The framework (written in C#) creates an OLE DB Source based on metadata. Then, after instantiating the Data Source, it looks through output columns. If certain columns are present - the framework injects specific transformations like Lookup etc.

Example of that logic - OLE DB Source uses query select quantity, salesdate, prcode as productid from salesfact. Then code looks up for productid column and checks whether it has int data type. If this is the case - adds Lookup, which is defined by the lookup column ProductID, has fixed lookup query/cache and provides new column DimProductID.

The approach is quite dynamic, because OLE DB Source can have SQL Select statement from variable or with parameters. It runs correctly, doing an Instantiate() call of OLE DB Source provides necessary columns in transformation metadata.
Question - can such dynamic approach be implemented in BIML? If yes - any insight or examples would be great. For now BIML seems fully declarative (i.e. all metadata has to be defined somewhere before BIML script run).

1
c#ssisbiml
To help my brain "gel" on this, assuming something like a fact load, and the query had select quantity, saledate, productcode from table, the current system would see the source component metadata has character data types and "know" that a lookup is required to translate that to a dimension key. The lookup table could be derived based on the text of the column name. Something like that? – billinkc
@billinkc, You are right, added example of the logic in the text above. – Ferdipux

1 Answers

1
votes

Having done some study I concluded that BIML works differently from our generic approach.
BIML actions in generating SSIS packages can be divided onto two phases:

  • BIML generation phase - when all C# code bits modify the template BIML. It is named BIML Expansion, takes BIML with C#/VB nugets and produces plain BIML text. Note - this phase can be absent if you have static BIML (which makes little sence).
  • SSIS Package generation based on BIML. BIML text is solid here, no dynamics, it is all worked on the first phase.

Note that BIML expansion is done offline, i.e. you may not have connection to DB which is used in SSIS Package. Generating SSIS Package based on BIML requires DB connection, or you have to use BIML offline schema.

On the contrary, SSIS generation with MS SSIS API is fully online. Adding each Task or Transformation instantiates such object, contacting DB. For OLE DB Source - a connection to the DB is made, and a list of column names with its format is retrieved. Working 'online' allows you to retrieve list of columns (i.e. metadata) on the fly and be quite easy and flexible on the metadata.
BIML dictates that you should know all the metadata somehow on the BIML generation phase, when you can control what goes to BIML code/package.

Back to the original question - we decided to restrict flexibility. The metadata describes SQL queries used to get the data with optional parameters. Added a C# module which connects to the DB and gets a list of columns with its format, one receives metadata needed for logic and conditional BIML.