Query: Execute SQL Task

• Kapil S

  Grasshopper

  Points: 10

  More actions

  January 22, 2007 at 3:51 am

  #122736

  Hi,

  Using Execute SQL Tasks

  I am using 2 exec sql tasks and doing sql search in 1st and storing output in variable. I am passing the variable value in 2nd exec sql task and writing to

  some other table. I am writing string/ integer values to the database.

  (passing values from sql to variables using parameter mapping option)

  I am getting following errors in both cases:

  IN Case of STRING VALUE

  [Execute SQL Task] Error: Executing the query "test_etable" failed with the following error: "String[1]: the Size property has an invalid size of 0.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  In case of INTEGERS

  [Execute SQL Task] Error: Executing the query "prod1" failed with the following error: "The type of the value being assigned to variable "User::quantity" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  Have also tried changing datatypes or default values.

  Kindly suggest how parameter passing from stored procs with sample example.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 22, 2007 at 3:59 am

  #684641

  Please will you post the two queries if you can, as well as a description of what input/output parameters and global variables you are using.

  Thanks

  John

• Kapil S

  Grasshopper

  Points: 10

  More actions

  January 22, 2007 at 4:20 am

  #684643

  proc1:

  id int , name nchar out

  select name from xyz where id = @id

  end proc1.

  1st query:

  (in exe sql task editor - in sql-stmt calling proc1 as

  proc1 and parameters specified in the parameter mapping using @)

  call to proc1.

  2nd query:

  insert into pqr (name1) values (@name)

  @name is output derived from query 1.

  On running the package the mentioned 2 errors appear

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 22, 2007 at 8:38 am

  #684718

  What Connection Type are you using?  Have a look at this and see if it helps:

  http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=101249&SiteID=1

  John