Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Custom SSIS sql task failed Expand / Collapse
Author
Message
Posted Wednesday, July 16, 2014 11:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 5:05 PM
Points: 4, Visits: 12

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!
Post #1593386
Posted Thursday, July 17, 2014 12:18 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 778, Visits: 1,380
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
Post #1593398
Posted Thursday, July 17, 2014 11:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 5:05 PM
Points: 4, Visits: 12
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!
Post #1593750
Posted Thursday, July 17, 2014 2:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 5:05 PM
Points: 4, Visits: 12
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!
Post #1593832
Posted Friday, July 18, 2014 12:22 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 778, Visits: 1,380
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
Post #1593917
Posted Friday, July 18, 2014 11:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 5:05 PM
Points: 4, Visits: 12
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!
Post #1594169
Posted Monday, July 21, 2014 1:42 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 778, Visits: 1,380
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.
Post #1594471
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse