1

I am connecting from SSIS to an Oracle database to import data into SQL Server. I can preview the data in SSIS:

enter image description here

The issue is when it imports this into SQL Server, the column FOUR shows as all NULLS for all 110 million records. When there is data in there. From reading about this error excel reader reads the first 8 rows and determines the type/data?

enter image description here

This is the table structure:

enter image description here

I have tried to use a data conversion to DT_STR which didn't work. I also tried INT and that column is still showing as NULL.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Marc
  • 81
  • 5
  • Make sure that columns are mapped correctly in the OLE DB Destination component. Besides, Why do you mention Excel? Are you importing data from Oracle or Excel? – Hadi Jan 30 '22 at 11:44
  • 1
    Thanks Mate the mapping is where the issue was. I forgot I added column 4 after the initial load. Mapped and worked. Such an easy fix I missed. Thanks. Also I only mentioned Excel because I was reading and wondering if the oracle connector has the same issue. Cheers – Marc Jan 30 '22 at 11:48
  • @Ali, Will do in the future like i said new to this. However I understand thanks – Marc Jan 30 '22 at 12:06
  • i just deleted the answer does that mean Hadi can answer it? – Marc Jan 30 '22 at 12:07
  • @Hadi are you able to mark as answered plz. – Marc Jan 30 '22 at 12:23
  • Dear Marc, I was ok with writing the answer by yourself since it didn't require considerable effort. And no problem, I will write it. – Hadi Jan 30 '22 at 12:27
  • feel free to check the [following page](https://stackoverflow.com/tour) to learn more about asking questions, answering, and accepting answers. It is highly recommended before posting on Stack Overlow. Good Luck – Hadi Jan 30 '22 at 12:33

1 Answers1

3

Since Excel is not a relational database, OLE DB tries to discover the most relevant metadata from the Excel worksheet by investigating the data. This is not the case in Oracle.

Please make sure that your columns are mapped correctly in the OLE DB Destination.

Hadi
  • 36,233
  • 13
  • 65
  • 124