3

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.

gofr1
  • 15,741
  • 11
  • 42
  • 52
WiredTheories
  • 231
  • 7
  • 18
  • 1
    Choose any date sounding data type. In Flat File Source, go to Advanced properties. For each of those date columns, change the FastParse option to true. It should allow you to load the data. If it *doesn't* look for instances where the data isn't populated or they use a bad date. Update your question with those values and/or possibly ping me – billinkc Oct 22 '14 at 20:48
  • [FastParse](http://stackoverflow.com/questions/8186291/import-string-date-in-derived-column/8197667#8197667) reference example – billinkc Oct 22 '14 at 20:48
  • @billinkc I changed the settings accordingly. The Dataflow is validated but the Date column in SQL Destination table is null for all rows. The dataflow ignores the column on failed conversion and loads null values. I used date [DT_DATE] in flatfile source and the column in Destination table is [Date] – WiredTheories Oct 22 '14 at 21:55

1 Answers1

2

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.

gofr1
  • 15,741
  • 11
  • 42
  • 52
EricF
  • 173
  • 7
  • Perfect this did the trick for me. I went through a lot of blogs and tried a lot of things to fix the issue. Thanks for help :). – WiredTheories Oct 22 '14 at 22:11