Post reply

Execute SQL task fails

  • June 17, 2010 at 3:54 am

    #143819

    Hi,

    I have execute sql task with the following query: (OLE DB provider)

    select * from emp where empid=?

    I have done proper mapping of input parameters for index 0. Also I have used package variable to get the result set.

    The same query I am writing as follows: (ADO.Net provider)

    select * from emp where empid=@id

    I am properly mapped the @id to the input parameter and also set one variable to get the result set.

    Now the problem is my both queries are failing.

    Further I am writing one more type of query where alias are used as follows:

    select empid as ID, ename as NAME from emp where empid=? (OLE DB provider)

    declare @id int, @name varchar(50)

    select @id=empid,@name=ename from emp where empid=@id (ADO.Net provider)

    both the queries are working fine in ACCESS and SQL Server databse respectively when executed individually.

    Both the queries are failing when used with Execute SQl task.

    Can you pls help me in writing correct syntax or provide the correct mapping of the input parameter and resultset for above queries.

  • June 17, 2010 at 7:29 am

    #1181549

    Can you give us the exact error message?

    Does SSIS complain or SQL Server?

    If the latter is true, you may want to start SQL Profiler on your target database and see if a query comes in and how it looks like. Then just pick the query and try it in SQL Management Studio.

    WM_JUSTMY2CENTS

    Guenter

  • June 17, 2010 at 10:09 pm

    #1181901

    I am getting the error as follows:

    "Either the input parameters or Resultset is not set correctly".

    I am not able to understand where the error is.

    -Anuya

  • June 17, 2010 at 11:35 pm

    #1181924

    Have you tried running the package with the SQL Profilter active? This often gives you a hint where the problem lies (e.g. misspelling of a SQL keyword).

  • June 18, 2010 at 12:10 pm

    #1182305

    The best way to use Execute SQL task is to have the SQL statement as an expression.

    Whatever parameters you want to pass should be declared as user variables.

    In the execute sql task editor, open the Property Expressions Editor.

    Chose the property of "SQLStatementSource" to be configured.

    Open the expression builder and enter the query in the expression--

    Example:

    "SELECT * FROM EMP where ENAME = '"+ @[User::EmpName]+"'"

    Here EmpName is a user variable of String Type.

    Using Property Expressions work with any type of connection.

    ------------------
    Why not ?

Viewing 5 posts - 1 through 4 (of 4 total)

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