Saturday, 31 October 2015

SQL Server 2016 CTP 3.0 New Control Flow Templates

A long long time ago (2008) there was a connect request for Package Parts to make it easier to reuse certain parts of a package. In SQL 2016 CTP 3.0 Microsoft added Control Flow Templates (probably not the best descriptive name). This blogpost describes what I have figured out so far.

1) SSDT October preview
First download and install the SSDT October preview for Visual Studio 2015.
SSDT October Preview

2) Create new SSIS project
Now open SSDT and create a new SSIS project. In the solution explorer you will find a new folder called Control Flow Templates. Right click it to create a new template. You can also add an existing template from an other project/folder or even reference an existing template which makes it easier to create some kind of template archive. For this example just create a new one Call it MoveToArchive.
Control Flow Templates

3) Template
When you have created a new template a new file is opened which looks just like a package. The only difference is its extension: dtsxt and it only has two tabs (control flow and data flow). For this example I added a FILE connection manager (named myFile.txt) pointing to a random file and second FILE connection manager pointing to an archive folder. A File System Task will move the file to the archive folder. Rename the task to FSYS - Move to Archive. Optionally add a annotation describing the template. Then save the template.
File System Task moving file to archive folder

4)  Adding template to package
You might have noticed the extra menu item in the SSIS Toolbar called Control Flow Templates. Drag the new template from the SSIS Toolbar to your package. You will see that the task has a T in the upper right corner and that it uses the name of the template and not the name of the task in the template. Any annotations are ignored and will not show up in the package.
Adding template to package

5) Configuring the template
Now double click the template in your package or right click it and choose Edit. In the new Template Configuration Dialog, go to the second tab called Connection Managers. Select the myFile.txt connection manager and then select the connectionstring property and change the path of it to let it point to an other file.
Unfortunately you can only hardcode any changes. It would be useful to have expressions or even use a connection manager from your package to override the connection manager from your template.
Template Configuration Dialog

6) Testing
Now run the package to test the result.
Running the package

7) Point of attentions

  • You can only have one execution in a template. If you need more tasks then you have to add them in a Sequence Container.
  • Script Tasks that uses Connection Manager wont work unless you copy the connection manager to the package. Apparently it searches the name in the package instead of in the template.
  • Renaming a template will screw up packages using that template. Make sure first give it a correct name. If you want to use naming conventions than you should give the template the name of taks. In this example: "FSYS - Move to archive.dtsxt"
  • You can't edit tasks in a template via the package. You need to do that via the template itself.

Sunday, 11 October 2015

Azure File System Task for SSIS

There is an upload and download task in the SSIS Azure Pack, but how can I delete a storage container in my Azure strorage account that was created with SSIS?

At the moment there is no Azure File System Task for SSIS, but you can also do this with a Script Task.

1) Azure SDK
First download and install the Azure SDK for .NET 2.7 (or newer). This SDK contains an assembly that we need to reference in our Script Task. When you hit the download button you can download multiple files. The one you need is called MicrosoftAzureLibsForNet-x64.msi (you can't install both 64 and 32bit).
Libraries only is enough

2) SSIS Feature Pack for Microsoft Azure
Download and install (next, next, finish) the SSIS Feature Pack for Microsoft Azure (2012, 2014).
SSIS Azure Feature Pack

3 Package Parameters
Unfortunately we cannot use the Azure Storage Connection Manager because the properties we need are sensitive (writeonly in a Script Task), therefore we will use two string package parameters. The first one contains the name of the container that you want to delete and is called "ContainerName". You can find the exact name in the Azure management portal.
Container in Storage Account

The second package parameter is a sensitive string parameter named "ConnectionStringStorageAccount". It contains the connection string of the Azure Storage Account. The format should be like this (you have to replace the red parts):

The first red part of the string is the name of the storage account. You can look it up on the Azure management portal.
Storage Account "ssisjoost"

The second red part is the Account Access Key which can also be copied from Azure.
Storage Account Access Keys

