Thursday 26 June 2014

Nested includes in BIML Script

Case
I want to use nested includes in a BIML Script (an include in an include), but the second level isn't working. It seems to skip it without giving an error.
No second Sequence Container













Solution
First be careful with (too many) nested includes! It could make your BIML script obscure. There are two tricks to solve this problem. They came to me via twitter from @cathrinew and @AndreKamman.

Solution A:
Use a full path in the include tag instead of only the name:
Using the fullpath













Big downside is of course the full path in your BIML Script. In a multi-user environment with for example TFS that could be an issue because everybody needs the same project path.

Solution B:
A better option is to use CallBimlScript instead of include:
Using CallBimlScript












And you could also pass parameters to the included file and use relative path and then reuse the file in multiple projects.

Friday 20 June 2014

SSIS 2012 Execute Package Task: External Reference does not work with SSISDB

Case
I have two SSIS projects (both project deployment) and I within project 'A' I want to execute a package from project 'B' with the Execute Package Task. Project reference won't work because there are two different projects, but External reference won't work either because it doesn't support packages from the SSISDB (only file or MSDB).
Pointing to MSDB instead of SSISDB























Solution
See (/vote) this MsConnect item. They are still considering to address this issue. Here is a workaround, but it requires a little coding. I while a go I did a post on executing an SSIS 2012 package from a .Net application and I thought that it would also be possible within an SSIS Script Task.

1) Connection Manager
Create an ADO.Net connection manager that points to the SSISDB on your server.
ADO.Net connection (don't use OLEDB)























2) String variable
Create a string variable and add the path from the package that you want to execute. Format is /SSISDB/folder/project/package.dtsx
String variable filled with package path
















3) Script Task
Add a Script Task to the Control Flow and give it a suitable name. Then edit the Script Task and add the string variable from step 2 as readonly variable.
Script Task - ReadOnlyVariables


























4) The Script - References
Choose the Scripting Language and hit the Edit Script button. We need to reference 4 assemblies, but they are not in the SQL Server folder. They are only available in the GAC. The path varies a little per computer. These are mine:
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll
C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll


Right click references in the Solution Explorer and choose Add Reference... Then browse these four dll files and add them one by one.
Add references

























IMPORTANT: After adding the references you should press the Save All button to save the reference changes!

5) The Script - Code
Now copy the usings(/imports) from my code and copy the content of my Main method to your main method. The example is in C#, but you can use this translator to get VB.Net code
// C# Code
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
// Added:
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.IntegrationServices;
using System.Collections.ObjectModel;
#endregion

