none
OLE DB Command - How to handle NULL value Parameters?

    Question

  • I've got a very simple SQL update to do as part of a SSIS package we're using and i'm using an OLE DB Command task toperform the update.

    The update statement is

    UPDATE TableName
    SET Field1 = ?
    WHERE Field2 = ?
    

    The problem is one of the values for field 2 is NULL. however records where Field 2 IS NULL aren't getting updated.  I tried changing the SQL to

    UPDATE TableName
    SET Field1 = ?
    WHERE 
    Field2 = ?
    OR
    (? IS NULL AND Field2 IS NULL)
    

    however it wont let me map the same input colum to multiple parameters, and duplicating input columns with a derived column seems a bit of a fudge

    I must have spent a good hour or 2 searching for a solution but to no avail. 

    Any ideas?

    Cheers

    Wednesday, December 22, 2010 4:30 PM

Answers

All replies

  • If you want to use the same parameter twice in an update operation in an OLE DB Command, you need to make your SQL a little more complex and introduce some variables.  The following ought to work better - of course, you'll have to adjust to use the correct data types.

    DECLARE @Field1 AS CHAR(10)
    DECLARE @Field2 AS CHAR(20)
    SET @Field1 = ?
    SET @Field2 = ?
    
    UPDATE TableName
    SET Field1 = @Field1
    WHERE 
    Field2 = @Field2
    OR
    (@Field2 IS NULL AND Field2 IS NULL)
    
    

    Todd McDermid's Blog Talk to me now on
    Wednesday, December 22, 2010 5:04 PM
    Moderator
  • Cheers for the reply Todd

    Unfortunately i'm getting an error "Syntax error, permissions violation, or other non specific error"

    basically as soon as i try and set the declared variables to the paramter values it errors, i've added the SQL a line at a time as a test and as soon as it gets to

    SET @Field1 = ? it doesn't like it

    any ideas?

     

    Thursday, December 23, 2010 9:26 AM
  • Not sure - I've done this a couple times, I'm sure...

    Your other alternative is to use a Derived Column component immediately before your OLE DB Command, and create a new column containing the data of Field2, then you'll have two columns you can map to your original statement.


    Todd McDermid's Blog Talk to me now on
    Friday, December 24, 2010 8:10 PM
    Moderator