Table Partitioning Webinar for Pragmatic Works

Pragmatic WorksPragmatic Works hosts and records free webinars every Tuesday and Thursday, called Free Training on the T’s. In March 2015 they wanted to highlight Women in Tech (WIT) by hosting nine webinars with speakers from the SQL Server community. I was very honored and excited to be invited, and a little nervous since it was my first ever webinar.

The recording of my webinar Table Partitioning in SQL Server: A Magic Solution for Better Performance? is now available, and the slide deck is available on my SlideShare:

The introduction was not included in the recording, but it can be summed up as “thank you to Pragmatic Works for supporting WIT, thank you for attending my webinar, my name is Cathrine and this is an introduction to table partitioning” :) There were some technical issues so the sound is missing for about a minute in the middle of the webinar, and I couldn’t see any questions in the chat. To all of you who asked questions, I’m sorry for not answering during the webinar, but I have now received the transcript and will answer your questions in upcoming blog posts.

Upcoming blog posts
Ed Leighton-Dick (@eleightondick) recently announced a #SQLNewBlogger challenge where those who participate will write one blog post every Tuesday in April. I have decided to participate in this challenge, and I encourage everyone else to do the same! :) I got a lot of great questions during my webinar that I want to answer in more detail with scripts, step-by-step instructions and pictures, so I have picked Table Partitioning as my topic for the challenge. This post will be updated with links when the new posts are published, so check back every Tuesday in April if you want to learn more about Table Partitioning basics! :)

Biml Scandinavian Tour 2015

Biml Scandinavian Tour 2015
Read the Varigence Blog Post

In my previous post I wrote about the recent Biml updates and that 2015 is such an exciting year for everyone using Biml. And it gets better! Varigence just announced the Biml Scandinavian Tour 2015, YAY :D

There are several events in Denmark and Sweden, and these events in Oslo, Norway:

Thursday, May 7th, 14:00-16:00: Biml Afternoon Tea

Thursday, May 7th, 17:30-19:30: Biml in SQL Server User Group Norway
I’m very happy to announce that Scott Currie will be back to talk about Biml to SQL Server User Group Norway! This is such an amazing opportunity to learn about Biml if you’re not using it yet, or to ask and discuss with Scott if you’re already using Biml. We bring the pizza and you bring your friends and coworkers :D

Friday, May 8th, 09:00-17:00: Biml Accelerator Class
Business Intelligence Markup Language (Biml) automates your BI patterns and eliminates the manual repetition that consumes most of your time. During this full day workshop Scott Currie from Varigence and Peter Hansen from Rehfeld will show you how to get the most out of Biml. First they’ll introduce you to the Biml language. Then using BimlScript they’ll demonstrate how to automate your BI development. Finally, they’ll show advanced Biml features that culminate in the construction of a full-blown, production quality, reusable BI framework. Productivity tips and tricks will be covered throughout the day. (If you attended the Biml Masterclass in 2014, you can arrive after 12 to learn about the advanced framework and new Mist 4.0 features.)

Friday, May 8th, 18:00-20:00: Biml over Beers
During this two hour event there will be a short presentation of Biml by Scott Currie from Varigence followed by an informal discussion of BI and Biml – over beers! :)

Is it May yet? Being the huge Biml fan that I am, I’m so excited! I hope to meet as many people as possible during one or more of the events. Please come and talk to me, I would love to hear more about how others use Biml! :)

Biml updates in BIDS Helper 1.7.0 and Mist 4.0

2015 is such an exciting year for everyone using Biml! Varigence recently released version 4.0 of Mist, and now version 1.7.0 of BIDS Helper is released. Both products have implemented the changes to the Biml language, and they have both been upgraded with new features and improvements. Only some of the changes are mentioned in this post, but you can find the full release notes here:

Bids Helper 1.7.0 Downloads and Release Notes
Mist 4.0 Release Notes
Mist 4.0 Update 1 Release Notes
Mist 4.0 Webinar Recording on YouTube

Biml language highlights
Nested included files now work.

ConstraintMode is no longer a required attribute on SSIS packages, but the default has been set to Parallel so you will still need to specify the ConstraintMode if you want to create a Linear package:

Hadron is no longer used, and all namespaces that included “Hadron” have 1:1 replacements with “Biml”:

New code directive to reference C# or VB files directly from BimlScript:

New Change Data Capture tasks and components: CDC Control Task, CDC Source and CDC Splitter:
CdcControl CdcSource and CdcSplitter

BIDS Helper 1.7.0 highlights
Properly supports emitting SSIS 2014 packages in SSDTBI for Visual Studio 2013!

Shows a progress bar when Checking Biml For Errors or Generating SSIS Packages
Checking Biml For Errors

More useful errors dialog and brings the Output window to the front:
Changed Hadron to Biml

Mist 4.0 highlights
New Metadata Modeling! (Mist 4.0 Metadata Preview on YouTube)

True Multiple Document Interface (MDI), multiple editor windows can be open at the same time (yay tab groups!):
Multiple Document Interface (MDI)

Logical View and Ribbon revamp for more intuitive grouping:
Mist 4.0 Logical View Ribbon

New designerbimlpath attribute in the template directive so intelliprompt works correctly on code fragments like callable BimlScripts or include files:

Improved intelliprompt completion lists and option to toggle content:
Intelliprompt Toggle Extension Methods

…and much, much, much more, YAY! :) Read and/or see all the changes here:

Bids Helper 1.7.0 Downloads and Release Notes
Mist 4.0 Release Notes
Mist 4.0 Update 1 Release Notes
Mist 4.0 Webinar Recording on YouTube

Volunteering and Speaking at SQLBits XIV

SQLBits XIV Superhero Edition was my first SQLBits experience, and it was a truly amazing week!

I want to start with a massive thank you to the organizers: Darren Green, Simon Sabin, James Rowland-Jones, Chris Testa-o’neill, Allan Mitchell, Jonathan Allen and Annette Allen. You guys did one heck of a job to make this the best conference in the universe! :) I also want to say thank you to all the sponsors, volunteers and speakers who made this a great event. And to all the attendees, I hope you had a blast and that I’ll see you again next year!

Continue Reading →

Don’t Repeat Your Biml – CallBimlScript with Parameters

Some of the main advantages of using Biml to generate SSIS packages is that you can save time and avoid doing the same tasks over and over and over again. You don’t want to manually update your SSIS packages one by one, and you should also follow the Don’t Repeat Yourself principle in your Biml projects:

  • Move common code to separate files
  • Centralize and reuse these files in many projects
  • Update code in one file to make changes to all projects

There are four main ways you can achieve this:

  1. Split and combine Biml files
  2. Include files
  3. CallBimlScript with Parameters
  4. Transformers (Mist feature only)

In this post I will show you how to use CallBimlScript with parameters.

CallBimlScript with Parameters
If you come from the world of SQL, you will see that using CallBimlScript is similar to using a stored procedure. If you come from the world of programming, you will see that CallBimlScript is similar to a method or function, but with some differences.

The Biml file with CallBimlScript (the caller) passes parameters to another Biml file (the callee). The callee can then use these input parameters as regular variables and to control logic. Below is a very simple example of a caller and a callee:

You use an Expression Control Block (that starts with the equal sign <#= ... #>) when you want to use CallBimlScript. Expression Control Blocks first parse the content inside the block as a string, and then the Expressian Control Block is replaced by that string. It is therefore important that the callee only contains the code that can replace the Expression Control Block. In the example above, CallBimlScript is used inside <Packages></Packages>, so the callee must only contain <Package> tags.

One of my favorite things about CallBimlScript is that you are not limited to using simple parameter types like String, Int32 or Boolean. You can pass all kinds of parameters! Just think about the cool solutions you can create by passing parameters such as AstTableNode, List<> or DataTable :)

In BIDS Helper you only need to right-click on Caller.biml and click Generate SSIS Packages, and it will locate and use Callee.biml. If the callee is located outside your project, you will have to use an appropriate file path.

Example: CallBimlScript to create Packages
Below is an extended example where I have created a callee that creates a Truncate / Load package. It accepts four parameters: AstTableNode, source connection, destination connection and destination schema. I can reuse this callee in all my projects where I Truncate / Load tables, without having to copy and paste the actual code. If I want to change the pattern, for example if I want to add a second derived column, I will only have to change it in that one callee Biml file. The CallBimlScript specific code lines are highlighted:

Example: CallBimlScript to control SSIS Logging
Below is an extended example where I have created a callee that accepts a single parameter called LogType. Based on the value of the LogType parameter, the SSIS package will log some or all events. This logging code is particularly verbose (I have shortened it in this example) and I absolutely do not want to copy, paste and maintain it in multiple files. With CallBimlScript, it is super easy to change! I can just change the value of the LogType parameter from “LogErrors” to “LogAll”, regenerate my SSIS packages, and all the logging will be updated :)

Page 1 of 13 1 2 3 4 5 ...10 ...Last »