namespace ST_e71fdb73f68c4a3f9595ea5d37464a62
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {
        public void Main()
  {
            // Boolean variable for firing event messages
            bool fireAgain = true;

            // Execution of child package starting
            Dts.Events.FireInformation(0, "Child Package - " + Dts.Variables["User::SSISDBPackagePath"].Value.ToString(), "Starting", string.Empty, 0, ref fireAgain);

            try
            {
                // Connection to the database server where the packages are located
                // SqlConnection ssisConnection = new SqlConnection(@"Data Source=.\SQL2012;Initial Catalog=SSISDB;Integrated Security=SSPI;");
                SqlConnection ssisConnection = new SqlConnection(Dts.Connections["mySqlServerAdoNet"].ConnectionString);
                                
                // SSIS server object with connection
                IntegrationServices ssisServer = new IntegrationServices(ssisConnection);
                
                // Split the variable containing the package path in smaller
                // parts: /SSISDB/Folder/Project/Package.Dtsx 
                string[] SSISDBPackagePath = Dts.Variables["User::SSISDBPackagePath"].Value.ToString().Split('/');

                // The reference to the package which you want to execute
                // Microsoft.SqlServer.Management.IntegrationServices.PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders["folder"].Projects["project"].Packages["package.dtsx"];
                Microsoft.SqlServer.Management.IntegrationServices.PackageInfo ssisPackage = ssisServer.Catalogs[SSISDBPackagePath[1]].Folders[SSISDBPackagePath[2]].Projects[SSISDBPackagePath[3]].Packages[SSISDBPackagePath[4]];

                // Add execution parameter to override the default asynchronized execution. If you leave this out the package is executed asynchronized
                Collection<Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet> executionParameter = new Collection<Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet>();
                executionParameter.Add(new Microsoft.SqlServer.Management.IntegrationServices.PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "SYNCHRONIZED", ParameterValue = 1 });

                // For adding more parameters go to my WIKI post on MSDN:
                // http://social.technet.microsoft.com/wiki/contents/articles/21978.execute-ssis-2012-package-with-parameters-via-net.aspx

                // Get the identifier of the execution to get the log
                long executionIdentifier = ssisPackage.Execute(false, null, executionParameter);

                // If you want to catch the events from the package you are executing then you can add this
                // foreach loop. It reads the events and fires them as events. You can remove this loop if
                // you're not interested in them.

                // Loop through the log and fire events
                foreach (OperationMessage message in ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier].Messages)
                {
                    // Translate Message Source Type code and Message Type code to description. See
                    // MSDN for the complete list http://msdn.microsoft.com/en-us/library/ff877994.aspx

                    string messageSourceType = "";
                    switch (message.MessageSourceType)
                    {
                        case 10:
                            messageSourceType = "Entry APIs, such as T-SQL and CLR Stored procedures";
                            break;
                        case 20:
                            messageSourceType = "External process used to run package (ISServerExec.exe)";
                            break;
                        case 30:
                            messageSourceType = "Package-level objects";
                            break;
                        case 40:
                            messageSourceType = "Control Flow tasks";
                            break;
                        case 50:
                            messageSourceType = "Control Flow containers";
                            break;
                        case 60:
                            messageSourceType = "Data Flow task";
                            break;
                    }

                    // Translate Message Type (=event)
                    string messageType = "";
                    switch (message.MessageType)
                    {
                        case -1:
                            messageType = "Unknown";
                            break;
                        case 120:
                            messageType = "Error";
                            break;
                        case 110:
                            messageType = "Warning";
                            break;
                        case 70:
                            messageType = "Information";
                            break;
                        case 10:
                            messageType = "Pre-validate";
                            break;
                        case 20:
                            messageType = "Post-validate";
                            break;
                        case 30:
                            messageType = "Pre-execute";
                            break;
                        case 40:
                            messageType = "Post-execute";
                            break;
                        case 60:
                            messageType = "Progress";
                            break;
                        case 50:
                            messageType = "StatusChange";
                            break;
                        case 100:
                            messageType = "QueryCancel";
                            break;
                        case 130:
                            messageType = "TaskFailed";
                            break;
                        case 90:
                            messageType = "Diagnostic";
                            break;
                        case 200:
                            messageType = "Custom";
                            break;
                        case 140:
                            messageType = "DiagnosticEx";
                            break;
                        case 400:
                            messageType = "NonDiagnostic";
                            break;
                        case 80:
                            messageType = "VariableValueChanged";
                            break;
                    }

                    // Fire event depending on the message type (event) in the child package. Since there are event types that you
                    // can't fire from a Script Task, we need to 'translate' them. For example a TaskFailed event is fired as an
                    // error event. More info see: http://microsoft-ssis.blogspot.com/2011/02/script-task-and-component-logging.html
                    switch (message.MessageType)
                    {
                        case -1:    // Unknown
                        case 120:   // Error
                        case 130:   // TaskFailed
                            Dts.Events.FireError(Convert.ToInt32(message.MessageType), "Child Package - " + messageSourceType, messageType + " : " + message.Message, string.Empty, 0);
                            break;
                        case 110:   // Warning
                            Dts.Events.FireWarning(Convert.ToInt32(message.MessageType), "Child Package - " + messageSourceType, messageType + " : " + message.Message, string.Empty, 0);
                            break;
                        default:
                            Dts.Events.FireInformation(Convert.ToInt32(message.MessageType), "Child Package - " + messageSourceType, messageType + " : " + message.Message, string.Empty, 0, ref fireAgain);
                            break;
                    }
                } // END FOREACH LOOP

                if (ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier].Status == Operation.ServerOperationStatus.Success)
                {
                    // Execution of child package succeeded
                    Dts.Events.FireInformation(0, "Child Package - " + Dts.Variables["User::SSISDBPackagePath"].Value.ToString(), "Succeeded", string.Empty, 0, ref fireAgain);
                    Dts.TaskResult = (int)ScriptResults.Success;
                }
                else
                {
                    // Execution of child package failed
                    Dts.Events.FireError(0, "Child Package - " + Dts.Variables["User::SSISDBPackagePath"].Value.ToString(), "There may be error messages posted before this with more information about the failure.", string.Empty, 0);
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }

            }
            catch (Exception ex)
            {
                // Execution of child package failed (server timeout, can't find package, etc.)
                Dts.Events.FireError(0, "Child Package - " + Dts.Variables["User::SSISDBPackagePath"].Value.ToString(), "Failed: " + ex.Message, string.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
  }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}


