Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


help needed with DTS ERROR

Posted on 2007-07-23
Medium Priority
Last Modified: 2013-11-30
Hi experts, I am having problems in finding out why a DTS job errors out.

Need help in debugging the why of the error.

I know a file is missing but where do I find the filename for this file? where do I have to look?

Run date/time - 20070721/23:00:04
Run duration in hours:minutes:seconds - 00:00:02
Executed as user: PVIEWS2001\SQLAdmin. ...cuting...   DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_1   DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_1   DTSRun OnStart:  DTSStep_DTSDataPumpTask_1   DTSRun OnError:  DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)      Error string:  Error opening datafile: The system cannot find the file specified.         Error source:  Microsoft Data Transformation Services Flat File Rowset Provider      Help file:  DTSFFile.hlp      Help context:  0      Error Detail Records:      Error:  2 (2); Provider Error:  2 (2)      Error string:  Error opening datafile: The system cannot find the file specified.         Error source:  Microsoft Data Transformation Services Flat File Rowset Provider      Help file:  DTSFFile.hlp      Help context:  0      DTSRun OnFinish:  DTSStep_DTSDataPumpTask_1   DTSRun OnStart:  DTSStep_DTSActiveScriptTask_1   DTSRun OnFinish:  DTSStep_DTSActiveScriptTask_1   DTSRun:  Package execution complete.  Process Exit Code 1.  The step failed.

GAP - ODS to GAP (Job outcome)
Run date/time - 20070721/23:00:04
Run duration in hours:minutes:seconds - 00:00:02 The job failed.  The Job was invoked by Schedule 90 (schedule2).  The last step to run was step 1 (GAP - ODS to GAP).

Question by:sharscho
  • 2
LVL 11

Expert Comment

ID: 19545823
I guess this is the DTS-package log-file.
One "simple" solution would be to create log-files for every step being run, that way you would be able to pinpoint more easily which step fails. And then it's just a matter of looking at the definition of that step.

(the DTS-GUI tends to give names like _STEP1 etc, which don't make much sense indeed =( If only they had taken the comment field ...)

Author Comment

ID: 19545927
It is the DTSStep_DTSDataPumpTask_1 step that is failing, it can open a datafile but where can I file the name of that datafile?

here is a little more info about the error description.

Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error opening datafile: The system cannot find the file specified.

Step Error code: 80004005
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0

How can I get the step of the dts package to see for which file it is looking?

Your answer is not clear, can you put more details?
LVL 11

Accepted Solution

deroby earned 1500 total points
ID: 19546010
DTSStep_DTSDataPumpTask_1 is the "technical name" of the step, it's most likely that that step is named differently in the GUI. In order to find the link between the technical name and the GUI name you need to either "script" the file and look in the generated (VB) files. Or you might try to do a guess by looking at the package in the GUI and checking the objects available. Things you know :
* it's a Flat File Rowset Provider, meaning it's a TEXT file
* it's probably one of the first ones to execute
* it fails, meaning that if you ask for its properties, you'll likely will get an error

In order to find the name of the file you will have to double-click the icon that represents the Text file.

I don't know how familiar you are with DTS ? If you're unable to make sense of this, then do the following : (MSSQL2000) : Start Enterprise Mgr, Browse to the DTS package that fails, double click the package so it opens in the GUI. Look for an icon that looks like a page with blue lines on it. Double-click it and see what file it tries to open, verify if that file exists. If all looks ok, look for next icon that resembles a page with blue lines on it ... etc...

Things to keep in mind :
* when the package runs from the server, eg. from a SQLAgent Job, then it will consider c:\inputfolder to be THE SERVER's C: drive, not the one on your computer !
* every step in a DTSPackage kan have it's own logfile, if that logfile is defined on a location that is not available (eg : c:\Temmp ) then you will also get a 'unable to open file' error


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question