SSIS Package Runs Fine; SQL Job Fails (DBF Involved)

by **SJHers** » Fri, 31 Oct 2014 13:22:13

I have created an SSIS package which, as its 2nd step, picks up a .dbf table and does a Data Flow Task of putting the .dbf table into a SQL Server 2005 table (which was just truncated in Step 1).

The Connection Manager that works on the .dbf table is Native OLE DB\Microsoft Jet 4.0 OLE DB Provider.

The entire SSIS package runs perfectly when i run it from my development environment.

I then assigned this Package to a SQL Server 2005 Job. The entire package is the sole step of the job.

But when I run the SQL Job, the Job fails immediately on Step 2.

Here is the error message in View History of the SQL Job:

-----------------------------------------------------------------------------

Executed as user: DBASE01\SQLExec. ...on 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:48:37 AM Progress: 2007-06-14 11:48:37.91 Source: DBF to ScanUS_Process Validating: 0% complete End Progress Progress: 2007-06-14 11:48:37.91 Source: DBF to ScanUS_Process Validating: 33% complete End Progress Error: 2007-06-14 11:48:37.91 Code: 0xC0202009 Source: DBF to ScanUS_Process ScanUS DBF Output File [1] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. End Error Error: 2007-06-14 11:48:37.91 Code: 0xC02020E8 Source: DBF to ScanUS_Process ScanUS DBF Output File [1] Description: Opening a rowset for "scanus_process" failed. Check that the object exists in the database. End Error Error: 2007-06-14 11:48:37.95 Code: 0xC004706B Source: DBF to ScanUS_Process DTS.Pipeline Description: "component "ScanUS... The package execution fa... The step failed.

-----------------------------------------------------------------------------

Could someone suggest why this works OK within SSIS but not when the SSIS Pakcage is called from SQL

thx very much/spirits.

seth j hersh

by **jwelc** » Sun, 02 Nov 2014 14:23:14

Does the SQL Server account have permissions on to the DBF file

by **Cindy Winegarde** » Tue, 04 Nov 2014 15:24:15

Hi SJHersh,

As JWelch said, the SISS package, when called from SQL, must have permissions to the directory where the DBF is located. Can you execute your SISS package when the DBF is stored on the local machine (SQL Server)

Also, you may be better off using the FoxPro and Visual FoxPro OLE DB data provider, downloadable from http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx . It works with all versions of FoxPro DBFs.