February 10, 2009 at 3:17 am
I have created several packages, all of which extract data from tables in MS Access databases and load it into tables in 64-bit SQL Server 2005. All the packages have been imported into and are stored in the MSDB database under Integration Services. When I right-click to obtain the short-cut menu and select execute, the packages run fine. I have also scheduled the packages under SQL Server Agent and they all run fine.
However, if I create a package importing from an MS Access QUERY rather than a TABLE the package runs without difficulty exactly as the packages described above except when I add it to an SQL Server Agent schedule - in which case it always fails with the error information detailed below. I can leave the job step in the SQL Server Agent schedule alone, i.e. just change the package to work off a table rather than a query and then reimport it - it runs fine, but not when I use a query (I have tried running it directly off a query pre-written in Access and writing a query in the SSIS control properties box against the Access tables).
Any ideas?
Error information:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 09:56:39
Info: 2009-02-10 09:56:39.62
Code: 0x4004300A
Source: Data Flow Task DTS.Pipeline
Description: Validation phase is beginning.
End Info
Progress: 2009-02-10 09:56:39.62
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2009-02-10 09:56:39.66
Source: Data Flow Task
Validating: 50% complete
End Progress
Error: 2009-02-10 09:56:39.96
Code: 0xC0202009
Source: Data Flow Task OLE DB Source [2048]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
End Error
Error: 2009-02-10 09:56:39.96
Code: 0xC02020E8
Source: Data Flow Task OLE DB Source [2048]
Description: Opening a rowset for "LinkFirstSCandDetail" failed. Check that the object exists in the database.
End Error
Error: 2009-02-10 09:56:41.26
Code: 0xC004706B
Source: Data Flow Task DTS.Pipeline
Description: "component "OLE DB Source" (2048)" failed validation and returned validation status "VS_ISBROKEN".
End Error
Progress: 2009-02-10 09:56:41.26
Source: Data Flow Task
Validating: 100% complete
End Progress
Error: 2009-02-10 09:56:41.26
Code: 0xC004700C
Source: Data Flow Task DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2009-02-10 09:56:41.26
Code: 0xC0024107
Source: Data Flow Task
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 09:56:39
Finished: 09:56:41
Elapsed: 2.203 seconds
February 10, 2009 at 5:08 am
UPDATE
I now do and don't know what is causing the above issue. Basically the package runs ok providing the tables the query is based upon are in the same Access database as the query itself. If they are attached tables, then the problem above appears.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply