SSIS data flow are very powerful and can sometime be very complex and long. Therefore, like any other programs, we would like the ability to comment sections of the data flows. This post will describe various way to d this.

Annotations

The obvious way to add comments or notes is to use annotations. Annotations can be used everywhere in a package, control flow, data flows and event handlers. There are efficient and easy to move around everywhere in the package.

Figure 1

Now, one feature I like a lot in SSIS data flows is the ability to re-arrange data flows objects by using the “Format”à“Auto Layout”à“Diagram” command from the top menu. This align objects correctly most of the time.

Figure 2

Now, when it comes to annotation, it doesn’t work properly. As you can see in Figure 3 below, the annotation has moved at the bottom of the package. If we had used many of these annotations, how can we know what transform(s) they belong to? As of now, there’s no way to tie an annotation to specific transforms. There is a connect issue that was closed in 2010 that relates to this issue.

Figure 3

Objects grouping

Since SSIS 2012, we now have the ability to group objects in a data flow. We can therefore group the annotation with the lookup and get something similar to Figure 1 above. This gives us something like Figure 4 below:

Figure 4

Then, using the “Format”à“Auto Layout”à“Diagram” command from the menu will also format the grouped objects but at least, they are still grouped. However, the group will now be moved to the bottom of the data flow.

Figure 5

So, we’re now back to square one basically.

Path annotation

Another option we have and it might be useful is to use the annotation property of the various paths. The regular paths underneath the lookup have “Lookup Match Output” assigned by default.

Figure 6

This is due to the fact that (as shown in Figure 6), the “PathAnnotation” property is set to “AsNeeded” and by default, it displays the “SourceName” property. We can’t change the “SourceName” property, it is read only. But we can change the “Name” property though.

In Figure 7 below, I changed the “Name” property and set the “PathAnnotation” property to “Name”.

Figure 7

Now, instead of having “Lookup Match Output”, we have a more descriptive path annotation.

 

Objects “Description” property

Another way of commenting our data flow transforms or control flow tasks is to use the “Description” property of every objects you want to comment on. To see the content of the “Description” property, we have to hover with over the object we want to see the description as shown in Figure 8.

Figure 8

By default, we have “Look up values in a reference dataset by using exact matching.”. We can change this using the property pane. Therefore, we’ll now see whatever we typed in the description of the object.

Figure 9

This might be useful to see what’s happening in the designer but the downside is that we can’t really know if a comment exists unless we hover the mouse over it.

To sum up

As useful as annotations are, they cannot be tied to a specific task or transform. So every time the task or transform moves, we have to carry the annotation manually.

Grouping helps but it takes significant space on the package.

Path annotation gives us more flexibility because we can see the annotation on the package, it is tied to it. Also, the name can be referred in the execution reports and log because we changed the name of the path. Still, it looks like a hack though.

Description is another option but are only visible when we hover the mouse on the object.

If Microsoft could let us tie annotation to a specific object, it would resolve many issues we talked in this post. Let’s hope they fixe it in future versions.

Bonjour, ne manquez pas le SQL Saturday à Montréal le 21 novembre prochain ! Voici le lien pour vous y inscrire : http://www.sqlsaturday.com/466/eventhome.aspx

L’inscription est gratuite. Des frais de 10$ peuvent s’appliquer si vous désirez manger sur place

L’agenda est disponible en suivant ce lien : http://www.sqlsaturday.com/466/sessions/schedule.aspx

Hi, don’t miss the Montreal SQL Saturday this coming November 21^st! Here is the link to register: http://www.sqlsaturday.com/466/eventhome.aspx

Registration is free, there’s an optional fee (10$) for the lunch if you’re interested to eat at the event location.

The schedule for the event is available there: http://www.sqlsaturday.com/466/sessions/schedule.aspx

Azure Data Factory (ADF) is always improving. This post will talk about how the editor has improved since its introduction.

SHIFT+F10, get the help menu

Pressing SHIFT+F10, the following menu appears.

From the image above, you can see that several commands are available from this menu:

• Go to Symbol: displays a list of elements in the JSON that you can access when selecting an element from the list.
• Change All Occurrences: as it indicates, will change all occurrences of a specific element that you selected.
• Format Code: this command will correct indentation and align the code correctly for you. If you close an artifact and re-open it, the code formatting is applied de facto.
• Command Palette: this command will display the available commands in ADF. Even though the shortcut key is F1, it doesn’t have the same behavior as on-premises program; it will not open another browser or tab in the browser that displays the documentation. It only provides a list of commands.

Go to Symbol

This command allows you to navigate faster inside the JSON document. Press SHIFT+F10 and choose go to symbol from the menu.

A list of objects present in your JSON document appears:

You can see in the upper right corner of the sub-menu how many symbols your document has. Now we’ll navigate to an element “Availability” in the document:

Using the keyboard down arrow, when we navigate in the list, the specific symbol is highlighted. Now, clicking on it with the mouse or pressing enter on the keyboard will move the cursor to the symbol:

Notice that the symbol is selected and its definition or contextual help is displayed above it.

 

Change All Occurrences

This command allows you to rename all occurrences of a specific symbol.

In the example below, we’ll change all “name” symbols to “test”. It doesn’t make sense to do it in the real life but I do it only to show you how the command would work.

In a real world scenario, you would use this command to rename multiple occurrences of structure element’s name.

Format Code

This command I really simple, it will reset the code formatting to make it more readable. For example, let’s say we mess a lot with our structure elements.

Using “Format Code” command will re-format the JSON structure:

 

Command Palette

This feature is a kind of help since it shows you all available commands of the editor.

As you can see, there’s a textbox at the top that allows you to search for specific command. I typed the letter t and all commands that contained that letter are now displayed.

IntelliSense

Now, let’s see how we can use intelliSence in ADF. Like any .Net language, intellisense can be triggered by typing CTRL+Spacebar. For example, when we type structures in datasets using the ADF editor, we simply have to type: na without completing the word to “name”. Press on TAB and voila, itelliSense corrects the tag for us.

When it comes to the datatypes, starting to type the name of the datatype will make a list of datatypes containing what we have just started to type.

So now it’s easier to create our structures in datasets. Structures are important for several reasons and I’ll explain them in a future post.

Validation and help

The editor also validates as we are typing. For example, we need to have a type in a structure of a dataset. Failure to provide one will lead to error when we’ll try to publish the dataset. In the image below, I highlighted the uncompleted structure element.

In the case above, it’s clear that we have to provide a valid data type for the element.

 

Another example is when external policies are used in the dataset. If one of the policies is not correctly provided, ADF editor will show a warning (green underline) where there’s missing or non-conform data.

As shown in the above screen clip, we are provided of what the property should look like and the description of the property.

To sum up

Azure Data Factory editor has gone a long way since i’s introduction at the end of February. There are remaining caveats like the fact that intelliSense won’t list user created objects in various artifacts. For example, in a pipeline, it would be nice that the input or output of an activity:

In the above code, I highlighted what I would like the editor integrate in intelliSense; the capability to list available datasets (in that case AdventureWorksLTDW-Dataset). We don’t have this possibility for the moment. Probably in the future though. Anyway, I hope so J.

Anyway, I’m sure that everyone that uses the editor will be more productive with these enhancements.