Diesen Artikel finden Sie hier auch in deutscher Sprache.

Now, that we’ve seen multiple ways on how to use Biml to create SSIS packages that will generate and populate tables, we’ll try to bridge the gap on how to use Biml for packages that will execute stored procedures and other tasks for us. We’ll use a metadata-model for that again, though it will be very minimalistic. This approach makes it very easy to maintain – we’re actually using it in exactly this way in multiple projects.

To get started, let’s create a new database “MyBiml_SimplisticMetaData” and create two tables, a view and some sample data in there:

USE [MyBiml_SimplisticMetaData]
GO
CREATE TABLE [dbo].[MyBimlMeta_Connections](
	[Connection] [nvarchar](50) NULL,
	[Type] [nvarchar](50) NULL,
	[Server] [nvarchar](50) NULL,
	[Database] [nvarchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MyBimlMeta_Packages](
	[Package] [nvarchar](50) NULL,
	[Tier] [int] NULL,
	[StepName] [nvarchar](50) NULL,
	[StepType] [nvarchar](50) NULL,
	[Connection] [nvarchar](50) NULL,
	[Task] [nvarchar](50) NULL,
	[Parameter_1] [nvarchar](50) NULL,
	[Parameter_2] [nvarchar](50) NULL
) ON [PRIMARY]
GO
CREATE VIEW [dbo].[vMyBimlMeta_Packages]
AS
	SELECT Conn.Connection
		, Conn.[Database]
		, Pack.Package
		, Pack.Tier
		, Pack.StepName
		, Pack.StepType
		, ISNULL(Pack.Task, N'') AS Task
		, ISNULL(Pack.Parameter_1, N'') AS Parameter_1
		, ISNULL(Pack.Parameter_2, N'') AS Parameter_2
	FROM dbo.MyBimlMeta_Connections AS Conn
	INNER JOIN dbo.MyBimlMeta_Packages AS Pack
		ON Conn.Connection = Pack.Connection
GO
INSERT INTO [MyBimlMeta_Connections]
SELECT 'OLAP','OLAP','MyDWHServer','MyOLAPDB'
INSERT INTO [MyBimlMeta_Connections]
SELECT 'DWH','OLEDB','MyDWHServer','MyDataWareHouse'
INSERT INTO [MyBimlMeta_Packages]
SELECT 'JustOneStoredProc',1,'SP 1','SP','DWH','SP_1','100',''
INSERT INTO [MyBimlMeta_Packages]
SELECT 'FullProcess',1,'SP 1','SP','DWH','SP_1','100',''
INSERT INTO [MyBimlMeta_Packages]
SELECT 'FullProcess',1,'SP 2','SP','DWH','SP_2','''A'',20',''
INSERT INTO [MyBimlMeta_Packages]
SELECT 'FullProcess',2,'SP 3','SP','DWH','SP_3','5',''
INSERT INTO [MyBimlMeta_Packages]
SELECT 'FullProcess',3,'Process SSAS','OLAPDB','OLAP','','',''

Our metadata consists of two components:

1. Connections
This is where we define our database connections, using the following properties:

Connection: The name to be used for the connection
Type: Type for the connection – our Biml code currently supports OLEDB and OLAP. Feel free to extend that to fit your needs!
Server: Name of the server (or instance) to be used
Database: Name of the database

Based on that table, we’ll use a Biml script which will simply loop over that table and create our Biml Connections:

 <#@ template language="VB" tier="1" #>
<#@ import namespace="System.Data"  #>
<# Dim ControlConnectionString as string = "Provider=SQLNCLI11;Server=Localhost;Initial Catalog=MyBiml_SimplisticMetaData;Integrated Security=SSPI;" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Connections>
		<OleDbConnection Name="Control" ConnectionString="<#= ControlConnectionString #>"/>
		<# For Each dr As datarow In ExternalDataAccess.GetDataTable( ControlConnectionString, "Select * from MyBimlMeta_Connections").rows 
		if dr("Type") = "OLEDB" then #>
			<OleDbConnection Name="<#=dr("Connection") #>" ConnectionString="Provider=SQLNCLI11;Server=<#= dr("Server") #>;Initial Catalog=<#= dr("Database") #>;Integrated Security=SSPI;"/>
		<# elseif dr("Type")  = "OLAP" then #>
			<AnalysisServicesConnection Name="<#=dr("Connection") #>" Server="<#= dr("Server") #>" ConnectionString="Data Source=<#= dr("Server") #>;Initial Catalog=<#= dr("Database") #>;Provider=MSOLAP.6;Integrated Security=SSPI;Impersonation Level=Impersonate;"/>
		<# end if
	   Next	#>
	</Connections>
</Biml>

Now it’s time for our second component:

2. Packages
This is, where we define which tasks we want to execute, in which package and in which order:

Package: Name of the package – all entries sharing the same package name will be executed in the same package
Tier: Tier/grouping of the task within the package – all tasks within the same tier will be executed in parallel
StepName: Name of the task
StepType: Type of the task – our example support processing of SSAS (Type: OLAPDB) as well as execution of stored procedures (Type: SP)
Connection: Reference to a connection from our connection metadata table
Task: Depends on the type, might be the name of the stored proc for example
Parameter_1: optional parameter, for example to be passed to a stored procedure
Parameter_2: not being used in our example but you might need it for other tasks

Based on that, we’ll create another Biml file which will…

– Create a package for each distinct “package” entry in that table
– Create a container for each distinct “tier” entry for that package
– Create a task for each entry in that tier

The containers will be run linear but the tasks within a container will run in parallel. Container 2 will therefore not be executed before container 1 has finished, all tasks within container 1 will run at the same time though.

 <#@ template language="VB" tier="2" #>
<#@ import namespace="System.Data"  #>
<# Dim ControlConnection  as AstDbConnectionNode = rootnode.connections("Control") 
   Dim ControlConnectionString as string = ControlConnection.Connectionstring #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
		<# For Each Package As datarow In ExternalDataAccess.GetDataTable( ControlConnectionString, "Select distinct Package from [vMyBimlMeta_Packages]").rows #>
		<Package Name="<#= Package("Package") #>" ConstraintMode="Linear">
			<Tasks>
			<# for each tier as datarow in ExternalDataAccess.GetDataTable( ControlConnectionString, "Select distinct tier from [vMyBimlMeta_Packages] where package = '" &  Package("Package") & "' order by tier").rows #>
				<Container Name="Tier <#= tier(0) #>" ConstraintMode="Parallel">
					<Tasks>
					<# for each task as datarow in ExternalDataAccess.GetDataTable( ControlConnectionString, "Select * from [vMyBimlMeta_Packages] where package = '" &  Package("Package") & "' and tier = " & tier(0)).rows #>
					 	<# if task("StepType") = "SP" then #>
							<ExecuteSQL Name="<#= task("StepName") #>" ConnectionName="<#=task("Connection") #>">
								<DirectInput>
									EXEC <#= task("Task") #> <#= task("Parameter_1") #> 
                                </DirectInput>
							</ExecuteSQL>
						<# elseif task("StepType") = "OLAPDB" then #>
							<AnalysisServicesProcessing Name="<#= task("StepName") #>" ConnectionName="<#= task("Connection") #>">
								<ProcessingConfigurations>
									<DatabaseProcessingConfiguration DatabaseId="<#= task("Database") #>" ProcessingOption="ProcessFull"/>
								</ProcessingConfigurations>
							</AnalysisServicesProcessing>
						<# end if #>
					<# next #>
					</Tasks>
                </Container>
			<# next #>
			</Tasks>
        </Package>
	    <# Next	#>
</Packages>
</Biml>

This leaves you with a solid basis on how to manage your processing tasks in SSIS using metadata and Biml. You can easily add other task-types (like processing of dimensions and partitions, backup operations etc.) by slightly extending the code.

Any questions or comments? We’d love to hear from you at !

Further Information on Biml in English can be found on our English Biml Page.

Happy Biml’ing!