Long delay on SSIS Job validation and Obvious delay on Execution when Source RetainSameConnection=False

Question

Hello all,

I don't know why but I'm having to bear a long delay at the package Validation step when either executing or loading a package.
It is a rough supossition, but it seems that everything that implyies the apperture of a connection creates a delay. Opening a package or executing the package from bids, agent or even manually with dtsexec in both 32/64 bits causes the same results.

The OleDb NativeSql sources and destinations all come from the same local server that executes the instance of SQLserver2005 and BIDS. The authentication is done through AD (integrated security) and the agent is employing a proxy account for my etl user. Even with local Sqlserver accounts I get the same error.

On the other hand, viewing the activity of the processes (dtsexec and sqlserver) I can easily pinpoint in packages where the control flow has a linear structure (dataflow_1 ->dataflow_2->...->dataflow_N), there is a gap in time between query and query in which both dtsexec and Sqlserver are doing nothing.... until the execution burst arrives. When I say nothing I really mean nothing... no I/O, no CPU, no paging... just wait.
It is very frustrating to check that changing the RetainSameConnection parameter in the connection to True makes those gaps almost dissapear. After the initial validation time (about one minute), then the behaviour of SSIS is seems just normal. The same package that takes 15min to execute with concurrent conexions is executing in about 7 minutes keeping the same connection.

However, this 'hack' does not help for those packages where there is actually concurrent execution going on as one query overlaps and blocks the execution of other... the same for insertions... so it fails saying that the connection is busy!

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Connection is busy with results for another command".

Anyway, there is no query concurrency over the same connection! so, even if it didn't fail, it would still queue the commands for the only connection! The purpose of this setting is to be able to rollback a transaction that spans over several dataflows... but that is hardly the need/case in my control flow...


The fact is that keeping the same connection alive diminishes the query-to-query time to almost nothing leaves me thinking that somehow the process of opening a new connection is for some reason slow. Any clues about why? I've tried normal sqlserver accounts but I still get the same behaviour... so i've partially ruled out some kinf of AD dns problem, but I must admit it's far from being my area of expertise. I have also try to research if there's any possible limiting factor in sqlserver of limiting the connections for the same user, but the couple of easy settings in the connections tab of the server properties window in MSSMS like concurrent connections (0) seem to be setup just fine.

I am kind of splitting hairs here, and although in the process I've made some performance improvements in other areas, I still face the same basic problem...


Any ideas around there????


Thank you very much,


Francisco.

PS: For the record, the resources of the server are perfectly fine. It has loads of unused ram (even at the peak of the etl process), and there's virtually no paging going on from the dtsexec side.

---

Francisco Isla, Technical BI Consultant

Answer 1

Francisco, it does sound like you have identified your problem - for whatever reason it is taking a long time to initialise the connection to the server.

Just as a simple check, if you are on the box that executes the SSIS packages and you try to connect to the target SQL Server using SSMS, do you get the same delay?

If so it strongly suggests there is some problem with security and specifically authentication - as you note, once the connection is open everything runs swiftly, its just acquiring that initial connection. I'm not sure how you would troubleshoot this - it gets into the area of network admins to resolve.

Cheers, James

---

James Beresford @ http://www.bimonkey.com SSIS / MSBI Consultant in Sydney, Australia

Answer 2

Hello James,

    Your conclusions seem to be pointing towards the same line of investigation I was heading, but somehow it still does not add up. The fact that the delay occurs even for native SQL server users. logon from the same machine, does not really make sense. I would understand a penalty when resolving a the server name, but that is not the case. I would also understand there was a penalty when using an AD user, as the logon is delegated somewhere else.

However I still can not  make my head around the fact that the same delay is introduced when logging on/opening connections, locally and with a native SQL server user.

There must be something related, or badly configured... but I have the feeling that it is at a SQL server instance level.

I think those knowledges might need a deeper understanding of the Logon process of the SQL Server than I possess.


Please... any suggestion on how to proceed?


Thank you very much, Francisco.

---

Francisco Isla, Technical BI Consultant

Answer 3

Sorry for "breathing life" into the thread, but I would really need some help on this.

Regards,


Francisco Isla.

---

Francisco Isla, Technical BI Consultant