CODEWITHSUNDEEP
sql-servervariablesssis
Nathan TSQL_Jr
Nathan TSQL_Jr

Reputation: 117

SSIS Pass Null value through SQL Execution task

UPDATE Addresses 
SET CountyCode = Null, 
    UPDATED_TIME = ?,
    UPDATED_BY = ?
WHERE (Country = ? AND Town = ?) 
  AND CONVERT(VARCHAR(10), CREATED_TIME, 112) = '20140602'

Can someone please tell me why the above code is failing to execute from my SSIS Execute SQL Task Component.

I have a countryCode column (INT) and I want to set it null but somehow the SSIS does not like the code above.

I have an int variable in my SSIS package but it does not allow null values and so defaults to 0 but I don't want to pass a 0 value as 0 is used for something else.

is there a way I can pass a null value with using a stored procedure or ideally no use a transformation component

Upvotes: 0

Views: 2115

Answers (1)

Eray Balkanli
Eray Balkanli

Reputation: 7990

SSIS does not support nullable types for it's built in variables. Therefore, when an integer value is created it is set to 0 by default.

For your situation, I would set CountryCode to -1 (0 works here for countrycode but in the future it might represent an item, safer to approach with a negative value) and update the table like that. Then, I would use an OLE DB Command in Data Flow, after getting the table by using OLE DB Source and set the statement of OLE DB Command like below:

Update Addresses set CountryCode = NULL where CountryCode = -1

Upvotes: 0

Related Questions