Showing posts with label SCRIPT TASK. Show all posts
Showing posts with label SCRIPT TASK. Show all posts
Case
I am filling an Object variable with an Execute SQL Task and I want to use it in a Foreach Loop Container (Foreach ADO Enumerator), but the Foreach Loop stays empty. So I want to check the value of my Object variable. However debugging the package does not show me the value of Object variables. How can I see the content of my Object variable?

No (readable) value for Object variables





















Solution
A solution could be to use a Script Task after the Execute SQL Task to show the content of the Object variable. The script below shows the top (x) records in a MessageBox. The code doesn't need any changes. The only change that you could consider to make is changing the number of records to show in the MessageBox (see C# variable maxRows).
Getting content of Object variable



















1) Add a Script Script Task
Add a new Script Task to the surface of your Control Flow and connect it to your Execute SQL Task. Then edit the Script Task to provide one Object variable in the property ReadOnlyVariables or ReadWriteVariables. This should of course be the same Object variable as in your Execute SQL Task.
Provide one Object variable























2) Edit Script
Make sure to select Microsoft Visual C# as Script Langugage and then hit the Edit Script button to open the Vsta environment. Then first locate the Namesspaces to add an using for System.Data.OleDb.
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb; // Added
#endregion

Then scroll down and located the Main method "public void Main()" and replace it with the code below.
public void Main()
{
// Show max number of data rows in a simgle messagebox
int maxRows = 3;

/////////////////////////////////////////////////////////////////////
// No need to change lines below
/////////////////////////////////////////////////////////////////////

// Create a table object to store the content of the object variable
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
DataTable myTable = new DataTable();

// Create message string to show the content of the object variable
string message = "";
string header = "Error";

// Five checks before looping through the records in the object variable
////////////////////////////////////
// 1) Is a variable provided?
////////////////////////////////////
if (Dts.Variables.Count.Equals(0))
{
message = "No read-only or read-write variables found";
}
////////////////////////////////////
// 2) Multiple variables provided
////////////////////////////////////
else if(Dts.Variables.Count > 1)
{
message = "Please provide only 1 read-only or read-write variable";
}
////////////////////////////////////
// 3) Is it an object variable?
////////////////////////////////////
else if (!Dts.Variables[0].DataType.ToString().Equals("Object"))
{
message = Dts.Variables[0].Name + " is not an Object variable";
}
////////////////////////////////////
// 4) Is it null or not an table?
////////////////////////////////////
else
{
try
{
// Try to fill the datatable with the content of the object variable
// It will fail when it is null or not containing a table object.
dataAdapter.Fill(myTable, Dts.Variables[0].Value);
}
catch
{
// Failing the third check
message = Dts.Variables[0].Name + " doesn't contain a usable value";
}
}

////////////////////////////////////
// 5) Is it containing records
////////////////////////////////////
if (myTable.Rows.Count > 0)
{
int j = 0;
// Loop through all rows in the dataset but don't exceed the maxRows
for (j = 0; j < myTable.Rows.Count && j < maxRows; j++)
{
// Get all values from a single row into an array
object[] valuesArray = myTable.Rows[j].ItemArray;

// Loop through value array and columnnames collection
for (int i = 0; i < valuesArray.Length; i++)
{
message += myTable.Rows[j].Table.Columns[i].ColumnName + " : " + valuesArray[i].ToString() + Environment.NewLine;
}
// Add an empty row between each data row
message += Environment.NewLine;
}

// Create header
header = "Showing " + j.ToString() + " rows out of " + myTable.Rows.Count.ToString();
}
else if (!message.Equals(""))
{
// Don't do anything
// Record count is 0, but an other validition already failed
}
else
{
// Record count is 0
message = Dts.Variables[0].Name + " doesn't contain any rows";
}

// Show message with custom header
MessageBox.Show(message, header);

Dts.TaskResult = (int)ScriptResults.Success;
}
Now close the Vsta environment and click on OK in the Script Task editor to finish it.


3) The result
Now run the package to see the result. I tried to make it a bit monkey proof by adding some checks in the code. If you provide a good and filled variable then it will show the data. Otherwise it will show an error telling you what's wrong.
The result




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.

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
Case
I have a sensitive parameter in my package with a password in it. I want to use it in a Script Task, but when I try that it throws an error: Exception has been thrown by the target of an invocation. Can I use a sensitive parameter in a Script Task or Component?

Exception has been thrown by the target of an invocation.

















Solution
Yes you can read sensitive package and project parameters in the Script Task, but with a minor change in the code.

1) Lock for read
First open the Script Task editor and add the parameter to the ReadOnlyVariables field to lock it for read in the script.
ReadOnlyVariables























