SSIS Data flow failing

  • 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.

  • 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

  • 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.

  • 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.

     

     

  • 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.

  • 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.

  • 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.

  • 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

  • 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.

  • 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.

     

  • 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.

  • 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.

  • 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

  • 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.

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

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