6) The result
Now you can execute the package and see the result. You could add some filtering in the message loop to reduce the number of messages.
The result in Visual Studio



















Note 1: The other project already needs to be deployed to the SSIS Catalog
Note 2: You get two executions with both their own ServerExecutionId
Two executions






If you want to pass parameters to the child package then you could check out my WIKI example on parameters.

There are alternatives like executing a SQL Server Agent job via an Execute SQL Task or Calling DTExec via an Execute Process Task.

* update: 30 second timeout workaround *

Wednesday 11 June 2014

Highlights

My third son was born yesterday!
Jasper Arthur



And less important, but still cool: millionth pageview today :-)

Regards,

Joost


Saturday 7 June 2014

Is the Row Sampling Transformation fully blocking?

Case
Is the Row Sampling Transformation a (fully) blocking component or a non-blocking component? Various blogs and forums disagree on each other. Is it possible to see it in SSIS?

Solution
By adding some logging to the package you can see the Pipeline Execution Trees of your Data Flow. This will tell you whether a transformation creates a new buffer. When it creates a new buffer then the component is a-synchronously / blocking.


1) Add logging
Open your package and go to the SSIS menu and choose Logging...
SSIS Menu - Logging...
















2) SQL logging
For this example I selected SQL Server logging, pressed the add button and then selected a Connection Manager.
SQL Server logging






















Next check the Data Flow Task (1) and the enable the SQL Server Log (2) for it. After that go to the Details tab (3).
Enable logging for the Data Flow Task





















In the details tab scroll down and select PipelineExecutionTrees. Then click on the advanced button.
Logging Details





















In the advanced section make sure that the MessageText is checked. In this column you will find the execution trees text. After that click OK to close the logging window.
Check MessageText
















3) Run and check log
Run the package and then check the log. In the column Message you can find the Execution Trees.
SQL Server Logging. Check message column.











Now check these examples and see which is blocking. Multiple paths means that there is a transformation that creates new buffers. The last example is the Row Sampling:
Plain Data Flow: 1 path - No blocking

Data Flow with Sort: 2 paths - Sort is blocking

Data Flow with Percentage Sampling: 1 path - No blocking

Data Flow with Row Sampling: 3 paths - Row Sampling is blocking














































4) Partial Blocking or Fully Blocking?
Unfortunately you can't see in the log whether the task is partially blocking (like union or merge join) or fully blocking (like sort and aggregate), but you can see it in the Data Flow Task when running your package in Visual Studio (BIDS/SSDT). The Row Sampling Transformation is fully blocking because it apparently need all data before it sends data to its output. So try not to use it unnecessarily.

Row Sampling is Fully Blocking































The Row Sampling isn't just doing a TOP X, but it spreads the sampled rows over all buffers. Because you don't know the number of records or buffers that is coming you have to have all rows before you can pick randomly X records from the whole set.

An alternative (with less random rows) could be to use a Script Component that adds a row number and then use a Conditional Split to select the first X rows (and perhaps combine it with a modulo expression like: Rownumber % 3 == 0 && Rownumber <= 3000). The Conditional Split is a non-blocking component. Note: this isn't necessarily faster. Check it first for your case!

So why is the Percentage Sampling not blocking? It just takes the same percentage of each buffer and can therefore be synchronous.


Confirmed by Mister SSIS himself!

Sunday 1 June 2014

Retry a Task on failure

Case
I have a webservice task that sometimes fails (due an external cause) and I want to retry it a couple of times before failing the entire package. How do I create a retry construction for a task?

Solution
A solution could be to add a FOR LOOP around the task you want to retry. This works for all tasks, not just the webservice task.
A For Loop Container for retrying a task















1) Variables
We need a couple of variables for the For Loop Container.
- RetryMax: an integer indicating the maximum number of attempts
- RetryCounter: an integer to keep track of the number of attempts
- QuitForLoop: a boolean for quiting loop before reaching the maximum number of attempts
- RetryPause: an integer for storing the number of pause seconds before retry

Variables for the FOR LOOP























2) For Loop
Add a For Loop Container and move the task you want to retry inside the container. Edit the For Loop Container and set the following properties:

InitExpression: @[User::RetryCounter] = 1
This will give the RetryCounter an initial value.

EvalExpression: @[User::RetryCounter] <= @[User::RetryMax] &&
                             @[User::QuitForLoop] == false
