URGENT: PARAMETER ISSUES WITH SQL TASK

URGENT: PARAMETER ISSUES WITH SQL TASK

• surajguru

  Think I might have it for you.

  Create a new Variable called Out and is of type string

  Create a new Execute SQL Task.

  Add the database connection (OLEDB)

  Keep ResultSet at None

  in the sqlstatment line enter in sp_GET_SQL_QUERY_FOR_SSISGRP

  In the Paramaters mapping screen enter in

  Variable name :User::Out

  Direction Output

  data Type: Nvarchar

  Parameter Name 0

  Try this, it worked for me.

  
  

• MariaSh

  SimonS, I tired option 1 again, with bypassprepare set to true (statement: exec[dbo].[sp_GET_SQL_QUERY_FOR_SSISGRP]  OUTPUT and parameter User::sql_query out varchar 0 ) and got the error
  
  [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
  Task GET SQL QUERY failed
  
  Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.
  
  Any ideas.
  
  
  

• Robertwell

  That didnt work either so i used the scripting task to call the stored procedure and set my variable and it works just fine. Now i have another headache. I set the sqlcommand source expression for my datasource component to the variable that was set by my script task (User::SQLQUERY). When I run the package, it tries to set the sqlcommand connection for the datasource component before even executing the package (that is to say before executing the script task). If i leave the default of my variable to an empty string, it yells at me before even executing because its trying to set the sqlcommand of the source component and its empty. Is there anyway to not execute or valide the datasource component till its it turn in the data flow queue at which it will set its sqlcommand to that retrieved by the scripting task.
  
  
  

• joost g.

  You need to set the delay validation property of the task to true. This will delay validation until it is executed.
  
  

• Philip ACH

  I think from the error the stored procedure is returning more than one line in the result set, Can you post your stored procedure. If you are returning more than one line then it needs to be a Full record set and the type of varable is an object. Make sure you have the words Set NOCOUNT ON as the first line of your procedure as this can cause issues if missing.

  
  

• Noddy74

  You should be able to use

  usp_mystoreprocedure OUTPUT

  With a single parameter, with a parameter name of 0 and a direction of Output and type of nvarchar (or varchar depending on your sp). Make sure you set resultset to "None".

  Parse Query will not work (it is not for SP calls), the task will work

  The other option is to use a single row resultset and assign your variable that way.

  
  
  

• Claudio Maras

  Thanks for the advice. Well the issue with using an "object" data type on a variable is then you can't do anything in an expression with that variable. I've read multiple forums and they all say the execute sql task is a good way to get a value from a database and then put that value in a variable, but I've never actually seen anybody answer this specific question on how it's done or why it's so hard. If anybody has a step by step example you would really help me out. Use execute SQL task to get a value out of a database and put it in a variable.

  Thanks,

  Phil

  
  

• Jian Hu

  Hi guys,
  I delayed the validation and that worked just fine. I set my variable with the right sql query and in my expression for the datasource component sqlcommand, I entered @Sql_Query (that the name of my variable). After the script task has set the variable, the data flow task generate an error as below
  
  Error: The type of the value being assigned to variable "User::Sql_Query" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
  
  Its wied but the variable is a string. I used breakpoint and the immediate window to find out the value of my variable as the scripting task(see script for script task below) executes. In using the immediate window, it confirmed that my variable was set to the query string. But yet, the datasource component in the data task flow cant set its sqlcommand to the variables value. Do I need to typecast @Sql_Query to anothe type for the expression to pick up the string value. I tried somethinkg like @(User::Sql_Query).ToString but not acceptable.
  
  
  Public Sub Main()
  
  Dim sqlstring As String
  Dim constr As String
  'constr = "Data Source=SPIDERMAN;Initial Catalog=Swamp_Cypess; Integrated Security=SSPI;Auto Translate=False "
  
  constr = "Data Source=SPIDERMAN;Initial Catalog=Swamp_Cypess;Integrated Security=True "
  
  'Dim con As SqlConnection = DirectCast(Dts.Connections("CYPRESS0927").AcquireConnection(Nothing), SqlConnection)
  Dim con As SqlConnection = New SqlConnection(constr)
  con.Open()
  Dim commd As SqlCommand = New SqlCommand()
  commd.Connection = con
  commd.CommandType = CommandType.StoredProcedure
  commd.CommandText = "sp_GET_SQL_QUERY_FOR_SSISGRP"
  commd.ExecuteNonQuery()
  
  commd.CommandType = CommandType.Text
  commd.CommandText = "SELECT QUERYSTATEMENT FROM QUERYTEXT WHERE TBLNAME = 'CJP' "
  Dim drd As SqlDataReader = commd.ExecuteReader
  While drd.Read
  sqlstring = DirectCast(drd.GetValue(0), String)
  End While
  
  'Dim vs As Variables
  'Dts.VariableDispenser.GetVariables(vs)
  'vs.Unlock()
  'System.Diagnostics.Debug.Print("my string " & constring)
  con.Close()
  
  ' Assign the query statement to DTS variable
  Dts.Variables("Sql_Query").Value = sqlstring
  
  
  
  Dts.TaskResult = Dts.Results.Success
  End Sub
  
  End Class
  
  Sorry guys but i feel SQL Server 2005 is not as easy as i thought it would be as compared to 2000
  
  
  
  

• Phil Wherrett

  Heh,

  I've had the same problem with the execute sql task. No matter what when I try and get a value from a database and assign it to a variable using this task I always get the data type error. The only thing it will allow is a object data type value set for the variable. I've posted numerious user groups, googled my butt off, and still nobody seems to know how to do this simple task.

  I'm also getting the "unspecified error" now in a completely seperate SSIS package while trying to Parse Query. This error only happens on our production server, I can change the connection and it works just fine.

  Any help on these two subjects would be appreciated it.

  Thanks,

  Phil

  
  

• Senthil Kumar.T.D

  I included Set NOCOUNT ON and tired it again and it didnt work. Got the error
  
  Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.
  
  Here is the script for the create stored procedure.
  
  ------------------------------------------------------------------------------------------------
  CREATE PROCEDURE [dbo].[sp_GET_SQL_QUERY_FOR_SSISGRP]
  (@SQLQUERY NVARCHAR(MAX) = ' ' OUTPUT )
  AS
  
  BEGIN
  SET NOCOUNT ON
  
  DECLARE @RECORD AS DATETIME
  
  
  SELECT @RECORD = MAX(ISSDATE) FROM dbo.Tmp_CJP
  
  IF @RECORD IS NULL
  BEGIN
  
  SET @SQLQUERY = 'SELECT ACCOUNT, CHECKNO, PUNBR, GRNBR, CLAIM, PAYTO, PAYEE, PALT, ISSDATE, POLICY, PNAME, PAY, ESSN, SEQ, TYPE, POST, DEPT, PROVID, CRECDT, CINCDT, BATCH, REISSUE, PYMTADJ FROM CJP'
  
  END
  ELSE
  BEGIN
  
  SET @SQLQUERY = 'SELECT ACCOUNT, CHECKNO, PUNBR, GRNBR, CLAIM, PAYTO, PAYEE, PALT, ISSDATE, POLICY, PNAME, PAY, ESSN, SEQ, TYPE, POST, DEPT, PROVID, CRECDT, CINCDT, BATCH, REISSUE, PYMTADJ FROM CJP WHERE ISSDATE > ' + [Swamp_Cypess].[dbo].[fn_GETELDODATE](@RECORD) + ' AND ISSDATE <= ' + [Swamp_Cypess].[dbo].[fn_GETELDODATE](GETDATE())
  END
  
  -- INSERT SQL STATMENT IN QUERYTEXT TABLE -------------
  DECLARE @TABLE AS NVARCHAR(20)
  SELECT @TABLE = TBLNAME FROM QUERYTEXT WHERE TBLNAME = 'CJP'
  
  IF (@TABLE IS NULL)
  BEGIN
  
  INSERT INTO QUERYTEXT
  (TBLNAME, QUERYSTATEMENT, DESCRIPTION )
  VALUES (N'CJP',@SQLQUERY, N'SQL QUERY STATMETENT TO DOWNLOAD DATA FROM ELDRO IN SSIS PACKAGES')
  END
  ELSE
  BEGIN
  
  UPDATE QUERYTEXT
  SET QUERYSTATEMENT = @SQLQUERY
  WHERE TBLNAME = 'CJP'
  END
  
  END
  
  -----------------------------------------------------------------------------------
  
  Any thing jump into mind
  
  
  

• Raj-Xps

  That is very weird I got it going as I descibe.
  

• iHD

  tackett wrote:

  Heh, I've had the same problem with the execute sql task. No matter what when I try and get a value from a database and assign it to a variable using this task I always get the data type error. The only thing it will allow is a object data type value set for the variable. I've posted numerious user groups, googled my butt off, and still nobody seems to know how to do this simple task. I'm also getting the "unspecified error" now in a completely seperate SSIS package while trying to Parse Query. This error only happens on our production server, I can change the connection and it works just fine. Any help on these two subjects would be appreciated it. Thanks, Phil

  
  
  So what's the problem using an "object" data type Use a foreach loop to enumerate over that object to get your results out.
  
  Also, you should open up a new thread for your unspecified error issue unless it deals with this. Just know that you won't be able to parse much on a stored procedure.
  
  
  

• shals

  I believe you should be able to do option 1 but set bypass prepare.
  
  

• Bertil Syamken

  I also tried "SELECT = QUERYSTATEMENT FROM QUERYTEXT WHERE TBLNAME = 'CJP' with the right parameters ( User::sql_query out varchar 0 ) and got the error
  
  [Execute SQL Task] Error: Executing the query "SELECT = QUERYSTATEMENT FROM QUERYTEXT WHERE TBLNAME = 'CJP'" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
  
  Is the this kind of T-SQL statement not allow --> SELECT = QUERYSTATEMENT FROM QUERYTEXT WHERE TBLNAME = 'CJP
  
  
• URGENT: PARAMETER ISSUES WITH SQL TASK