Monday 7 December 2015

Executing a PowerShell script in an SSIS package

Case
I want to execute a PowerShell Script within an SSIS package, but there is no PowerShell task. How do I do that in SSIS?

Solution
There are 3 possible solutions within SSIS:
  1. Using a Custom Task
  2. Using the Execute Process Task
  3. Using the Script Task
For this example I will use a simple PowerShell script that exports a list of installed hotfixes on the server to a CSV file, but the possibilities with PowerShell are endless.
#PowerShell: c:\temp\hotfixes.ps1
[CmdletBinding()]
Param(
    # FilePathCsv parameter is required
    [Parameter(Mandatory=$True,Position=1)]
    [string]$FilePathCsv
)

# Create folder if it doesn't exists
$FolderPath = split-path $FilePathCsv
# Check if folder exists
if (-Not (Test-Path $FolderPath))
{
 Write-Host "Creating folder $FolderPath"
 New-Item -ItemType directory -Path $FolderPath
}

# Export list of hotfixes to CSV file
Get-WmiObject -Class Win32_QuickFixEngineering -ComputerName .| Export-Csv $FilePathCsv

The script has a parameter to supply a csv filepath. It checks whether the folder mentioned in this path exists. If not it creates it. Then it exports the data to specified CSV file.

A) Using a Custom Task
You could look for a custom task like the one on codeplex (also see this example), but it seems not to be an active project. And so far I haven't found any commercial PowerShell Tasks for SSIS.

B) Using the Execute Process Task
To execute the PowerShell script with an Execute Process Task you need a command that looks like:
powershell.exe -command "c:\temp\hotfixes.ps1 -FilePathCsv c:\temp\export\hotfixes.csv"

Add an Execute Process Task to the Control Flow and give it a suitable name like "EPR - Create hotfixes CSV". Edit it and go to the Process page. In the Excutable property you add the complete path of the PowerShell executable. For example: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
And in the Arguments property you add the rest of the command: -command "c:\temp\hotfixes.ps1 -FilePathCsv c:\temp\export\hotfixes.csv"
PowerShell via Execute Process Task























This step is optional but with two SSIS string variables (or parameters) containing the paths and an expression on the arguments property you can make it a little more flexible: "-command \"" + @[User::PathPowerShellScript] + " -FilePathCsv " + @[User::PathHotfixes] + "\""
Process Task with variables(expressions) in the arguments



















When you run this in Visual Studio you will see a PowerShell window for a couple of seconds. With the StandardOutputVariable and StandardErrorVariable you can catch the output of the PowerShell script into an SSIS variable.
PowerShell window when executing the Execute Process Task




















C) Using the Script Task
If you don't like the Execute Process Task then you could use the Script Task to execute a PowerShell script, but to use PowerShell in .NET you need to install the Windows Software Development Kit (SDK) for Windows.
 It contains the assembly System.Management.Automation.dll which we need to reference in our Script Task later on. Don't forget to install it on all your machines (DTAP: Development, Test, Acceptance and Production).
Choose Windows Software Development Kit for the libraries





















First add two string variables (or parameters) to your package. PathHotfixes containts the filepath of the to be created CSV file and PathPowerShellScript contains the filepath of the PowerShell file.
String variables








Add the Script Task to the Control Flow and give it a suitable name like "SCR - Create hotfixes CSV". Then edit it choose the Script Language (C# or VB.NET).
Choose ScriptLanguage C# or VB




















Then add the two string variables (or parameters) as Read Only Variables. We are using them to avoid hardcoded paths in our script.
ReadOnlyVariables
























After that hit the Edit Script button to open the VSTA environment. In the Solution Explorer (upper right corner) right click the References and choose Add Reference... then browse to C:\Program Files (x86)\Reference Assemblies\Microsoft\WindowsPowerShell\3.0 and select the System.Management.Automation.dll file.
Add Reference


















In the code we need to add extra namespaces (usings in C# and imports VB) to shorten the code. You can either add these rows to the existing namespaces by adding them in the region Namespaces. Or you can add a separate region for custom namespaces.
#region Custom Namespaces
using System.Management.Automation;
using System.Management.Automation.Runspaces;
#endregion

or VB.NET
#Region "Custom Namespaces"
Imports System.Management.Automation
Imports System.Management.Automation.Runspaces
#End Region

Next go to the main method and add the following code
// C# code
/// 
/// This method executes a PowerShell file with a parameter
/// 
public void Main()
{
    // Create a new Runspace to run your command in
    RunspaceConfiguration runspaceConfiguration = RunspaceConfiguration.Create();
    Runspace runspace = RunspaceFactory.CreateRunspace(runspaceConfiguration);
    runspace.Open();

    // Create a new command with a parameter
    Command command = new Command(Dts.Variables["User::PathPowerShellScript"].Value.ToString());
    CommandParameter commandParameter = new CommandParameter("FilePathCsv", Dts.Variables["User::PathHotfixes"].Value.ToString());
    command.Parameters.Add(commandParameter);

    // Execute the PowerShell script in the Runspace
    Pipeline pipeline = runspace.CreatePipeline();
    pipeline.Commands.Add(command);
    pipeline.Invoke();

    // Close the Script Task and return Success
    Dts.TaskResult = (int)ScriptResults.Success;
}

or VB.NET
' VB.NET code
' This method executes a PowerShell file with a parameter
Public Sub Main()
 ' Create a new Runspace to run your command in
 Dim runspaceConfiguration As RunspaceConfiguration = RunspaceConfiguration.Create()
 Dim runspace As Runspace = RunspaceFactory.CreateRunspace(runspaceConfiguration)
 runspace.Open()

 ' Create a new command with a parameter
 Dim command As New Command(Dts.Variables("User::PathPowerShellScript").Value.ToString())
 Dim commandParameter As New CommandParameter("FilePathCsv", Dts.Variables("User::PathHotfixes").Value.ToString())
 command.Parameters.Add(commandParameter)

 ' Execute the PowerShell script in the Runspace
 Dim pipeline As Pipeline = runspace.CreatePipeline()
 pipeline.Commands.Add(command)
 pipeline.Invoke()

 ' Close the Script Task and return Success
 Dts.TaskResult = ScriptResults.Success
End Sub

Alternative: If you don't want to store the PowerShell code in a separate file because it makes it harder to deploy it through the DTAP environment then you can also add the PowerShell code directly in your .NET code or store it in an SSIS variable and pass that to the Script Task. In that case the code slightly changes.
// C# code
/// 
/// This method executes a PowerShell script
/// 
public void Main()
{
    // Create a new Runspace to run your script in
    RunspaceConfiguration runspaceConfiguration = RunspaceConfiguration.Create();
    Runspace runspace = RunspaceFactory.CreateRunspace(runspaceConfiguration);
    runspace.Open();

    // Create string with PowerShell code (or get it from an SSIS variable)
    string PowerShellScript = @"$FilePathCsv = ""XXX""

                                # Create folder if it doesn't exists
                                $FolderPath = split-path $FilePathCsv
                                # Check if folder exists
                                if (-Not (Test-Path $FolderPath))
                                {
                                 Write-Host ""Creating folder $FolderPath""
                                 New-Item -ItemType directory -Path $FolderPath
                                }

                                # Export list of hotfixes to CSV file
                                Get-WmiObject -Class Win32_QuickFixEngineering -ComputerName .| Export-Csv $FilePathCsv";

    // Replace the hardcode dummy path with a value from an SSIS variable
    string exportFile = Dts.Variables["User::PathHotfixes"].Value.ToString();
    PowerShellScript = PowerShellScript.Replace("XXX", exportFile);

    // Execute the PowerShell script in the Runspace
    Pipeline pipeline = runspace.CreatePipeline();
    pipeline.Commands.AddScript(PowerShellScript);
    pipeline.Invoke();

    // Close the Script Task and return Success
    Dts.TaskResult = (int)ScriptResults.Success;
}

or VB.NET (less readable due the lack of a good string continuation on multiple lines in VB)
' VB.NET code
' This method executes a PowerShell script
Public Sub Main()
 ' Create a new Runspace to run your script in
 Dim runspaceConfiguration As RunspaceConfiguration = RunspaceConfiguration.Create()
 Dim runspace As Runspace = RunspaceFactory.CreateRunspace(runspaceConfiguration)
 runspace.Open()

 ' Create string with PowerShell code (or get it from an SSIS variable)
 Dim PowerShellScript As String = "$FilePathCsv = ""XXX""" & Environment.NewLine &
          "# Create folder if it doesn't exists" & Environment.NewLine &
          "$FolderPath = split-path $FilePathCsv" & Environment.NewLine &
          "# Check if folder exists" & Environment.NewLine &
          "if (-Not (Test-Path $FolderPath))" & Environment.NewLine &
          "{" & Environment.NewLine &
          " Write-Host ""Creating folder $FolderPath""" & Environment.NewLine &
          " New-Item -ItemType directory -Path $FolderPath" & Environment.NewLine &
          "}" & Environment.NewLine &
          "# Export list of hotfixes to CSV file" & Environment.NewLine &
          "Get-WmiObject -Class Win32_QuickFixEngineering -ComputerName .| Export-Csv $FilePathCsv"

 ' Replace the hardcode dummy path with a value from an SSIS variable
 Dim exportFile As String = Dts.Variables("User::PathHotfixes").Value.ToString()
 PowerShellScript = PowerShellScript.Replace("XXX", exportFile)

 ' Execute the PowerShell script in the Runspace
 Dim pipeline As Pipeline = runspace.CreatePipeline()
 pipeline.Commands.AddScript(PowerShellScript)
 pipeline.Invoke()

 ' Close the Script Task and return Success
 Dts.TaskResult = ScriptResults.Success
End Sub


After that close the VSTA editor and the Script Task Editor and test the Script Task. If it was successful you can use a Data Flow Task to read the CSV file. With this relative easy code and this custom task example you could create your own custom PowerShell task.

Sunday 6 December 2015

Disable multiple checkout in TFS

Case
I tried disabling the TFS multiple checkout for an SSIS project, but my collegue and me can still check out the same SSIS package at the same time causing all kinds of issues when checking in the changed package. How can I disable multiple checkout?
Disable multiple checkout in TFS

















Solution
You need to check your workspaces. If one of you is still using a local workspace then you can still checkout the same package multiple times.

Go to the Source Control Explorer in Visual Studio. You can find the link in the Team Explorer pane. Then (1) open the Workspace selectbox and choose Workspaces... In the Manage Workspaces window click (2) on the Edit... button and then (3) on the Advanced >> button.
Edit Workspace














In the advanced option change the location from Local to Server and click on OK and one more time in the next window.
Set workspace location to server




















If you both use the workspace on the server then you will get an message that the SSIS package is checked out by user xxx.
Package checked out by colleague Menno














Now you only need to remember to don't keep the project or solution checked out otherwise no one else can add (, delete or rename) projects/packages/connection managers/etc. When adding a new package the project will be checked out. To prevent long check outs, you should first give the new/empty package its correct name and then check-in everything (the project and the new/empty package). Only then you can start building the new package!
Also see MSND for more information about local and server workspaces.

Monday 30 November 2015

BIML force creating connection managers

Case
If you declare a connection manager in BIML, but don't use it in one of the tasks or transformations, it won't be created. Can you force BIML to create the connection managers nevertheless?


No connection managers were created
















Solution
In some cases you want to override this feature and just create the connection managers. For example when using Custom Tasks/Transformations where BIML doesn't recognize a connection manager attribute.



To force BIML to create the connection managers you need to add a second <Connections> tag, but this time within the package tag. And within this tag you can add <Connection> tags with a ConnectionName attribute. As a value you need to need to supply the name of the connection manager that you created in the first <Connections> tag.
Force creating connection managers
















<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <AdoNetConnection ConnectionString="Data Source=.;Initial Catalog=tempdb;Integrated Security=True;"
        Provider="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
        Name="myStage"
        CreateInProject="true"
        />
    <FileConnection FileUsageType="ExistingFolder"
        FilePath="d:\"
        Name="myFolder"
        CreateInProject="false"
        />
  </Connections>

  <Packages>
    <Package Name="myPackage" ProtectionLevel="DontSaveSensitive">
      <Tasks>
        <Container Name="myContainer">
          
        </Container>
      </Tasks>
      <Connections>
        <!--  Force creating connection managers  -->
        <Connection ConnectionName="myStage" />
        <Connection ConnectionName="myFolder" />
      </Connections>
    </Package>
  </Packages>
</Biml>


You can even determine the guid of each connection manager.
<Connections>
  <!--  Force creating connection managers  -->
  <Connection ConnectionName="myStage"
        Id="{365878DA-0DE4-4F93-825D-D8985E2765FA}"/>
  <Connection ConnectionName="myFolder"
        Id="{365878DA-0DE4-4F93-825D-D8985E2765FB}"/>
</Connections>


And if you need the same GUID in multiple places within your script, but you want a random GUID, then you can add a string variable and fill it with a random GUID. Then you can use that variable in multiple places.
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <AdoNetConnection ConnectionString="Data Source=.;Initial Catalog=tempdb;Integrated Security=True;"
            Provider="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
            Name="myStage"
            CreateInProject="true"
        />
    <FileConnection FileUsageType="ExistingFolder"
            FilePath="d:\"
            Name="myFolder"
            CreateInProject="false"
        />
  </Connections>
  
  <#
    // Create Random Guid but use it in multiple places
    string myGuid = System.Guid.NewGuid().ToString();
  #>

    <Packages>
    <Package Name="myPackage" ProtectionLevel="DontSaveSensitive">
      <Tasks>
        <Container Name="myContainer">
          
        </Container>
      </Tasks>
      <Connections>
        <!--  Force creating connection managers    -->
        <Connection ConnectionName="myStage"
              Id="<#=myGuid#>"/>
        <Connection ConnectionName="myFolder"
              Id="{365878DA-0DE4-4F93-825D-D8985E2765FB}"/>
      </Connections>
    </Package>
  </Packages>
</Biml>

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

Case
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?

Solution
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):
DefaultEndpointsProtocol=https;AccountName=ssisjoost;AccountKey=34PQgq+Kpr9Mz4rUfGoTpR1GZrGcC/SaFphXt3aUmgzXrcowtba0vz+uq1bIYBS5FkFYEaJ6W2CYVSsB5C8AEDQ==

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;
#endregion

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();

    try
    {
        // 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
        container.DeleteIfExists();

        // 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()

    Try
        ' 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
        container.DeleteIfExists()

        ' 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

Sunday 27 September 2015

SQL Saturday Holland 2015 - What's new in SSIS 2016 CTP 2.3

My SQL Saturday Holland 2015 presentation is now available for download. See link on sessions schedule. I had a great day and met lots of nice people! See you all on the next sql pass event.
Photo by @mstreutker

Tuesday 8 September 2015

SQL Server 2016 CTP 2.3 New database roles SSISDB

Case
There are two new database roles available in the SSIS Catalog (ssis_logreader and ssis_monitor). What kind of permissions do these roles have and how should we use them?
ssis_logreader and ssis_monitor roles


























Solution
The database role ssis_monitor is for an AlwaysOn situation to do some clean-up and update work. The role is used by the SQL Server Agent job "SSIS Failover Monitor Job" and you shouldn't use this role yourself.

But you can use the new database role ssis_logreader which allows a user to read reports in the catalog. If you don't have this role you can only see the reports with your own executions on it. A workaround for that was to give a user the database role ssis_admin, but allows you to play God in the catalog. This is where the new logreader role comes in handy. It allows you to see everybody's executions without the God-mode.

Friday 4 September 2015

SQL Server 2016 CTP 2.3 OData V4 protocol

SSIS 2016 CTP 2.3 introduces an ODdata Source Component and an OData Connection Manager with V4 support. It now supports:
  • OData V3 protocol for ATOM and JSON data formats.
  • OData V4 protocol for JSON data format.
The 2014 version only supports V3.
SSIS 2014: Data at the root level is invalid. Line 1, position 1. (System.Xml)

























 
First the OData Connection Manager. You can test this with the test URL: http://services.odata.org/V4/Northwind/Northwind.svc/
More info here.
OData Connection Manager with V4 support



 




















 
 

And there is the OData Source Component that uses the OData Connection Manager. More info here.
OData Source




















And now in action with a Data Viewer:
OData with Data Viewer

Thursday 3 September 2015

SQL Server 2016 CTP 2.3 Get error columnname

Case
Before SSIS 2016 there was no way to get the name of the column that caused the error. Atleast not without a custom transformation or a very complicated script that looped through a copy of your package to get all details. Now there is a simple solution available.

Solution
There was already a script available to get the error description. Now you can use a similar way to get the columnname. First download SSDT 2015 (SSDT and SSDT-BI have been merged!).

* UPDATE: code below outdated / changed click here for new code *

1) Data Flow Task
For this example we have a Flat File Source and to throw an error there is a column in the textfile with a too large value causing a truncation error. To catch the error details we redirect all errors of the Flat File Source to an Error Output. You can find these settings by editing the Flat File Source component or by connecting its red output to an other transformation.

Redirect errors to Error Output

























2) Add Script Component
The Error Output is redirected to a Script Component (type transformation). It should look something like this below. Give it a suitable name like "SCR- Get Error Details".
Add Script Component Transformation


















3) Input Columns
Edit the Script Components and go to the Input Columns page and select the ErrorCode (for getting an error description) and the ErrorColumn (for getting a column name) as ReadOnly input columns.
Input Columns

























4) Output Columns
Create two output columns with the Data Type String (DT_STR). For this example I used 255 for the length, but that could probably be a little smaller. The names are ErrorDescription and ErrorColumnName.
Output Columns

























5) The Code
Now go to the first page to choose your Scripting Language and then click on the Edit Script button to open the VSTA environment. Then locate the Input0_ProcessInputRow method at the bottom and add the following two lines of code.
// C# Code
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
    Row.ErrorColumnName = this.ComponentMetaData.GetIdentificationStringByLineageID(Row.ErrorColumn);
}

And VB.NET code

' VB Code
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)
    Row.ErrorColumnName = Me.ComponentMetaData.GetIdentificationStringByLineageID(Row.ErrorColumn)
End Sub


6) Testing
Now add a Data Viewer behind the Script Component Transformation to see the results
Error Description and ColumnName

Monday 31 August 2015

SQL Server 2016 CTP 2.3 Custom Logging Levels

Case
In CTP 2.3 Custom logging levels where added. How does it work?

Solution
You can set it up in the Catalog with SQL Server Management Studio.

1) Customized Logging Level
Right click on the SSISDB Catalog and choose Customized Logging Level. A new window will be opened where you can add and change Customized Logging Levels.
Right click the SSISDB Catalog
















