SSIS Data flow failing

Question

SSIS Data flow failing

below86

SSChampion

Points: 11551
More actions
May 8, 2013 at 8:00 am

#273711

I have a data flow that has been working for months. Starting this week it will transfer some of the rows then just fail. I am copying data from a DB2 database to SQL Server.
Here is the error that I get from the agent:
The step did not generate any output. The return value was unknown. The process exit code was -529697949. The step failed.
That's all it shows which seems odd, ever other error I've looked at there is usually a lot more information.
I see no errors in the sysssislog for the process failing.
This process moves 3 different tables from DB2 to SQL Server. This morning the first 2 data flows worked, al data moved. The third data flow moved around 376,000 rows of the 393,000 rows it should have. When I tried to rerun the data flow it only moved about 99,000 rows.
Any thoughts? Any thing else you need to know?
Thanks in advance for your help.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

Have you checked the package? Did something happen to the return variables that you set up? It also sounds like something has changed in the table, or you are getting an incorrect data type coming in. Does your first table that the data is pumped in to, have valid data types, or all varchar that will be certain to accept the data pump?

Andrew SQLDBA

below86 SSChampion Points: 11551 More actions · Answer 2

In my testing I am pulling the data from the same source DB2 table, but putting it to a different SQL server(DEV). I can run this process on DEV and it will move all the rows with no errors. Just kicking off the process in BIDS. When I move the process to our UAT server is when I get this problem. Like I said before this has been working just fine on the UAT server. I've checked the destination tables on both DEV and UAT and they are defined exactly the same. To me it almost seems like it is 'timing' out on the UAT server or some how loosing connection. But I don't know how to prove that. I did check and the last time the UAT server was patched, and rebooted was two weeks before this error started showing up.

Thanks,

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 3

Check your settings and create smaller batches if necessary (see screenshot below).

Also, I would import into non-indexed staging tables. If your staging tables have indexes, add a drop indexes step and then re-create them after a successful import. Then do a MERGE to insert/update the production tables.

I had this same issue recently and it was always the last of 3 tables in which the import would fail after 600,000+ rows. I dropped the indexes and reduced the batch size and that fixed the problem in my case.

Also, you may want to try running each step sequentially rather than all at once.

below86 SSChampion Points: 11551 More actions · Answer 4

The destination table is dropped and recreated before the data flow step. My process builds the table on DB2, then moves it to SQL. Then the next one builds, then moves, and so on. I have the 'Table lock' checked and the 'Check constraints'. There are no constraints, not sure why I have that checked. For the 'Rows per batch' and the 'maximum..' I did have 50000 set in both. I was advised to change both to 2000, it still failed with the same error.

I also tried rerunning several times, some times it moved a couple thousand rows, some times none.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 5

Yeah, these imports can be a real PITA...especially so when everything's been working fine and then sudden failure. So I must ask: any server updates? patches? permission changes? Or perhaps you're reaching the capacity limit for this instance or for tempdb?

Maybe more likely is that some bad data has crept in such as a duplicate or weird ISO characters pasted in from something like WordPress. :crazy: Does the error repeat predictably on any particular row? If so perhaps you can manually search for and remove the offending record.

below86 SSChampion Points: 11551 More actions · Answer 6

I did over hear that the UAT server was running low on some space, could have been the tempdb.

Maybe that is what it is, since the smaller files loaded and the larger one is failing. That could explain why it moves a different amount of rows each time I try it. I would think the DBA should be able to tell me that?

The error does not repeat, and I can pull from the same source and move the data to our DEV server without any errors, all rows get moved.

I agree this is becoming a huge PITA. :w00t:

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Calibear Ten Centuries Points: 1377 More actions · Answer 7

Try running the job manually from within the SSIS project. You should get more details then what SQL Server Agent is providing.

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455

below86 SSChampion Points: 11551 More actions · Answer 8

I've only been working with SSIS for about a year. So when you say "Try running the job manually from within the SSIS project." are you talking about running it in BIDS? When I run it through BIDS it works with no errors, but it is pulling the data to our DEV server. When it runs in the agent it is moving the data to our UAT server.

I guess I could force it to point to the UAT server when I have it open in BIDS and see what happens.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 9

If you haven't already done so and you have access to the SQL Server Agent node I'd take a peek at the job error logs to see if that tells you anything.

below86 SSChampion Points: 11551 More actions · Answer 10

I was not able to see anything in the logs.

I opened my process in BIDS and I forced my destination connection to be the UAT server. The process ran with no errors and all data was moved.

Here are the suggestions I got from my DBA who finally got it to run on UAT.

First is to set "ValidateExternalMetadata" to True on the source, especially if this is a non SQL-Server database such as DB2. This helps to keep everything up to date and reduces the chance for inaccurate or corrupted metadata. More importantly, configure Data Flow database sources to use "SQL Command" rather than "Table or View". Write the SQL Command to use a column list rather than *, and only include the columns specifically needed for processing. Even better is to use a stored procedure, which will execute faster and provide the metadata more quickly using the pre-compiled query plan.

I still don't understand how when I run it and push it to the UAT server it works but when it runs as an agent on UAT it needs to be done this way. I'll look at making the changes he suggest, but I don't understand. Any thoughts?

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

below86 SSChampion Points: 11551 More actions · Answer 11

We have two more servers for me to check this on, out INT and production. I set this job up on each and each ran without errors. There has to be something on the UAT that is not set up correctly or it could be the issue with low space.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 12

belowery (5/9/2013)
There has to be something on the UAT that is not set up correctly or it could be the issue with low space.

Does your UAT SQL Agent Login and/or proxy differ from the ones in prod and dev? If so, have you confirmed those logins have the same access as the other levels?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

below86 SSChampion Points: 11551 More actions · Answer 13

My process is set to move 3 files between DB2 and SQLserver. The first two run fine, smaller data, the third fails. These run one at a time with in the process.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.