This will indicate when the For Loop Container stops looping. In this case reaching the MaxRetry or when the Boolean variable is filled with True.

AssignExpression: @[User::RetryCounter] = @[User::RetryCounter] + 1
This will increase the RetryCounter.
The FOR LOOP expressions
















3a) Pause Task
For this example I will use a Script Task for waiting a couple of seconds/minutes before a retry . If you don't like scripting there are alternatives for a pause. Add a Script Task, give it a useful name and connect it to the task you want to retry. Make sure the Script Task only executes on error by setting the Constraint Option to Failure.
Pause after failure
















3b) The Script
Edit the script Task and add the 3 integer variables as read-only variables. Then hit the edit button and copy the contents of my main method to your main method. The example code is in C#. For a VB.Net version you can use this conversion tool.
Read-only variables

















// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_1c11fe9f84ce4662bdc37ece5316e04d
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        public void Main()
        {
            if (Dts.Variables["RetryCounter"].Value.ToString() != Dts.Variables["RetryMax"].Value.ToString())
            {
                // Fire warning message that the previous task failed
                Dts.Events.FireWarning(0, "Wait", "Attempt " + Dts.Variables["RetryCounter"].Value.ToString() + " of " + Dts.Variables["RetryMax"].Value.ToString() + " failed. Retry in " + Dts.Variables["RetryPause"].Value.ToString() + " seconds.", string.Empty, 0);

                // Wait x seconds
                System.Threading.Thread.Sleep(Convert.ToInt32(Dts.Variables["RetryPause"].Value) * 1000);

                // Succeed Script Task and continue loop
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            else
            {
                // Max retry has been reached. Log, fail and quit
                Dts.Events.FireError(0, "Wait", "Attempt " + Dts.Variables["RetryCounter"].Value.ToString() + " of " + Dts.Variables["RetryMax"].Value.ToString() + " failed. No more retries.", string.Empty, 0);

                // Fail Script Task and quit loop/package
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

        #region ScriptResults declaration
        /// 
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// 
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
    }
}


4a) Quit loop on success
To quit the loop when your task executes successfully we are setting the Boolean variable QuitForLoop to true. I'm using a Script Task for it, but you could also use an Expression Task if you're using 2012 and above or a custom Expression Task for 2008.

Add a Script Task, give it a useful name and connect it to the task you want to retry. Edit the Script Task and add the integer variables RetryCounter and RetryMax as read-only variables and the Boolean variable QuitForLoop as read-write variable. After this hit the Edit button and go to the next step.
read-only and read-write variables


















4b) The Script
Copy the contents of my main method to your main method. This code will set the Boolean variable to true causing the loop to stop. The example code is in C#. For a VB.Net version you can use this conversion tool.
// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_a72ebc0827b64c0f8a1083951014129c
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            // Fire information message that the previous task succeeded
            bool FireAgain = true;
            Dts.Events.FireInformation(0, "Succeeded", "Attempt " + Dts.Variables["User::RetryCounter"].Value.ToString() + " of " + Dts.Variables["User::RetryMax"].Value.ToString() + " succeeded. Quiting loop", string.Empty, 0, ref FireAgain);

            // Fill boolean variable with true so that the FOR LOOP EvalExpression will evaluate false and quit
            Dts.Variables["User::QuitForLoop"].Value = true;

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        #region ScriptResults declaration
        /// 
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// 
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
    }
}

5) Event handler propagate
Now the most important step! A failure on one of the tasks within a parent container (package, sequence, for loop or foreach loop) will also cause the parent container to fail, but we want to continue after an error. The trick is to add an empty OnError event handler on the task that could fail and change the value of the propagate variable .

In this example we want to ignore errors on the webservice task. Go to the event handlers. Select the webservice task as executable and select OnError as eventhandler and then create the event handler by clicking on the link in the middle of the screen: "Click here to create an 'OnError' eventhandler for executable 'WST - Call webservice'.

Next go to the variables pane and hit the Variable Grid Option button to also show system variables (SSIS 2008 has a different button). Then find the system variable Propagate and set it to false.

Last step is to add an annotation in the empty event handler to explain why it's empty.

Empty OnError event handler with propagate set to false


















Show system variables for SSIS 2008








Note: propagate will only work within a package not in a parent-child package construction, but there is a workaround available.

6) testing
In the first run the third attempt was successful and the package succeeded. In the second run all five attempts failed and so did the package.
Third attempt was successful

All five attempts failed
Related Posts Plugin for WordPress, Blogger...