Custom SSIS sql task failed

  • I am developing a custom SSIS task for running sql task. But it fails with error - I'm trying to set the resultsetbinding for some parameters , to be used by next task in workflow.

    Error: 0xC0014054 at CustomSSISTask: Failed to lock variable "User::id" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

    Error: 0xC002F210 at CustomSSISTask, Execute SQL Task: Executing the query "SELECT id FROM sysobjects WHERE name = 'sysrowsets..." failed with the following error: "Failed to lock variable "User::id" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

    ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Warning: 0x80019002 at CustomSSISTask: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    Task failed: CustomSSISTask

    Here is the code sample I'm trying ,

    public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser,

    IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)

    {

    MessageBox.Show("testing:");

    try

    {

    // Add the SQL Task

    Package package = new Package();

    package.Executables.Add("STOCK:SQLTask");

    Microsoft.SqlServer.Dts.Runtime.Variable variable = package.Variables.Add("id", false, "User", 0);

    // Get the task host wrapper

    TaskHost taskHost = package.Executables[0] as TaskHost;

    // Get the task object

    ExecuteSQLTask task = taskHost.InnerObject as ExecuteSQLTask;

    // Set core properties

    task.Connection = connections[0].Name;

    task.SqlStatementSource = "SELECT id FROM sysobjects WHERE name = 'sysrowsets'";

    task.SqlStatementSourceType = SqlStatementSourceType.DirectInput;

    // Add result set binding, map the id column to variable

    IDTSResultBinding resultBinding = task.ResultSetBindings.Add();

    //IDTSResultBinding resultBinding = task.ResultSetBindings.GetBinding(0);

    resultBinding.ResultName = "variable";

    resultBinding.DtsVariableName = variable.QualifiedName; //"User::id";

    task.Execute(connections, variableDispenser, componentEvents, log, transaction);

    }

    catch (Exception ex)

    {

    throw new ArgumentException(ex.Message);

    }

    return DTSExecResult.Success;

    }

    It doesnt throw any exception but custom task fails.

    Later I will be also using parametersetbindings to pass some input parameters to this task , since I'm stuck for out param - blocked moving ahead.

    Thanks!

  • The value of a custom variable can be a literal or an expression:

    If you want the variable to contain a literal value, set the value of its Value property.

    If you want the variable to contain an expression, so that you can use the results of the expression as its value, set the EvaluateAsExpression property of the variable to true, and provide an expression in the Expression property. At run time, the expression is evaluated, and the result.

    For more details LINK

  • Thanks , but this dint help.

    I changed code as

    <<<

    Microsoft.SqlServer.Dts.Runtime.Variable variable = package.Variables.Add("id", false, "User", 0);

    // Get the task host wrapper

    variable.EvaluateAsExpression = false;

    >>>>

    Thanks!

  • To add my further investigation, when I run this line of code to run query

    task.Execute(connections, variableDispenser, componentEvents, log, transaction);

    I see that variableDispenser doesn't have User::id variable contained in it.

    Ques : - How do I add variable to variableDispenser object. This object is coming from overridable execute function.

    DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser,

    IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)

    Thanks!

  • When you edit the script component, there is an option where you can reference the local variables to the script component.

    1. Create a UserID variable

    2. Assign it to script component.

    3. How to code it

  • Thanks ,

    I tried this sample, but the issue here is when I try to add new variable to custom task - it doesnt work and throws error.

    variableDispenser of this overridden method needs to have the User:id variable , which I'm not able to add to this object.

    public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser,

    IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)

    Thanks!

  • When i say create a variable you need to create a variable manually at designer level 1st. then reference that variable in the script component. then you will be able to view that variable.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply