0

I am using SSIS for ETL. Source and destination databases are Oracle. When I run job through SQL agent its prompts me with the following error:

enter image description here

This table contains 5 date columns which are creating this issue.

I have tried all possible solution but it didn't work. It does not seems data issue as I rerun job on those selective dates which worked perfectly. On full load it failed.

enter image description here

MT0
  • 143,790
  • 11
  • 59
  • 117
anasyasir
  • 1
  • 2
  • 'invalid datetime format' seems to be the main culprit. Try to open and run the the package in e.g. Visual Studio (BIDS) step by step (add some breakpoints). – Karel-Jan Misseghers Nov 09 '20 at 15:03
  • i have updated my question. there is null issue in date datatype. – anasyasir Nov 09 '20 at 17:31
  • It is theoretically possible to put [5-digit years into Oracle](https://stackoverflow.com/a/59761035/1509264) or by using older JDBC drivers (but don't if you can help it). It would be better to sanitise your data to put in sensible values for those dates. – MT0 Nov 09 '20 at 18:54

2 Answers2

1

The bottom error message is:

Data Flow: Task:Error: SQLSTATE 22007, Message: [Microsoft][ODBC Oracle Wire Protocol driver]Invalid datetime format. Error in parameter 17.

You have an Invalid datetime format. You need to fix it by correcting either the data or the format model you are using but, since you haven't included any code, we can't help further.

MT0
  • 143,790
  • 11
  • 59
  • 117
  • I have updated my question. Attached second snap for code and data result. i did some work and I found out that there is some issue in nulls. not null values and format is ok. without nulls, data travels. for null values i tried to use nvl() function to replace nulls but it didn't help too. if you see snap. there are nulls against"10000" as in Year. which is impossible for DATE datatype. – anasyasir Nov 09 '20 at 17:30
0

I have a similar issue, the difference is my source is the SQL Server database and the destination is Oracle database. I converted the source DateTime columns to type String first and then they were loaded to destination date columns successfully.

June1620
  • 3
  • 2