SQL Server Performance Forum – Threads Archive

Database Connectivity Failing during ETL

Hi there, I am encountering a problem when running SSIS. The error message reports that there is a connection error. The error message is consistent but can occur on different steps of the job. When I re-run the step manually it completes successfully. It is as if the database is temporarily going offline or possibly into Recovery mode? We encountered the same error previously when we moved to a new server which had a smaller drive used to store the log files. So it could be linked to Transaction log not growing fast enough or becoming too big? My other theory is that it is related to specifications of the server:- CPU Intel Xeon 3.2 GHz RAM 1.5 Gb Data Drive: 40Gb used 13Gb free Log Drive: 8Gb used 31Gb free The memory meets the minimum requirement but is this enough? The databases being used are of the following size:- Staging Database: 3.0 Gb Reporting Database: 3.2 Gb AutoGrowth, By 10%, restricted growth to 1024Mb If anyone has experienced this issue before or knows of any recommended configuration settings related to the Transaction Log or hardware specs, then it would be greatly appreciated. Thanks, Malcolm [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Here is one of the error messages:- Message Executed as user: MULTISQLService. …n 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:49:27 AM Error: 2007-07-04 08:50:03.33 Code: 0xC0202009 Source: ImportSecondaryFISCAL Connection manager "GL Data Mart" 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 Native Client" Hresult: 0x80004005 Description: "Login failed for user ‘MULTISQLService’.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot open database "GL_DataMart" requested by the login. The login failed.". End Error Error: 2007-07-04 08:50:03.35 Code: 0xC020801C Source: Dim Invoice Billed Dim Invoice Disposal [3140] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connecti… The package execution fa… The step failed.

Make sure user has the proper access based on this error…
"Login failed for user ‘MULTISQLService’"
What is the default db for this user…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

,… also "cannot open database….", so you have to see whether it is available or not in addition to checking whether this user has privielge on that database under corresponding tables. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

…got similar error-messages on transferring a SQL2000 Database to a new sql2005 – Server. Im shure that all the privileges are ok, the rights, the roles and so on! Because everything works if i import the tables one by one, all the damned 183 tables!
The warning that such a bulk of tables could run into troubles is a joke. SQL2000 DTS did such job’s without any whisper!
What’s the matter with that sql2005 thing! Does anybody know what is going on? Ice

]]>