The end result should look like this. Of course you can use different names or project parameters instead, but then you have to change that in the Script Task!
Package Parameters

4) Add Script Task
Add a Script Task to the Control Flow and give it a suitable name like "SCR - Delete Storage Container". Edit it, choose the ScriptLanguage and select the two string parameters from the previous step as ReadOnlyVariables. Then click on the Edit Script button to open the VSTA environment.
Edit Script Task

5) Add reference
In the solution explorer we first need to add a reference to one of the assemblies installed in step 1: Microsoft.Windows.Storage.dll which is located in the folder: C:\Program Files\Microsoft SDKs\Azure\.NET SDK\v2.7\ToolsRef\
Adding a reference in C#

6) The code - Import custom namespaces
To shorten the code we need to add some usings (C#) or some imports (VB). Add these just below the standard imports or usings.
// C# Code
#region CustomNamespaces
using Microsoft.WindowsAzure;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Auth;
using Microsoft.WindowsAzure.Storage.Blob;

or VB.NET code

' VB.NET Code
#Region "CustomImports"
Imports Microsoft.WindowsAzure
Imports Microsoft.WindowsAzure.Storage
Imports Microsoft.WindowsAzure.Storage.Auth
Imports Microsoft.WindowsAzure.Storage.Blob
#End Region

7) The code Main method
In the main method we need to replace the existing comments and code with the following code.
// C# Code
public void Main()
    // Get parameter values. Notice the difference between
    // a normal and a sensitive parameter to get its value
    string connStr = Dts.Variables["$Package::ConnectionStringStorageAccount"].GetSensitiveValue().ToString();
    string containerName = Dts.Variables["$Package::ContainerName"].Value.ToString();

        // Retrieve storage account from connection string.
        CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connStr);

        // Create the blob client.
        CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();

        // Create a reference to the container you want to delete
        CloudBlobContainer container = blobClient.GetContainerReference(containerName);

        // Delete the container if it exists

        // Show success in log
        bool fireAgain = true;
        Dts.Events.FireInformation(0, "Delete Storage Container", "Container " + containerName + " was deleted successfully", string.Empty, 0, ref fireAgain);

        // Close Script Task with Success
        Dts.TaskResult = (int)ScriptResults.Success;
    catch (Exception ex)
        // Show Failure in log
        Dts.Events.FireError(0, "Delete Storage Container", ex.Message, string.Empty, 0);

        // Close Script Task with Failure
        Dts.TaskResult = (int)ScriptResults.Failure;

or VB.NET code

' VB.NET Code
Public Sub Main()
    ' Get parameter values. Notice the difference between
    ' a normal and a sensitive parameter to get its value
    Dim connStr As String = Dts.Variables("$Package::ConnectionStringStorageAccount").GetSensitiveValue().ToString()
    Dim containerName As String = Dts.Variables("$Package::ContainerName").Value.ToString()

        ' Retrieve storage account from connection string.
        Dim storageAccount As CloudStorageAccount = CloudStorageAccount.Parse(connStr)

        ' Create the blob client.
        Dim blobClient As CloudBlobClient = storageAccount.CreateCloudBlobClient()

        ' Create a reference to the container you want to delete
        Dim container As CloudBlobContainer = blobClient.GetContainerReference(containerName)

        ' Delete the container if it exists

        ' Show success in log
        Dim fireAgain As Boolean = True
        Dts.Events.FireInformation(0, "Delete Storage Container", "Container " + containerName + " was deleted successfully", String.Empty, 0, fireAgain)

        ' Close Script Task with Success
        Dts.TaskResult = ScriptResults.Success
    Catch ex As Exception
        ' Show Failure in log
        Dts.Events.FireError(0, "Delete Storage Container", ex.Message, String.Empty, 0)

        ' Close Script Task with Failure
        Dts.TaskResult = ScriptResults.Failure
    End Try
End Sub
Related Posts Plugin for WordPress, Blogger...