SQLSearcher
asked on
SSIS Inserting data into Oracle from Flat file
Hello Experts Exchange
I have a text file with 1832 records inside that I want to insert into a Oracle table using SQL Server Integration Services.
However when I run the package only 832 files go to the database the rest 1000 go to a error file, with errorcode of 8 and the errorcolumn of 0.
The file is ok as I have run a test into SQL Server and the data went into the database fine. But I need the data to be in Oracle.
I can't put a example file on here as it is sensitive information in the file.
There are no errors in the SSIS package.
I am using a ODBC destination to get the data into oracle.
Does anyone know what I can try next to get all the data into Oracle?
Regards
Graham Rock
I have a text file with 1832 records inside that I want to insert into a Oracle table using SQL Server Integration Services.
However when I run the package only 832 files go to the database the rest 1000 go to a error file, with errorcode of 8 and the errorcolumn of 0.
The file is ok as I have run a test into SQL Server and the data went into the database fine. But I need the data to be in Oracle.
I can't put a example file on here as it is sensitive information in the file.
There are no errors in the SSIS package.
I am using a ODBC destination to get the data into oracle.
Does anyone know what I can try next to get all the data into Oracle?
Regards
Graham Rock
ASKER
Hello Icohan
I have tried to save the file in a ASCII format but the import did not work with the same results as before.
I have change the SSIS package so the destination was ODBC and changed it to a OLE DB and all records imported in fine.
How do I get the ODBC to work?
Regards
SQLSearcher
I have tried to save the file in a ASCII format but the import did not work with the same results as before.
I have change the SSIS package so the destination was ODBC and changed it to a OLE DB and all records imported in fine.
How do I get the ODBC to work?
Regards
SQLSearcher
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
I stuck with OLE DB, thank you for your help.
Based on the above my guess is that you have a UNICODE character in the source file that is not compatible with the UTF8 ORACLE database column where it should go and if possible...save a copy of the original source file somewhere safe, Edit the text file and then save as (different or same name does not matter) but please use ASCII not Unicode encoding under file type then try to import the pure ASCII text saved file and see if it works.