Failed to establish datasource connection

chitravallivenkat · Post by **chitravallivenkat** » Fri Aug 07, 2009 9:05 am

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

chulett · Post by **chulett** » Fri Aug 07, 2009 9:37 am

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.

chitravallivenkat · Post by **chitravallivenkat** » Fri Aug 07, 2009 9:44 am

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

chulett · Post by **chulett** » Fri Aug 07, 2009 10:02 am

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.

chitravallivenkat · Post by **chitravallivenkat** » Fri Aug 07, 2009 10:09 am

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,

ray.wurlod · Post by **ray.wurlod** » Sun Aug 09, 2009 5:07 pm

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.

chitravallivenkat · Post by **chitravallivenkat** » Mon Aug 10, 2009 9:24 am

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,

chulett · Post by **chulett** » Mon Aug 10, 2009 9:37 am

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

I find that very hard to believe.

ArndW · Post by **ArndW** » Mon Aug 10, 2009 9:48 am

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

chitravallivenkat · Post by **chitravallivenkat** » Mon Aug 10, 2009 11:57 am

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,

chulett · Post by **chulett** » Mon Aug 10, 2009 12:58 pm

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.

chitravallivenkat · Post by **chitravallivenkat** » Thu Aug 27, 2009 10:53 am

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