DTS runs great... but not from the job list

I have a DTS package that updates a database from a text file and it works
great when I run it manually from the DTS designer. However, I right click
on it and set it up to run automatically and it will not run. I went to
view the history of the job messages and it appears that the following is
the error for this task:
Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
Executing... 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 network name cannot be found. Error source: Microsoft Data
Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 Error Detail Records: Error:
67 (43); Provider Error: 67 (43) Error string: Error opening
datafile: The network name cannot be found. Error source: Microsoft
Data Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Can anyone give me any clues as to what the problem could be?
Schoo
Have a look at this KB that details the common reasons a scheduled DTS
package will fail
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default...;en-us;Q269074
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Schoo" <scott.schuman@.nospam.ma-hc.com> wrote in message
news:%23enkcwUrEHA.452@.TK2MSFTNGP09.phx.gbl...
>I have a DTS package that updates a database from a text file and it works
> great when I run it manually from the DTS designer. However, I right
> click
> on it and set it up to run automatically and it will not run. I went to
> view the history of the job messages and it appears that the following is
> the error for this task:
> Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
> Executing... 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 network name cannot be found. Error source: Microsoft Data
> Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 Error Detail Records: Error:
> 67 (43); Provider Error: 67 (43) Error string: Error opening
> datafile: The network name cannot be found. Error source:
> Microsoft
> Data Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 DTSRun OnFinish:
> DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
> Exit Code 1. The step failed.
> Can anyone give me any clues as to what the problem could be?
> Schoo
>
|||Hi Schoo,
Thanks for you post and I would love to show my gratidue for MVP Jasper
Smith's providing that great KB:Q269074
From your Error Message, I foudn the error was "The network name cannot be
found" so that please check whether there is anything wrong with your
source and target of database. Make sure they are "online", also please
check the permission issue. It seems strange and I need more information to
make further troubleshooting.
Would you please generate a small DTS package and see whether it works
fine? To get a better picture of the problem we would like to reproduct the
issue on our end. To expedite the process, please provide us the detailed
reproduce steps.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Schoo,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

DTS Package: EXCEPTION_ACCESS_VIOLATION when saving

My team is getting this message when altering a DTS Package, using SQL 2000 DTS Designer. We have downloaded all the SQL updates (Backward Compatibility, and the one specifically for DTS Save problems. Have tried this in SQL2005 as well. Have uninstalled both of these programs, and reinstalled on local machines. No avail. Here is what happens:

1) Editing an existing DTS Package, such as a Text Change, or simple connection change

2) Saving the file creates the exact message below:

Package Error

Error Source: Microsoft Data Transformation Services (DTS) Package

Error Description: Code execution exception: EXCEPTION_ACCESS_VIOLATION

3) Then you have to choose OK in the windows Popup Box.

4) The DTS is brought back to the original screen, or completely exited out of Designer.

5) From that point, the Package can no longer be opened for editing, unless you open a previous version (which means changes aren't saved.

We have also tried to SAVE AS another filename. This doesn't work either.

Our team has close to 75 DTS Packages, some simple, some more complex. We are corrupting these left and right. This problem happens no matter what is created in the DTS Package.

This is running into our business operations, and we are unable to find out a resolution...HELP

Any insight in this would be greatly appreciated.

Thanks,

Jason

The first thing that you need to do is back up msdb (or export sysdtspackages)! Make sure that you have all of your DTS packages available to reopen. I would first try exporting the package records to another server and see if you are getting the same thing. Possibly something has happened on that particular server.

Lee Everest

www.texastoo.com/sqlblog

DTS package to check if table exists

Hi all,

I have created a DTS package in SQL Server that takes Excel file and updates the database. The problem that i run into is - sometimes XLS file does not have any records in the table, and in this case i do not want DTS to execute and update the database.

The question is how to check ( i think usign Active X) if the exel table has any values in there or if it is blank?

a lot of thanks in advance,

Dmitry

yeah, and by the way,

when i execute the DTS i can see how many records are taken from XLS and copied into the database, if i can just somehow check that number, retreive that varible... that would solve the problem.

thanks again,

Dmitry

|||

It is often a good idea to import data into 'holding tables' (duplicate schema to the production tables), do whatever data cleanup/manipulation is required, and then move the data to the final tables.

With that process, it is easy to determine rowcount, duplications, etc, before running other code.

|||

ok...sounds ok, i have never done them before though... can you link an example or something..

many thanks

DTS package

Hi guyz,

I have created a DTS package that takes XLS file and updates the database. The problem that i run into is - sometimes XLS file does not have any records in the table, and in this case i do not want DTS to execute and update the database.

The question is i believe is how to check ( i think usign Active X) if the exel table has any values in there or if it is blank?

many thanks in advance,

Dmitry

Edit: Sorry, this is a response based on SSIS. I'm leaving it here as it might help someone using SSIS. If you are seeking DTS advice, please seek the DTS group: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg&hl=en

This isn't the most elegant solution, but it will work and can be done without programming.

Since the file exists, it just might not have any records in it, you'll be creating two data flows. The first data flow connects to the file and hooks to a Row Count Transformation. That's it. Store the results of the Row Count Transformation into a package-scoped variable.

The second data flow is what you've already got built, just use it as is.

Connect the two data flows with a precedence constraint and use the expression "@.Your_Variable > 0" to execute the second data flow if there were any records coming out of the Excel file.

|||

this is a very good idea, thank you very much...

but how would i count rows in the excel, can u maybe link some example please, or a code snippet?

many thanks in advance

|||If you are using SSIS, there is an Excel Source connection which you would hook up to a Row Count Transformation. Two pieces, very simple. No code or formula required. However, it sounds like you are using DTS (the old product that SSIS replaces)|||

Yes, im using DTS. any idea how to do it using DTS?

many thanks

Dmitry

|||

Again, this is an SSIS forum. For DTS questions, please see the DTS group:

http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg&hl=en