• @jason

    I don't think so, the default has to be set as part of the DDL for the table and you can't use variables in that situation.

    The SSIS package calls a stored procedure which builds a dynamic Bulk Insert script which is then executed with exec sp_executesql() I do this because the date formats in the source file are DMY which you can set in t-SQL but not with the Bulk Insert Task in SSIS. I opted not to go down the Data Transfomation task route as I have already built the format files and the only transform I need to do is on the date; it didn't seem worth it to confuse future maintainers (I am a contrator) with having to remap the SSIS tasks each time the data changes - much easier to edit the BCP format files.