CODEWITHSUNDEEP
sql-serverdatessisflat-file
WiredTheories
WiredTheories

Reputation: 231

Flatfile Datatype Issue with SSIS to SQL Server

Loading a flatfile to SQL Server via SSIS. I have few Columns with DATE datatype in SQL Server Destination table. What should be the corresponding datatype to be defined in connection manager in SSIS? I tried all the DATE datatype available in the connection manager but it results in conversion errors as below:

[OLE DB Destination [199]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

[OLE DB Destination [199]] Error: There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[Created on] on OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data.".

[OLE DB Destination [199]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC0209077 occurred, and the error row disposition on "OLE DB Destination.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

The flatfile source date format is in DD-MM-YYYY format.

Upvotes: 3

Views: 7082

Answers (1)

EricF
EricF

Reputation: 173

Copied from: http://www.sqlservercentral.com/Forums/Topic609337-148-1.aspx

Since you have DD-MM-YYYY format, you need to flip it to YYYY-MM-DD, I think, for the (DT_DATE) cast to work correctly. I've changed the substring parameters accordingly.

Create a Derived Column transformation, create a new column, and put this in the expression field. Use your own field name, obviously.

(DT_DATE)(SUBSTRING([field],7,4) + "-" + SUBSTRING([field],4,2) + "-" + SUBSTRING([field],1,2))

Personally I name the derived column as CONV_[field] just so I know it's a converted field and what field I converted. Use whatever naming convention you like.

Use the derived column in your destination mapping.

Longer answer.

Make sure you have your source coming in as a [DT_STR] data type.

Hope this helps.

Upvotes: 2

Related Questions