I have a package with SQL Server Table as its configuration file. This table has date variable. I use this date variable in my Execute SQL Task as Input to run a store Procedure which take this date and work on it. When I set this date variable to say 11/11/2009 in configuration table it works fine. But when I set this date variable to NULL and try to execute the SSIS package it doesn't take the NULL as date input to store procedure. Please advice how can I pass this NULL date to SSIS package and execute the store procedure with this NULL date variable a s InputThanksBR, AWM

What is the error? Have you executed the SP by passing NULL data as input parameter inside SSMS?Nitesh Rai- Please mark the post as answered if it answers your question

It works absolutely fine as expected from SSMS, when I execute the store proc with NULL passed as Input. In SSIS, Same thing works fine when this dtae variable is set to Some date, but when it set to NULL in configuration table , my SSIS package doesn't pick this NULL as the value for that Date.I am not sure why but SSIS some how not accpeting NULL as the Date variable where as if I give some date it works fine.My question how can I set the or work around to fix the configuration variable to read even NULL. BR, AWM

Could you please check the value of the variable (which is used as input parameter for SP) using a script task incase the date value is NULL. A work around could be: Dummy Script Task(ST) | -------------------------------------------- | | Execute SQL Task1(EST2) Execute SQL Task2(EST2)EST1: EXEC SPNAME NULLEST2: EXEC SPNAME ?Use precedence constraint between ST and EST1 and ST and EST2For ST and EST1: Use "Constraint and Expression" option and the expression would be: ISNULL(@[User::Date])For ST and EST2: Use "Constraint and Expression" option and the expression would be: !ISNULL(@[User::Date]) Nitesh Rai- Please mark the post as answered if it answers your question

You can't pass a NULL value to a Date typed variable, because in SSIS, variables of all data types except Object can't be assigned to NULL.The only way to achieve what you're attempting to do is to have another variable in your configuration - a boolean, probably - that indicates you want to use a NULL date. In your package, wherever you use the date, you're going to have to check that boolean variable to see if you should pass a NULL value, otherwise you'd pass the contents of the date variable.

Thanks Todd...Yes I see that DateTime variable in SSIS cannot be set to NULL and even with expression NULL(DT_DATE) the default value is set 12/30/1899 and I cannot change this to NULL. I wonder why it doesn't allow NULL. Anyways I regretly took 12/30/1899 and considering it as NULL in my Store Procedure.I didn't like it but thats the easiest one.BR, AWM

You can't pass a NULL value to a Date typed variable, because in SSIS, variables of all data types except Object can't be assigned to NULL. for all the programmers who read this, it actually makes sense. all ssis data types are value types except the system.object data type. null isn't a value -- it's null...which means that a value type variable cannot be assigned to a null. only object data types can be assigned a null.Duane Douglas, MCAD, MCITP | http://www.ssisbi.com | Please mark the post(s) that answered your question.

I understand that ssis variable are value types and therefore do not allow NULLs - however saying it makes sense is a typical programmers answer. In a real application it can make sense to want to assign NULL to a variable. Its a real pain working with SSIS variables.