Database Connection Failing During ETL | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Database Connection Failing During ETL

Hi there,<br /><br />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.<br /><br />It is as if the database is temporarily going offline or possibly into Recovery mode?<br /><br />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:-<br /><br />CPU Intel Xeon 3.2 GHz<br />RAM 1.5 Gb<br />Data Drive: 40Gb used 13Gb free<br />Log Drive: 8Gb used 31Gb free<br /><br />The memory meets the minimum requirement but is this enough?<br /><br />The databases being used are of the following size:-<br /><br />Staging Database: 3.0 Gb<br />Reporting Database: 3.2 Gb<br /><br />AutoGrowth, By 10%, restricted growth to 1024Mb<br /><br />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.<br /><br />Thanks,<br /><br />Malcolm [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><br />Here is one of the error messages:-<br /><br /><i>Message<br />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.</i>
Please adhere to the forum policies and do not post multiple – duplicate posts – for further reference. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
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.