October 3, 2014 at 7:41 am
Hi all
I've got an odd one here.
I have a package that copies some data from one SQL server database to another, into staging tables. That data is then transformed and copied into live tables. This is all done via an SSIS package run by a SQL job.
It has been completely solid for months. This morning the job failed with the unhelpful "DTS_E_PRIMEOUTPUTFAILED" error in the SQL job, at the part where it copies the data into staging.
So I ran it manually from BIDS and it worked without any errors. Odd, I thought.
I then ran it manually from the server where it executes, but via the SSIS execution tool rather than the SQL job. No errors.
However, when I run it from the job, it fails. It always partially copies the data (1158 rows should be copied, so not a lot of data) but fails after 813 rows. I have looked at the data in row 814 and it all looks fine.
I am logged into the server with the same account that the SQL job runs under - it uses a credential mapped to a domain account.
So I temporarily put a restriction in to not import the row at 814, and now it works!! (it imports 1157 rows)
There are other tasks in the package that copy way more rows than this, and they are working fine.
So:
-Why does it work manually but not from the job?
-What is it about row 814 that's odd? I have copied the whole dataset into Excel, and there is nothing in that row that is odd, I am sure of it. And if there was something odd, why would it only affect the package when run from the job and not manually?
Finally, I put the row 814 back in, and took out another row at random, and it works!!
I am completely stumped.
October 3, 2014 at 8:12 am
Update: If I force an additional row in, it also works.
So it for some reason doesn't like the number of rows it's importing to be 1158! (but only when run via a SQL job)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply