DSXchange

Hi,

I post this after seached the word "Failed to establish datasource connection" in this forum. In all the forums, it has been mentioned that "Failed to establish datasource connection" might be happened due to connection string or userid or password or DB or server name wrong.

But Here is different. the job has only two stages - OLEDB and Hash.
|-------|------------------>|-----------|
|-------|------------------>|-----------|
|-------|------------------>|-----------|
OLEDB|------------------>|Hash------|
|-------|------------------>|------------|
|-------|------------------>|------------|

OLEDB stage has settings all default. and the same to Hash stage (Dynamic - 30). It has 17 links that are connecting different tables and some of them (3) are connecting to the same table.

When job is running, most of links are establishing a connection, but few (1 or 2) dont establish a connection. At different times, different link(s) dont establish connection. Finally job aborts by throwing the error "Failed to establish datasource connection and OLE DB provider error". I could not identify the issue.

I appreicate if any one gives input on this.

Is there a problem with OLEDB stage or SQL server or Network issue or any other?

Regards,
Venkat

If which ones fail change run over run, then I'd say you have a resource issue and the instance/server/whatever can't support that many simultaneous connections.

chulett wrote:If which ones fail change run over run, then I'd say you have a resource issue and the instance/server/whatever can't support that many simultaneous connections. ...

Hi Craig,

I could not get your point. could you please expand it?

Regards

Basically asking if the problem is consistent or not. In other words, do the exact same connections fail each time or do the failed ones change with each run? If it is the latter, then I'd be checking with your DBA and/or SysAdmin to see if you are having resource issues.

Thanks Craig.
Each time it fails at different links and looks like consistent, only 10% is finishing successfully. But anyway i will check with DBA to find out any resource issue happens.

Regards,

Is there a limit on the number of simultaneous connections to the database server? For example Oracle is limited by the number of listeners that have been configured.

ray.wurlod wrote:Is there a limit on the number of simultaneous connections to the database server? For example Oracle is limited by the number of listeners that have been configured. ...

Hi Ray,

Thanks for your response.

But there is no limitation on simultaneous connections on SQL server. Is there any limitations on Datastage side?

Regards,

chitravallivenkat wrote:But there is no limitation on simultaneous connections on SQL server.

I find that very hard to believe.

They do have a per-cpu with no concurrent limit licensing option, but in this case they probably do not have that license.

SQL Server Licensing

ArndW wrote:They do have a per-cpu with no concurrent limit licensing option, but in this case they probably do not have that license.

[url=http://www.microsoft.com/sqlserver/2005 ... nsing.aspx]SQL Serv ...

Hi ArndW,

We have a license on SQL server 2000 Developer edition. There seems no limit on concurrent in this.

Regards,

OK, perhaps, but still you don't have "unlimited resources" to go along with your "unlimited connections". You will hit a limit at some point.

Hi all,

thanks for your valuable response and support.

this problem got resolved. Due to some incorrect settings in the DB config, this issue happened. After corrected the config file, this problem is gone.

In the DB config, the "set working set size" was set to 1. This should be set to 1, when the value of "Max and Min server memory" is user defined and both has same value.

But at our environment, this was 1, when "Max and Min server memory" is automatic. Actually in this case, "set working set size" should be 0.

After changed to 0, it is working fine.

Regards,
Venkat