To see any real benefit of using BIML you will need to combine it with C# to generate dynamic packages. The only problem is, unless you fork out $1000’s for MIST then your only alternative is BIDSHelper. This is a great tool, but one of the biggest problems is that you loose IntelliSense for C#. Lately I’ve been using *LinqPad to prototype and test C# code which I then paste into my BIML files.
In this tip I’m going to show you how you can use LinqPad to speed up your BIML Scripting experience.
Just a quick note before we get started: To get the added benefit of IntelliSense in LinqPad you have to fork out $39USD for the premium edition, however as you will see this is definitely a worthwhile expense. Even if you don’t purchase the premium version you can still follow along, you’ll just miss out on the auto-complete. (I have not being paid for this endorsement).
Setting Up LinqPad
You can download LinqPad from http://www.linqpad.net/
Once you have installed and launched LinqPad you will see an empty query window.
To be able to use the BIML engine and Varigence extensions we’ll need to add a reference to the required assemblies and their namespaces. To do that, press F4, this will bring up the Query Properties window.
The first thing that you will need to do is add the assembly references. Click the Browse button and locate BimlEngine.dll (for me this is in C:\Program Files (x86)\BIDS Helper 2012). I also added a reference to WindowsBase.dll which you can do by clicking the Add button then searching for that assembly.
The next thing to do is setup the namespaces, click the “Additional Namespace Imports” tab, and paste the following:
1
2
3
4
5
6
7
8
|
System.Data
System.Data.SqlClient
Varigence.Hadron.CoreLowerer.SchemaManagement
Varigence.Hadron.Extensions.SchemaManagement
Varigence.Languages.Biml
Varigence.Languages.Biml.Connection
Varigence.Languages.Biml.Table
Varigence.Hadron.Extensions
|
Click OK to close the Query Properties window.
Language Modes in LinqPad
LinqPad gives you a few different options of writing C#.
I’ve found the most useful modes to write C# to be used with BIML is either the “C# Program” or “C# Statements” mode.
The “C# Program” mode is useful when you want to write different methods which can be reused in your BIML. Whereas “C# Statements” mode is useful when you just want to write some C# to directly paste into your BIML, you can’t break this into different methods.
Writing C# - Example 1
In this first example I’ll show you how you could use LinqPad to write some very simple C# code that will use the Varigence extension methods to query the metadata of your database.
This is useful in BIML when you want to generate either a number of different packages to perform over a series of tables, or multiple sequences in a single package over a series of tables.
Set LinqPad to use “C# Statements” mode and paste in the following code:
1
2
3
4
5
6
7
|
string connectionstring = "Data Source=.;Initial Catalog=AdventureWorksDW2012;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";
var targetConnection = SchemaManager.CreateConnectionNode("SchemaProvider", connectionstring);
IEnumerable<AstTableNode> sourceTables = targetConnection.ImportDB("dbo", "Dim%", ImportOptions.ExcludeViews).TableNodes;
sourceTables.ToList().ForEach(x=>x.Name.Dump());
|
This will simply use the SchemaManager and ImportDB extension methods provided by Varigence to dump the names of all the tables in the AdventureWorksDW2012 dbo schema that start with Dim
The next thing to do is make this a bit more useful, paste the following code:
1
2
3
4
5
6
7
8
9
10
11
|
string connectionstring = "Data Source=.;Initial Catalog=AdventureWorksDW2012;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";
var targetConnection = SchemaManager.CreateConnectionNode("SchemaProvider", connectionstring);
IEnumerable<AstTableNode> sourceTables = targetConnection.ImportDB("dbo", "Dim%", ImportOptions.ExcludeViews).TableNodes;
foreach (var table in sourceTables)
{
table.Name.Dump();
table.GetColumnList().Dump();
}
|
This will now loop over all the tables and this time dump the table name and then the column list.
These two snippets demonstrate how to get started using LinqPad.
We can now take the second snippet and plug it into a BIML file:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
|
<!--
<#@ import namespace="System.Data" #>
<#@ import namespace="Varigence.Hadron.CoreLowerer.SchemaManagement" #>
<#@ import namespace="Varigence.Hadron.Extensions.SchemaManagement" #>
<#
string connectionstring = "Data Source=.;Initial Catalog=AdventureWorksDW2012;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";
var targetConnection = SchemaManager.CreateConnectionNode("SchemaProvider", connectionstring);
IEnumerable<AstTableNode> sourceTables = targetConnection.ImportDB("dbo", "Dim%", ImportOptions.ExcludeViews).TableNodes;
#>
-->
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<OleDbConnection ConnectionString="Data Source=.;Initial Catalog=AdventureWorksDW2012;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" Name="Source"></OleDbConnection>
<OleDbConnection ConnectionString="Data Source=.;Initial Catalog=MyDW;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" Name="Target"></OleDbConnection>
</Connections>
<Packages>
<Package ConstraintMode="Linear" Name="Extract">
<Connections>
<Connection ConnectionName="Source"></Connection>
<Connection ConnectionName="Target"></Connection>
</Connections>
<Tasks>
<!--
<# foreach(var table in sourceTables)
{
#>
-->
<ExecuteSQL ConnectionName="Target" Name="Truncate Import Table <#=table.Name#>">
<DirectInput>
TRUNCATE TABLE Import.<#=table.Name#>
</DirectInput>
</ExecuteSQL>
<Dataflow Name="Copy data into Source <#=table.Name#>">
<Transformations>
<OleDbSource ConnectionName="Source" Name="Source - <#=table.Name#>">
<DirectInput>
SELECT
<#=table.GetColumnList()#>
FROM
<#=table.Name#>
</DirectInput>
</OleDbSource>
<OleDbDestination ConnectionName="Target" Name="Target - <#=table.Name#>">
<ExternalTableOutput Table="Import.<#=table.Name#>"></ExternalTableOutput>
</OleDbDestination>
</Transformations>
</Dataflow>
<!--
<#
}
#>
-->
</Tasks>
</Package>
</Packages>
</Biml>
|
This BIML script will then generate a package which will use the AdventureWorksDW2012 database as a source database and copy all the data from each of the Dim tables into an equivalent table in the Import schema of the MyDW database.
Writing C# - Example 2
In this example I will show you how you could use LinqPad to write a reusable methods which can then be in your BIML files.
The example that I will use is the GetSqlServerExtendedProperties method which was provided by Dave Stein (b | t), it simply queries the extended properties on a given table.
Set LinqPad to use “C# Program” mode and paste in the following code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
AstOleDbConnectionNode targetConnection;
void Main()
{
targetConnection = SchemaManager.CreateConnectionNode("SchemaProvider", "Data Source=.;Initial Catalog=AdventureWorksDW2012;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;");
var sourceTables = targetConnection.ImportDB("dbo", "Dim%", ImportOptions.ExcludeViews).TableNodes;
foreach (var element in sourceTables)
{
GetSqlServerExtendedProperties(element);
}
foreach (var element in sourceTables)
{
var skip = element.Annotations.FirstOrDefault (a => a.Tag.Equals("ETL_Should_Skip"));
if (annot.Text.Equals("1"))
{
//skip the table as it is marked to skip
continue;
}
else
{
}
}
}
AstNode GetSqlServerExtendedProperties(AstTableNode table)
{
AstNode returnValue = (AstNode)table;
var query = string.Format("SELECT name, value FROM fn_listextendedproperty(NULL, 'schema', '{0}', 'table', '{1}', NULL, NULL)", table.SchemaName, table.Name);
var extendedProperties = ExternalDataAccess.GetDataTable(targetConnection.ConnectionString, query);
foreach (DataRow ep in extendedProperties.Rows)
{
returnValue.AddAnnotation(AnnotationType.Tag, ep["value"].ToString(), ep["name"].ToString());
}
return returnValue;
}
|
This snippet will query the extended properties of all the tables in the dbo schema that starts with Dim and then loop through each of them. If it finds one called ETL_Should_Skip (lines 16 and 18) then it will skip it and go to the next one in the sequence.
This could be useful for example if you wanted to control which tables should go into your package without having this hard coded, or having to add a “control table” to your database.
The BIML would look like this:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
|
<!--
<#@ import namespace="System.Data" #>
<#@ import namespace="Varigence.Hadron.CoreLowerer.SchemaManagement" #>
<#@ import namespace="Varigence.Hadron.Extensions.SchemaManagement" #>
<#+
AstOleDbConnectionNode conn;
#>
<#
string connectionstring = "Data Source=.;Initial Catalog=AdventureWorksDW2012;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";
conn = SchemaManager.CreateConnectionNode("SchemaProvider", connectionstring);
IEnumerable<AstTableNode> sourceTables = conn.ImportDB("dbo", "Dim%", ImportOptions.ExcludeViews).TableNodes;
#>
-->
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<OleDbConnection ConnectionString="Data Source=.;Initial Catalog=AdventureWorksDW2012;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" Name="Source"></OleDbConnection>
<OleDbConnection ConnectionString="Data Source=.;Initial Catalog=AdventureWorksDW2012;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" Name="Target"></OleDbConnection>
</Connections>
<Packages>
<Package ConstraintMode="Linear" Name="Extract">
<Connections>
<Connection ConnectionName="Source"></Connection>
<Connection ConnectionName="Target"></Connection>
</Connections>
<Tasks>
<!--
<#
foreach (var element in sourceTables)
{
GetSqlServerExtendedProperties(element);
}
foreach(var table in sourceTables)
{
var skip = table.Annotations.FirstOrDefault (a => a.Tag.Equals("ETL_Should_Skip"));
if (skip != null && skip.Text.Equals("1"))
{
//skip the table as it is marked to skip
continue;
}
#>
-->
<ExecuteSQL ConnectionName="Target" Name="Truncate Import Table <#=table.Name#>">
<DirectInput>
TRUNCATE TABLE dbo.<#=table.Name#>
</DirectInput>
</ExecuteSQL>
<Dataflow Name="Copy data into Source <#=table.Name#>">
<Transformations>
<OleDbSource ConnectionName="Source" Name="Source -"
<#=table.Name#>">
<DirectInput>
SELECT
<#=table.GetColumnList()#>
FROM
<#=table.Name#>
</DirectInput>
</OleDbSource>
<OleDbDestination ConnectionName="Target" Name="Target -"
<#=table.Name#>">
<ExternalTableOutput Table="Import."
<#=table.Name#>">
</ExternalTableOutput>
</OleDbDestination>
</Transformations>
</Dataflow>
<!--
<#
}
#>
-->
</Tasks>
</Package>
</Packages>
</Biml>
<!--
<#+
AstNode GetSqlServerExtendedProperties(AstTableNode table)
{
AstNode returnValue = (AstNode)table;
var query = string.Format("SELECT name, value FROM fn_listextendedproperty(NULL, 'schema', '{0}', 'table', '{1}', NULL, NULL)", table.SchemaName, table.Name);
var extendedProperties = ExternalDataAccess.GetDataTable(conn.ConnectionString, query);
foreach (DataRow ep in extendedProperties.Rows)
{
returnValue.AddAnnotation(AnnotationType.Tag, ep["value"].ToString(), ep["name"].ToString());
}
return returnValue;
}
#>
-->
|
Conclusion
If you find yourself writing lots of C# code which you’re using within BIML then it is definitely work trying out LinqPad to see if it makes your life easier.
🍪 I use Disqus for comments
Because Disqus requires cookies this site doesn't automatically load comments.
I don't mind about cookies - Show me the comments from now on (and set a cookie to remember my preference)