SQL Server Performance

Using Parameter in an Execute SQL task within a Control Flow

Discussion in 'SQL Server 2005 Integration Services' started by WingSzeto, Nov 25, 2008.

  1. WingSzeto Member

    In the Control flow design area, I create an Execute SQL task. The SQL statement I am using is as follows:
    Select * into tmpEmployer from tblEmployer where EmployID = ?
    EmployerID is defned as Integer in our SQL table.
    I have set up a variable say varEmployID which has a scope for the entire solution. When I parse the statement It said "The query failed to parse. Paramter information cannot be derived from SQL statements Set parameter information before preparing command.". If I execute the statement, it still give out errors like resultset is not set, parameter is not set, or query is not correct
    In the Execute SQL Task Editor, there is a 'Parameter Mapping' setting. I have tried to put in some info there but it is not working. Am I supposed to set up an entry in 'Parameter Mapping'? If so, bsides the variable that I have set up, what information do I need to set up for the Direction, DataType, ParameterName and Parameter Size? After I set up these, what do I need to change in my SQL statement for it if any?
    wingman
  2. ranjitjain New Member

    Hi,
    In Parameter mapping you need to set parameter name which is required by the procedure.
    Click on parameter mapping and select user variable which you have created to pass value as
    input to the procedure. select direction as input and datatype as long and finally type the
    procedure parameter name to map value from package user variable.
    If your procedure execution is returning OUTPUT parameter then reapeat above steps with different params and output direction.
    If procedure is returning a resultset then in the general section specify the format of resultset
    i.e. single row result or full resultset or none.
    In case of single row, in resultset section, in the result name type the returned column name and map it with a user variable created in your package.
  3. WingSzeto Member

    Thank for the detail instructions.
    I got hung up on this part of your statement "......finally type the procedure parameter name to map value from package user variable". Where is the package user variable coming from? I do define a user variable, varEmployID in my VB project solution, which I entered it in the variable name in the Parameter Mapping window. But I don't have a package user variable. If I need to create one, where do I do that?
    Let me show you the full SQL statements that I am trying to do.
    Truncate table test.dbo.tblemployer
    go
    Insert into test.dbo.tblemployer
    select * from production.dbo.tblEmployer where EmployID = ?
    In the Parameter mapping window, I typed in as follows:
    Variable name: User::varEmployID
    Direction: Input
    Data Type: LONG
    Parameter Name: ???? (not sure what to do here, I tried to type in varEmployID again here but when I execute the code, it failed and I listed the error at the end of this email, if I typed in any name, same error)
    Parameter Size: -1
    As far as the returning result set type, I set the type to ResultSetType_None because the result is going into another table. Am I right on this? Also, I don't use Output parameter in this code.
    Error message:
    [Execute SQL Task] Error: Executing the query "Truncate table test.dbo.tblemployer " failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Share This Page