In many SSIS packages variables control the workflow. Here is a quick and generic way to load the initial values from a database table instead of having to hard-code them into the package or use configuration files.
You may get an error when loading a string variable from a varchar(MAX) field via an Execute SQL Task. Here is the complete error I got when loading a variable named SQLStatement:
Error: 0xC001F009 at I_CTHNDL: The type of the value being assigned to variable “User::SQLStatement” differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
A common way to represent a date – often in mainframe systems – is in the format yyyymmdd. Unfortunately it is not easy to do calculations on such a representation. To be able to use the native T-SQL date functions such as datediff and dateadd you will need to convert it to the datetime or smalldatetime type.
y2008m03d28
Tom VdP