When you are logging a process, one of the main things you need is to do is track dates and times. However SSIS has a bit of a gotcha. If you are using Variables, you can't use the DateTime data type.

If you do, and say you use an Execute SQL component to return data from an ODBC connection, it won't do what you may think it should. When I did it in this configuration, the variable populated with '1900-01-01 00:00:00.000' even though I had used getdate() to populate the variable.

When I changed the variable to be a string and changed the getdate() to convert(varchar, getdate(), 121), the variable populated with the correct date. Just so you know the convert with the option 121 formats the date into the ODBC standard for dates.

There was another gotcha that I hadn't expected, say you want to put that date back into the SQL server through another Execute SQL component, you can't use any of the date types as the input. In order to it to correctly be inserted into a date field you have to use the input type of NVARCHAR. Then everything will transfer as expected.