Post reply

Weird behaviour with Execute SQL Parameter mapping

  • January 13, 2010 at 10:04 am

    #138261

    I have an SSIS package on Server A and it runs fine. The first step is an Execute SQL Task that populates all of the variables based off a settings table in the DB. This works fine on Server A.

    We setup a server B for testing. It is exactly the same server spec, the only difference is I have installed SQL2K5 Enterprise 64bit SP3 (SP2+hotfixes on Server A).

    When I run the job, it errors [Execute SQL Task] Error: Executing the query "SELECT bla bla bla" failed with the following error: "The type of the value being assigned to variable "User::RUN_STATUS" 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.

    Why would it be doing this? Am I missing something? The connection is fine and is pointing to the correct server. The query and parameter mapping and variables are all the same on both Server A's version and Server B's version. ODD!

    Thanks for any help!

    :hehe:

  • January 19, 2010 at 9:31 am

    #1105514

    Sorry to bump but does anyone have an inkling?

  • January 19, 2010 at 12:59 pm

    #1105699

    Is the property "Run64BitRuntime" set to TRUE?

    The property can be found under SSIS Project Property Pages -> Configuration Properties -> Debugging

    Here is an article that goes into a lot of details on running 32 vs 64 bit.

    http://www.sql-server-performance.com/articles/biz/SSIS_An_Inside_View_Part_4_p1.aspx

  • January 20, 2010 at 2:51 am

    #1105910

    Yep that is set. Thanks for trying.

  • January 20, 2010 at 12:20 pm

    #1106256

    does the SQL task have an inline SELECT statement? If so is it calling a view or straight a table? If it is calling a view has something changed in the underlying table. If is it calling a table, where is that table located at in server A or B? You know how it is with errors sometime, it says one thing but the problem is totally something different. Also rights issues, have you checked that? What results do you get when you run the SQL statement manually in SSMS?

  • January 20, 2010 at 1:34 pm

    #1106315

    What data type is User::RUN_STATUS?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • January 21, 2010 at 4:00 am

    #1106580

    This is the statement -

    SELECT @RunStatus = RUN_STATUS

    ,@FileFolder = EXTRACT_FOLDER

    ,@AlertTo = ALERT_TO

    ,@AlertCC = ALERT_CC

    ,@StartDate = START_DATE

    ,@EndDate = END_DATE

    FROM dbo.Job_Settings

    WHERE PROFILE_DESC = @SettingsProfile

    All parameters are mapped and use the same Data Type as the matching variable. RUN_STATUS is a boolean. I am simply trying to collect the variable values formt he settings table.

    If I run it without Run_Status the error moves onto the next field. It is just having a nightmare mapping correctly.

  • January 21, 2010 at 4:07 am

    #1106583

    The idiot has fixed it! @Settings_Profile wasn't being passed in correctly. :crazy:

    Thanks for your help people, discussing it opened my eyes!

Viewing 8 posts - 1 through 7 (of 7 total)

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