Post reply

SSIS: Execute SQL Task: Problems with paremeterse

  • November 28, 2013 at 8:28 am

    #283033

    I'm having a very elementary problem with parameters passed to an Execute SQL Task:

    I have two parameters defined at the package level:

    1. Input, type int32, value 1

    2. Output type int32, value 0

    In my task, I map the variable Input to the Parameters, direction input, type LONG, name 0.

    Then in the Result Set, I define the variable Output, result name 0.

    I put in a simple query:

    Select 1

    And set the result set to Single Row.

    I added a script task afterwards to show the results in a message box.

    I run the package and it works. I see '1' in the messagebox.

    Now, the trouble begins! I changed the query to:

    select ?

    and used the same parameter and result set mappings. This time, however, the task fails with the error message:

    [Execute SQL Task] Error: Executing the query "select ?" 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.

    I'm totally stuck! Since the simpe 'select 1' works fine, I think I can rule out the suggestions regarding the resultset or connection. That leaves the parameters. I just can't see what the issue can be.

    Any help would be greatly appreciated.

  • November 28, 2013 at 1:23 pm

    #1669720

    What if you rewrite the query?

    DECLARE @Input INT = ?;

    SELECT @Input;

    Also make sure the property ByPassPrepare is set to True.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • November 28, 2013 at 2:29 pm

    #1669729

    More info on this:

    From http://technet.microsoft.com/en-us/library/cc280502.aspx

    quote:

    --------------------------------------------------------------------------------

    SELECT, INSERT, UPDATE, and DELETE commands frequently include WHERE clauses to specify filters that define the conditions each row in the source tables must meet to qualify for an SQL command. Parameters provide the filter values in the WHERE clauses.

    --------------------------------------------------------------------------------

    What this does NOT say is that you cannot substitute parameters anywhere else! Not even in a case statement (wouldn't that be nice?)

  • November 28, 2013 at 2:44 pm

    #1669734

    quote]Koen Verbeeck (11/28/2013)

    What if you rewrite the query?

    DECLARE @Input INT = ?;

    SELECT @Input;

    Also make sure the property ByPassPrepare is set to True.[/quote]

    Now, that makes sense. ByPassPrepare did the trick (I had it set to False).

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

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