by Audrey and Julie
Summary: The BimlScript methods of Business Intelligence Markup Language (Biml) will only work (ie access SQL metadata) with SQL Server versions 2005 and higher. This article briefly tells the story of how Innovative Architects worked around this limitation for one of our projects and successfully tricked Biml by creating system views in SQL Server 2000. They ddl script for the views is found in a link at the end of the post.
Audrey and I have been using Biml at a client site for almost a year (and by Audrey and I, I mean mostly Audrey). The project is for an Enterprise Data Warehouse and what Audrey has designed is a metadata driven solution that will stage the data with SSIS. The solution reads the metadata supplied by Business Analysts and creates and runs the SSIS packages accordingly each time they are run. It’s pretty slick and naturally the client wanted to use it on all of their platforms (which were many and varied–SQL Server, Oracle, Flat Files, etc).
A large number of the SQL Server instances were of the 2000 variety and we discovered to our disappointment (if only we’d read the manual!) that Biml doesn’t natively work with SQL Server Versions below 2005. The errors we got went like this:
sys.columns is a system view implemented with SQL Server 2005. I reached out to Biml’s creator, Scott Currie of Varigence.com and asked him if there was a way to cheat the Biml by manufacturing this view and any others that Biml might need in the SQL Server 2000 database. Scott believed that we probably could. Based on the errors we received and also a list from Scott of views he knew that Biml would be querying, we came up with this list of views we needed to generate:
We then divided and conquered creating the views. We researched these views’ metadata from the post 2000 instances we had, and sought to find the data from what is available in the SQL Server 2000 instances, which wound up being mostly the tables sysobjects and sysindexes. Many of the columns in these views we were creating did not seem to be incredibly important for SSIS Package creation, so often we used default static values for a lot of the columns. For example, a column in one of the views was named [uses_database_collation]. Instead of trying to find the actual answer in the 2000 system objects, we just used convert(bit, 0) and let the value be always false for the views we were creating. We just put a stub value in for columns that didn’t seem relevant to our needs or the needs of Biml. The point was to get the view objects there so Biml would not error out and stop.
We deployed the views and on our first attempt to run our solution against SQL Server 2000 sources, we still got one more error. We needed to create one more view: the [sys].[computed_columns] view had been missed on our first list. Once we created that final view, the Biml worked and our SQL Server 2000 databases were cranking out packages just like their older brothers and sisters. Thus far, we have not encountered any other problems with the packages coming from Biml using these views.
Here is a link to a script with the SQL to create your views: CLICKME Pay attention to the comments in the script regarding how to handle future upgrades.