Column column filling CSV table behaves differently using SSIS
I have a CSV file
**date column**
06/04/1999
06/04/2000
.
.
.06/04/2017
I just load this file from a Csv file into a database table using SSIS
Flat file source for OLEDB destination
where the date column is absolutely DT_STR and the SQL table column is varchar (50)
But it behaves differently when I boot, sometimes it fills up like
**date column**
06/04/1999
06/04/2000
and at other times, for example
**date column**
1999-04-06
2000-04-06
I'm just trying to load the same as the Flat file, but why does it sometimes give "-". Can anyone suggest me why it behaves differently every time. It has no transforms (like Derived Columns) in SSIS
source to share
You need to convert these values to DateTime and then to String with the required format yyyy-MM-dd
usingScriptComponent Transformation
- Create
Flat File connection manager
andOLEDB Connection Manager
(source and destination) - Add to
DataFlow Task
- In
DataFlow Task
addFlat File Source
,Script Component
,OLEDB Destination
- your DataFlow should look like
Source --> Script --> Destination
- In the Script component Mark the "Time" column as input (assuming it's a name
inDateColumn
) and add the output column (ex:) of theOutDateColumn
StringDT_STR
-
In Script Write the following code: (using Vb.net)
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) If Not Row.inDateColumn_IsNull AndAlso _ Not String.IsNullOrEmpty(Row.inDateColumn.Trim) Then ' You can add more formats inside the following method Row.OutDateColumn = DateTime.ParseExact(Row.inDateColumn.Trim,New String(){"yyyy-MM-dd","dd/MM/yyyy"},New System.Globalization.CultureInfo("En-GB"), System.Globalization.DateTimeStyles.None).ToString("yyyy-MM-dd") Else Row.OutDateColumn_IsNull = True End If End Sub
-
Map
OutDateColumn
to destination column
source to share