Bulk Import keeps failing
The code below works fine in SSMS but fails when run under a sql job
Generates the history below
Bulk Insert Import_Data
From 'D:\Data\Data.txt'
With
(
FIELDTERMINATOR ='\t',
ROWTERMINATOR ='\n',
MAXERRORS = 3000,
FIRSTROW = 2
)
GOGenerates the history below
Date 6/27/2013 3:15:00 AM
Log Job History (SERVER: Import)
Step ID 1
Server WIN-RAQ4
Job Name SERVER: Import
Step Name Import File
Duration 00:00:52
Sql Severity 16
Sql Message ID 4864
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: NT SERVICE\SQLSERVERAGENT. ...er for the specified codepage) for row 2530, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2553, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2578, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2635, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2736, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2779, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2794, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2898, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3015, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3054, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3215, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3359, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3452, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4130, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4347, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6866, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7002, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7309, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9921, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10033, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11474, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11557, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12048, column 24 (SellingPrice).
[SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12075, column 24 (SellingPrice). [SQLSTATE 42... The step failed.
edtechdba
Since it looks like the issue here has to do with your SellingPrice column, do you see anything unusual with the value in this column (in your .txt file)? Such as a "$" sign or special numeric formatting?
The solution to this error is you should use the correct datatype according to the data in the file from which you want to load the data.
Probably data.txt contains strings which you are trying to convert to an Integer. (guessing here) so if you change the datatype from INT to VARCHAR for SellingPrice might help you.
Probably data.txt contains strings which you are trying to convert to an Integer. (guessing here) so if you change the datatype from INT to VARCHAR for SellingPrice might help you.
ASKER
I have checked and double checked and triple checked the file and the file only has 5 digits, no commas, no dollar signs, nothing fancy
also, I can not change the datatype otherwise the next step of moving data from that table to the main table will fail
also, I can not change the datatype otherwise the next step of moving data from that table to the main table will fail
ASKER
The question that I can not get an answer to is:
Why does the exact same script work in SSMS and not as a step in a job?
The bulk import command is already told to ignore errors
Than why, why, why, why, why.... would it fail on the agent and not on SSMS?
I believe that is the key
Any suggestions would be greatly appreciated.
Why does the exact same script work in SSMS and not as a step in a job?
The bulk import command is already told to ignore errors
Than why, why, why, why, why.... would it fail on the agent and not on SSMS?
I believe that is the key
Any suggestions would be greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
TRUST ME
I get the point about it being different
And that is why it is not working
My question is
How do I get it to work?
Because I already know it doesnt work
What do I need to change it to in order for the import to work even when executed under the sql server job agent
I get the point about it being different
And that is why it is not working
My question is
How do I get it to work?
Because I already know it doesnt work
Bulk Insert Import_Data
From 'D:\Data\Data.txt'
With
(
FIELDTERMINATOR ='\t',
ROWTERMINATOR ='\n',
MAXERRORS = 3000,
FIRSTROW = 2
)
GOWhat do I need to change it to in order for the import to work even when executed under the sql server job agent
Ok. Consider temporarily changing the startup account for the SQL Server Agent service to your account and then report back to see if there are any errors. This should help to narrow the problem down.
ASKER
Done.
That did not resolve the issue
Is there a difference in the buffer size for SSMS and SQL Agent?
That did not resolve the issue
Is there a difference in the buffer size for SSMS and SQL Agent?
That did not resolve the issue
I did not say it would. But if you don't think I am helping any that is fine with me.
I did not say it would. But if you don't think I am helping any that is fine with me.
ASKER
I was merely saying that we can eliminate that as a cause
I had also reached out to a few of my friends and they recommended looking at the Event Viewer
I did, no errors were logged there either.
And I do appreciate your help.
I had also reached out to a few of my friends and they recommended looking at the Event Viewer
I did, no errors were logged there either.
And I do appreciate your help.
Ok.
In order to troubleshoot this further:
In order to troubleshoot this further:
1.
Post the output from SQL Server Agent after you changed the startup account to your Windows account.2.
Confirm that the file 'D:\Data\Data.txt' is on SQL Server.3.
When you execute it successfully (without using SQL Server Agent) are you logged on to the server using Remote Desktop or directly at the console?ASKER
1. Output
Date 7/28/2013 3:15:00 AM
Log Job History (ABCD)
Step ID 1
Server WIN-DHDOR
Job Name ABCD
Step Name Step 1
Duration 00:00:49
Sql Severity 16
Sql Message ID 4864
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: WIN-DHDOR\Smit Shah. ...er for the specified codepage) for row 47, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 17144, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 17203, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 17326, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 17515, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 17596, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 17843, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 18393, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 22421, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 22427, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 26193, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 28133, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 36752, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 41815, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 42677, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 44476, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 44830, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 44990, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 49411, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 55182, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 56933, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 60201, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 60969, column 24 (SellingPrice). [SQLSTATE 42000] (Error 4864) Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 61118, column 24 (SellingPric... The step failed.2. Confirmed
Confirmed that file exists3. Remote Desktop
Remote Desktop
If you are saying that when you logon as WIN-DHDOR\Smit Shah you can run it fine, yet when the SQL Server Agent is using your same account it fails, than I am out of ideas.
The only other thing you may want to verify is that the SQL Server Agent is not interpreting the decimal symbol differently from you. In other words, take a look at the text file, if the selling price is using a comma for a decimal symbol, perhaps SQL Server Agent is using a decimal point as in the US.
The only other thing you may want to verify is that the SQL Server Agent is not interpreting the decimal symbol differently from you. In other words, take a look at the text file, if the selling price is using a comma for a decimal symbol, perhaps SQL Server Agent is using a decimal point as in the US.
ASKER
So, finally got this to work
The solution was to change the datatype to decimal vs int
I could not have changed it to varchar because I would have than had to do data cleanup before the select > insert into statement.
Thank you for your help
Smit.
The solution was to change the datatype to decimal vs int
I could not have changed it to varchar because I would have than had to do data cleanup before the select > insert into statement.
Thank you for your help
Smit.