2) The Script
Open the VSTA environment by clicking in the Edit Script button. In the Main method you have something like this at the moment:
// C# Code (incorrect)
public void Main()
{
// Create string variable to store the parameter value
string mySecretPassword = Dts.Variables["$Package::MySecretPassword"].Value.ToString();

// Show the parameter value with a messagebox
MessageBox.Show("Your secret password is " + mySecretPassword);

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

Change the .Value in to .GetGetSensitiveValue() in order to retrieve the sensitive information. But from now on you are responsible for not leaking the sensitive information accidentally!

// C# Code (correct)
public void Main()
{
// Create string variable to store the parameter value
string mySecretPassword = Dts.Variables["$Package::MySecretPassword"].GetSensitiveValue().ToString();

// Show the parameter value with a messagebox
MessageBox.Show("Your secret password is " + mySecretPassword);

// Close the Script Task with success
Dts.TaskResult = (int)ScriptResults.Success;
}
The Result
Now run the script to see the result.
Oops
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Note: This GetSensitiveValue method is not available in the Script Component. And when using the .Value you get an error: Accessing value of the parameter variable for the sensitive parameter "MySecretPassword" is not allowed. Verify that the variable is used properly and that it protects the sensitive information. A tricky/ugly workaround could be to use a Script Task to retrieve the sensitive parameter and to save it in a regular package variable and then use the variable in the Script Component (but be careful!).
It's not ready yet, but I'm proud to announce the first SSIS book by me and fellow MVP Régis Baccaro (B|T).
Extending SSIS with .NET Scripting


Extending SSIS with .NET Scripting will be a timeless and comprehensive scripting toolkit for SQL Server Integration Services to solve a wide array of everyday problems that SSIS developers encounter. The detailed explanation of the Script Task and Script Component foundations will help you to develop your own scripting solutions, but this book will also show a broad arsenal of readymade and well documented scripting solutions for all common problems.

Feel free to contact us for ideas and suggestions. We will post status updates on twitter and our blogs.

It could be that the number of blogposts will slightly reduce the coming months due writing obligations (but only temporarily).
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://server.hoit.asia/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 *
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
Case
I used the propagate variable trick to continue a loop on error. That works within the package, but the parent package calling the package with the loop still fails.
Child succeeds, parent fails

















Solution
This is by design according Microsoft. The workaround they suggest is setting the DisableEventHandlers property of the Execute Package task to True. This should ignore all errors in the child package. Below here is an alternative solution to only ignore expected errors. Don't hesitate to post your own solution in the comments.

A parent package variable will be filled by the child package in case of an unexpected error. In the parent package it will be used to throw an error when it's filled.

1) Variable - Parent Package
Add a string variable in the parent package called childError.
Add string variable


























2) OnError - Child Package
Go to the child package where you used the propagate variable 'trick' and add an OnError event handler on package level. Go to Event Handlers tab. Make sure the package is selected as Executable and OnError as Event handler. Then click on the link in the middle of the page: Click here to create an 'OnError' event handler for the executable 'Child1'.
OnError event handler for package


















3a) Script Task - Child Package
Add a Script Task to the event handler and give it a suitable name. Then edit it and add the System variables ErrorCode and ErrorDescription as read only variables and type the name of the parent package in the read write box. You can't select it because it's only known at runtime.
Add the variables




















3b) The Script
Hit the edit button and copy the code 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.
// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_ace2311e5a4c4bbb98101cd54888c7c9
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
// Fill parent package variables with error message from this child package.
Dts.Variables["User::ChildError"].Value = Dts.Variables["System::ErrorCode"].Value.ToString() + " - " + Dts.Variables["System::ErrorDescription"].Value.ToString();
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

}
}

4) Propagate - Parent Package
Go back to the parent package and add an empty event handler on the Execute Package Task so that it won't fail if the child package has an error. Go to the event handlers. Select the Execute Package 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' event handler for executable 'EPT - Child1'.

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.
OnError Event Handler

















Show system variables for SSIS 2008








5a) Script Task - Parent Package
Go back to the Control Flow of your parent package and add a Script Task to fire an error. Connect it to the Execute Package Task with an expression only on the precedence constraint:
@[User::ChildError] != ""
Fire Child Error























5b) The Script
Edit the Script Task and add the string variable ChildError as read-only variable. After that hit the edit button and copy the content 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 variable























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

namespace ST_7b522ca79c9f4428a233a100bfc66e6e
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
// Fire error with error message from child package and fail script task
Dts.Events.FireError(0, "Child package", "Child package error: " + Dts.Variables["User::ChildError"].Value.ToString(), string.Empty, 0);
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

}
}


6) Precedence Contraint - Parent Package
If there is no unexpected error in the child package then we continue with the next task(s). Each task that is connected to the Execute Package Task with a Success Precedence Constraint should be changed to an expression: @[User::ChildError] == ""
Continue if there are no unexpected errors in the child package


























7) The result
I added a Script Task in the child package that always fails to simulate an unexpected error. On the second run there where only expected errors in the loop, so the child package was successful. In the parent package there is still a red cross, but it continues without (unexpected) errors.
The result