Post reply

Deployed package never ends, where as the dev finishes in 2 minutes.

  • September 22, 2009 at 11:37 am

    #204886

    Hi all,

    I have a situation where I am testing a package I've built on my dev environment. The package works perfectly in the Dev, but never ends on the QA Server.

    Both machines are 32 Bit, both versions are the same : SQL Server 2005 SP2 Build 3042.

    I have a dev edition on my desktop, and a Standard Edition on the QA Server, that is the only difference.

    When I try to run the package, it simply never ends, and gives me no information. Is there anyway I can log what happens, to at least know where the package fails, or never "fails"?

    What do you guys propose?

    Thanks in advance,

    Cheers,

    J-F

  • September 22, 2009 at 11:49 am

    #1057019

    What's is doing from the DB perspective? Is it being blocked by some other process?

    J.D.

  • September 22, 2009 at 12:10 pm

    #1057038

    Opening a transaction, deleting all the data in 3 tables, and insert from flat files, with some validation as per total Customer/Invoice counts/InvoiceTotals.

    It only imports plain data to the DB, in a big transaction.

    No one uses this server but me, so I do not think it is being blocked.

    Cheers,

    J-F

  • September 22, 2009 at 12:55 pm

    #1057064

    I have more information, I added package Logging in the SSIS menu, and tried to sort things out.

    The last information I see in the logging is a "OnProgress" during the validation of 1 of the dataflows.

    Seems the "Validation of the dataFlow" never ends, and never gives progress.

    Is there anything I can check to see what makes it never end? Is there something I can do?

    The dataflow reads from a raw file that was generated in the previous dataflow, formats the data, and inserts it in the "Detail" Table. It looks pretty clear to me, nothing looks ODD in this, but the fact that the "RAW File" that gets read is not created once the package starts. It is created during the previous dataflow.

    That should not be a problem, since in dev, that works perfectly (I have the DelayValdiation switch on).

    Cheers,

    J-F

  • September 22, 2009 at 5:34 pm

    #1057205

    I'm not much into SSIS but my first dash at this would be to drop the package and rebuild it. It wouldn't be the first time I ever saw such a thing work.

    I don't know if it applies here but the other thing that you have to remember is that the "Developer Edition" is actually the "Enterprise Edition"... not everything you do on the Dev Edition will work on the Standard Edition.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 23, 2009 at 7:12 am

    #1057472

    Hey Jeff,

    Thanks for your input, sadly, it does not help me 😉 I've been stuck on this for 2 days now, and I can't get to deploy my package.

    I've tried to deploy my package on my dev environment, not as a debug package, but as a deployed one, and it works perfectly, sadly... I wish I could at least get an error message, there just seems to be some error during the validation process of the Lookup component...

    Just a wild guess, is there anyway the next dataflow cannot get the same transaction as the parent? Maybe that would result in a timeout, since the I've got 1 big transaction on the data. But still, it works perfectly on my Dev environment, so that does not sound accurate.

    I hate it when I cannot even see what heppens or what blocks the process, Is there any form of error I could see somewhere, any way to see blocking transactions that could occur within SSIS?

    Maybe putting a wait command before the validation of the Detail Dataflow? I don't know anymore.

    Thanks for any input, I'm completely stuck.

    Cheers,

    J-F

  • September 23, 2009 at 7:58 am

    #1057524

    Hi All, I might have another piece of the puzzle. When executing the package, after a few minutes, executing sp_who2 gives me the SPID of the importation, saying it's stuck on a "Execute", and the column BlkBy says "-2"

    Now, for the tricky question, what is a "-2" block?

    Thanks,

    Cheers,

    J-F

  • September 23, 2009 at 8:42 am

    #1057561

    Nice, I found the Problem, well, the Lock!

    By looking at the Activity Monitor, I can see there is a Table Lock waiting for this process. The table is the Header Table, which I just inserted in.

    Now, why does this happen on the QA Server, and not on my environment, I don't know, and I do not know how to fix it either.

    It's being blocked by SPID -2 ([UPDStats]), on a table Lock, any hints?

    Cheers,

    J-F

  • September 23, 2009 at 9:44 am

    #1057604

    Finally found the problem.

    When in a transaction with Distributed transaction coordinator, if you want to insert rows in a Header/Detail table, you need to insert in the header, but the default option has the "lock the table" checked. I removed that when inserting in the Header, and the detail was then able to query the just inserted data, to get the Identity of the Header.

    Everything works perfectly now, and I've learned quite a bit on blocking transactions, and how to find them

    Thanks to everyone,

    Cheers,

    J-F

Viewing 9 posts - 1 through 8 (of 8 total)

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