How to format dd-mmm-yy values ​​in a flat file for smalldatetime during data import?

Question

I have a flat file that is imported into SQL Server through an existing SSIS package. I need to make changes to the package to place a new field in a flat file. A new field is a date field that is in a format dd-mmm-yy(for example, 25-AUG-11). The date field in the flat file will be either empty (for example, space / space) or filled with the date. I have no control over the date format in a flat file.

I need to import a date field in a flat file into an existing SQL Server table, and the target field data type is smalldatetime.

I suggested importing the date as a string into the loading table, and then converting to smalldatetime when taking data from the loading table. But is there another possible way to parse the dd-mmm-yy date format to load it directly into the smalldatetime field without having to use the conversion to smalldatetime from the load table. I cannot think of how to parse the date format, especially the month. Any suggestions are welcome.

Answer 1

, , . , SSIS ETL , , . , . SSIS , .

Derived Column Data conversion .

SSIS 2008 R2. , , SQL.

SQL .

SSIS SQL Flat. , .

SSIS " ". , OLE DB. Flat OLE DB , . Derived (DT_DBDATE)SmallDate. , , . SmallDateTimeValue OLE DB Destination.

, , .

, , , . , . , Derived- , - . , . . . OLE DB , , .

. " ". Error and Truncation from Fail component Redirect row. .

, , . , .

, . 2 . , , , 2 .

, SSIS.

Answer 2

SMALLDATETIME, . , - SQL Server, /. SSIS 25-AUG-2011 , , SMALLDATETIME .

-?