This is an addendum to my previous blog post How to use a SharePoint list as a data source in your SSIS package.
If you are populating a SharePoint lists with a field of date type, you need to format the date as text, in the following format, in order for SharePoint to accept the input from SSIS.
‘yyyy-mm-dd’ or for one digit months or days ‘yyyy-m-d’
So if you’re using a SQL Query you can write something really ugly, like this:
CAST(DATEPART(Year, GETDATE()) AS varchar(4)) + ‘-‘ + CAST(DATEPART(Month, GETDATE()) AS varchar(2)) + ‘-‘ + CAST(DATEPART(Day, GETDATE()) AS varchar(2)) AS [Adjusment Date]