[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1571
  • Last Modified:

SQL Server 2008 - Package data source connection string ignored.

Hello,

I have been trying to install RSExecutionLog_Update in order to monitor and analyse my reporting services usage.
After following every step in the following guide - http://msdn.microsoft.com/en-us/library/ms161561.aspx the package worked in my test environment.

However, when doing the same in my production environment, when I try to run the job I started getting the following error:

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Login timeout expired".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured

Initial investigation lead me to this articles which solved this error
http://blog.mclaughlinsoftware.com/2009/05/16/fix-sql-server-2008-client/
and another one about changing my Data Source to 127.0.0.1 instead of (local).

My problem now is that when I configure the data source for the RSExecutionLog_Update as follows: Data Source=127.0.0.1;Initial Catalog=ReportServer$SUPPORTSPACE;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;

I get the following job error:

rror: 2009-12-17 13:21:22.62
   Code: 0xC0202009
   Source: RSExecutionLog_Update Connection manager "Source"
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Login failed for user 'S2REPORTS\AnalysisAdmin'.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Cannot open database "ReportServer" requested by the login. The login failed.".


Whatever I try to put in the "Initial Catalog" parameter (Test, XXXXXX) I get the same error and it's clear I do not ask for ReportServer database.
Furthermore, when I create a dummy database called ReportServer, it seems like it is connecting and I get a statement failed error.

Please note that I also tried most variations of connection strings from here: http://www.connectionstrings.com/sql-server-2008

Appreciate the help,
Thanks.
0
supportspace
Asked:
supportspace
1 Solution
 
supportspaceAuthor Commented:
Solved.

The solution was to configure the RSExecutionLog_Update.dtsConfig file that is over riding the instance specified in the data source.
0
 
QPRCommented:
accept your own solution as the answer and the question will be cleaned up.
cheers
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now