2) Create
Click on the create button to add a name and description of your new Customized Logging Level. In this case we will create a logging level that only shows OnError events.
Only OnError





















3) Edit
A new logging level has been created. You can click it to view and change the properties. Our first Customized Logging Level has ID 1.
Edit Customized Logging Level


















4) Statistics
Go to the second tab to change the Statistics. In CTP 2.3 the checboxes on the individual rows seems to be missing. So I added them manually to the screenshot, but they do work when you use your Arrow keys to select the cell where they supose to be and then press [Space] to (un)check the individual item.

Select Statistics options



















5) Events
Now go to the third tab to select the events you want to log. Same problem with the missing checkboxes here, but the same workaround works. In this case I only checked OnError events. When you're ready click on the Save button to save all changes. 
Select the Events


















6) Execute
Now right click on your package to execute it and go to the Advanced tab to select the Logging Level. In the drop down list select the bottom item <Select customized logging level...>.
Select customized logging level...

















7) Select customized logging level
A new popup window appears when you can select your newly created Cusomized Loging Level. Again there seems to be missing a checkbox here. By default the row seems to be already selected, so you don't have to selected it. If you do try to select it, you will uncheck it and you will get a warning:
No customized logging level is specified. Please select a customized logging level value before performing this operation.





















8) View details customized logging level
When you click on the button with the three dots on the right side of the popup window, you will be able to see the details. Here the checkboxes are visible!
View details of your customized logging level




















9) Execution results
Now it's time to see the result. As you can see: a customized logging level was selected and only errors are shown.


















10) Feature request
Besides the hidden checkboxes I also would like to make my Customized Logging Level the default for the catalog. At the moment this seems not to be possible.
Default logging level























Update: Nice youtube video with Matt Masson showing the custom logging levels
Related Posts Plugin for WordPress, Blogger...