ETL错误

Transfrom user_daily_activities_count to Drpt
select
 date(actiontime),
 userid,
 planid,
 logtype,
 
count(1)
from singtel_drptetl.ott_activitieslog al
where
 
actiontime>="2013-01-01"
 
and actiontime<"2013/08/21"

group by userid, planid, logtype
order by userid, planid, logtype
2013/08/21 13:25:42 - Checks if files exist -
ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :

2013/08/21 13:25:42 - Checks if files exist -
ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
Column 'UserID' cannot be null

解决办法：太难添加where条件 UserID>0

 

2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unexpected error
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : org.pentaho.di.core.exception.KettleException:
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Error converting line
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Couldn't parse field [Number(15)] with value [dcf3d66bd49ba884e9474ef5                                                                            ], format [#.#] on data row [1126406].
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unexpected conversion error while converting value [ClientVersion String(15)] to a Number
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ClientVersion String(15) : couldn't convert String to number
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unparseable number: "dcf3d66bd49ba884e9474ef5                                                                            "
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :  at org.pentaho.di.trans.steps.textfileinput.TextFileInput.convertLineToRow(TextFileInput.java:844)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :  at org.pentaho.di.trans.steps.textfileinput.TextFileInput.processRow(TextFileInput.java:1149)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :  at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :  at java.lang.Thread.run(Unknown Source)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleException:
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Couldn't parse field [Number(15)] with value [dcf3d66bd49ba884e9474ef5                                                                            ], format [#.#] on data row [1126406].
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unexpected conversion error while converting value [ClientVersion String(15)] to a Number
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ClientVersion String(15) : couldn't convert String to number
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unparseable number: "dcf3d66bd49ba884e9474ef5                                                                            "
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :  at org.pentaho.di.trans.steps.textfileinput.TextFileInput.convertLineToRow(TextFileInput.java:731)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :  ... 3 more
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleValueException:
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unexpected conversion error while converting value [ClientVersion String(15)] to a Number
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ClientVersion String(15) : couldn't convert String to number
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unparseable number: "dcf3d66bd49ba884e9474ef5                                                                            "
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :  at org.pentaho.di.core.row.ValueMeta.getNumber(ValueMeta.java:1451)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :  at org.pentaho.di.core.row.ValueMeta.convertData(ValueMeta.java:3070)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :  at org.pentaho.di.core.row.ValueMeta.convertDataFromString(ValueMeta.java:3267)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :  at org.pentaho.di.trans.steps.textfileinput.TextFileInput.convertLineToRow(TextFileInput.java:691)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :  ... 3 more
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleValueException:
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ClientVersion String(15) : couldn't convert String to number
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unparseable number: "dcf3d66bd49ba884e9474ef5                                                                            "
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :  at org.pentaho.di.core.row.ValueMeta.convertStringToNumber(ValueMeta.java:708)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :  at org.pentaho.di.core.row.ValueMeta.getNumber(ValueMeta.java:1404)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :  ... 6 more
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: java.text.ParseException: Unparseable number: "dcf3d66bd49ba884e9474ef5                                                                            "
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :  at java.text.NumberFormat.parse(Unknown Source)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :  at org.pentaho.di.core.row.ValueMeta.convertStringToNumber(ValueMeta.java:704)
2013/08/21 16:38:02 - 文本文件输入.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :  ... 7 more
2013/08/21 16:38:02 - RepMysql - Connection to database closed!
2013/08/21 16:38:02 - SRC_Loginhistory - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 错误被检测到!
2013/08/21 16:38:02 - import to DRPTSRC_Loginhistory.0 - 完成处理 (I=0, O=1119233, R=1119233, W=1119232, U=0, E=0
2013/08/21 16:38:02 - SRC_Loginhistory - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 错误被检测到!
2013/08/21 16:38:02 - 文本文件输入.0 - 完成处理 (I=1126407, O=0, R=0, W=1126405, U=0, E=1
2013/08/21 16:38:02 - SRC_Loginhistory - SRC_Loginhistory
2013/08/21 16:38:02 - SRC_Loginhistory - SRC_Loginhistory

Exact的时候选UTF-8.避免乱码

或者 有回车换行符 下面的sql语句去除换行符

SELECT
   [LoginHistoryID]
  ,[CreateDate]
  ,[MemberID]
  ,[LoginDate]
   ,REPLACE( [ClientVersion]   ,CHAR(10),'') AS ClientVersion
        ,REPLACE( [HandsetCode],CHAR(10),'') AS HandsetCode
   ,REPLACE( [Model]   ,CHAR(10),'') AS Model
        ,REPLACE( [DeviceType],CHAR(10),'') AS DeviceType
FROM  SingTelReportDB.[dbo].[LoginHistory]
WHERE CONVERT(varchar(10),CreateDate,23) >=   ${Exact_StartDate}
AND CONVERT(varchar(10),CreateDate,23) <    ${Exact_EndDate}