0

Question edited (big thanks billinkc!) in more detail as proposed answer wasn't resolving the problem

The SSIS package (see below) is moving BIRTHDATE column from one staging table into another. Starting from scratch Flat File Source to Staging table: enter image description here

Derived Column for the DATE (vachar) column:

(DT_STR,40,1252)((TRIM(BDAY) == "" ? 0 : 1) == 0 ? ((DT_STR,40,1252)"19000101") : ((DT_STR,40,1252)(SUBSTRING((REPLACE(BDAY,"/","")),5,4) + SUBSTRING((REPLACE(BDAY,"/","")),3,2) + SUBSTRING((REPLACE(BDAY,"/","")),1,2))))

Second package is trying to convert varchar to DATE format but fails: enter image description here enter image description here

Destination format in the second table is DATE

Error msg: [Data Conversion 2] Error: Data conversion failed while converting column "BIRTHDATE" (248) to column "BIRTHDATE" (6). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[Data Conversion 2] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Data Conversion.Outputs[Data Conversion Output].Columns[BIRTHDATE]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion.Outputs[Data Conversion Output].Columns[BIRTHDATE]" 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.

I did try using Derived Column with often proposed solutions but it didn't work. Last research solution was to use Data Conversion task but this one returns an error below.

Am I missing something? Thanks for any points guys! :)

Thomas Pollack
  • 59
  • 2
  • 11
  • 1
    I've used something along the lines of `SUBSTRING(DT_STR,10,1252)MyString,1,4) + "/" + SUBSTRING(DT_STR,10,1252)MyString,5,2) + "/" + RIGHT (DT_STR,10,1252)MyString,2)` in a derived column and defined the column as datetimestamp – MiguelH Oct 19 '15 at 07:55
  • Thanks Miguel! I've been using almost the same syntax in the derived column. No success :( Since I can cast that string in sql, I shouldn't have any problems casting it in SSIS but....anyway, thanks for the tip! – Thomas Pollack Oct 19 '15 at 08:01
  • "Source: "Microsoft SQL Server Native Client 11.0" and "Invalid character value for cast specification" imply this is actually a SQL error. What does your source SQL look like? Do you get this error regardless of whether you use a derived column or not? It would help if you posted the exact syntax you are using. – Nick.Mc Oct 19 '15 at 08:42
  • 1
    `yyyymmdd` can be parsed in TSQL but can't be parsed in the Flat File Connection Manager until you specify the FastParse option for that column - see linked duplicate – billinkc Oct 19 '15 at 11:57
  • Thanks billinkc! I've edited the question to give more details. Connection manager, FastParse didn't resolve the problem. I'm moving the data from one sql table with varchar, to another with date format. that is where I fail ;) – Thomas Pollack Oct 20 '15 at 00:41

0 Answers0