Saturday, 16 May 2015

Timeout after 30 seconds when executing package via .NET

Case
I'm executing a package via .NET (example 1, example 2), but if the packages takes more than 30 seconds I get an error: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. Changing the timeout in the connection string to for example 300 has no effect.

// C# Code (incorrect)
// Connection to the database server where the packages are located
using (SqlConnection ssisConnection = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=SSPI;Connection Timeout=300")
{
  try
  {
    // SSIS server object with connection
    IntegrationServices ssisServer = new IntegrationServices(ssisConnection);
    
    // The reference to the package which you want to execute
    PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders["SSISJoost"].Projects["MyProject"].Packages["MyPackage.dtsx"];

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

    // Add a package parameter
    executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 30, ParameterName = "myStringParam", ParameterValue = "some value" });

    // Get the identifier of the execution to get the log
    long executionIdentifier = ssisPackage.Execute(false, null, executionParameter);
  }
  catch (exception ex)
  {
    // Log code for exceptions
  }
}


Solution
The 30 seconds is the default timeout which apparently can't be changed in the ssisPackage.Execute command. The solution is a little dirty. First remove the SYNCHRONIZED parameter to execute the package asynchronized. Then add some code after the ssisPackage.Execute command.
// C# Code (correct)
using (SqlConnection ssisConnection = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=SSPI;")
{
  try
  {
    // SSIS server object with connection
    IntegrationServices ssisServer = new IntegrationServices(ssisConnection);
    
    // The reference to the package which you want to execute
    PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders["SSISJoost"].Projects["MyProject"].Packages["MyPackage.dtsx"];

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

    // Add a package parameter
    executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 30, ParameterName = "myStringParam", ParameterValue = "some value" });

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

    // Get execution details with the executionIdentifier from the previous step
    ExecutionOperation executionOperation = ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier];

    // Workaround for 30 second timeout:
    // Loop while the execution is not completed
    while (!(executionOperation.Completed))
    {
      // Refresh execution info
      executionOperation.Refresh();
   
      // Wait 5 seconds before refreshing (we don't want to stress the server)
      System.Threading.Thread.Sleep(5000);
    }
  }
  catch (exception ex)
  {
    // Log code for exceptions
  }
}
Thanks to SequelMate and HansAnderss

Saturday, 11 April 2015

Reading sensitive parameters in a script

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!).
Related Posts Plugin for WordPress, Blogger...