DTS schedule job fails urgent pls help!

Hi all,

We have created DTS jobs which copies data from one server to another and truncates the destination before copying the data from source. when we run the job manually it runs fine, but when schedule its failing with the below errors.

DTSRun: Loading...DTSRun: Executing...DTSRun
OnStart: DTSStep_DTSExecuteSQLTask_1DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1DTSRun OnStart: Copy Data
from CustomerMaster to
[OUTBOUND].[dbo].[CustomerMaster]
StepDTSRun OnError: Copy Data from CustomerMaster
to [OUTBOUND].[dbo].[CustomerMaster]
Step, Error = -2147008507 (80074005) Error string:
Unspecified error Error source: Microsoft Data
Transformation Services (DTS) Package Help file:
sqldts.hlp Help context: 1100Error Detail
Records:Error: -2147008507 (80074005); Provider
Error: 0 (0) Error string: Unspecified error
Error source: Microsoft Data Transformation Services
(DTS) Package Help file: sqldts.hlp Help context:
1100Error: -2147467259 (80004005); Provider Error:
0 (0) Error string: Timeout expired Error source:
Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0DTSRun OnFinish: Copy Data from
CustomerMaster to
[OUTBOUND].[dbo].[CustomerMaster]
StepDTSRun: Package execution complete.
please suggest to over come this problem.

TIAMy guess is that the DTS job is scheduled to run as a different NT logon than the one you are using to test it interactively. The scheduled logon is probably missing one or more permissions needed for the DTS package.

-PatP

DTS package elements at run time through VB6

How can i configure DTS package elements at run time through VB6..?
Like Specifying the Source,Destination etcTry to start http://search.microsoft.com/search/results.aspx?View=msdn&st=a&qu=SQL-DMO&c=0&s=1

DTS Package - Destination server is now off domain.

Okay, I figured it out (right after I made my post). The issue was
that my connection in the DTS package was connecting to the database
that I was trying to drop. I changed it to default to the master
database and it worked fine.
Thank You,
Jeremy
jbiros wrote:
> We have a DTS package that refreshes our external website data on a
> nightly basis. I recently moved our external website off of our
> domain. The SQL Services were previously running as
> DOMAIN\Administrator account. When I moved the server off of the
> domain, I reinstalled SQL Server and set the services to run as a
> local, non-administrative account, MACHINE\sqlservices. The reason I
> did this is, if someone were to compromise the machine via the SQL
> Services, they would not have administrative access to the machine.
> Now, my DTS package fails. I have been reading many posts on the
> Internet to try to understand why the package fails, but I have not
> been able to figure it out. When it fails, I am logged into the
> Internal machine as DOMAIN\Administrator. The owner of the package is
> also DOMAIN\Administrator.
> Step 1: (succeeds) - sp_UserKill. This procedure kills all SPIDs with
> the specified database (DATABASENAME). It appears to be successful.
> Step 2: (fails) - drop database DATABASENAME. This fails with the
> following message: "Cannot drop the database 'DATABASENAME' because it
> is currently in use." Checking enterprise manager, it does not appear
> to be in use. I've cycled the SQL Services and rerun with no luck.
> I have a feeling that this is an issue with security, but I can't
> figure it out. Can someone please assist me?
> Thank you in advance.
> JeremyWe have a DTS package that refreshes our external website data on a
nightly basis. I recently moved our external website off of our
domain. The SQL Services were previously running as
DOMAIN\Administrator account. When I moved the server off of the
domain, I reinstalled SQL Server and set the services to run as a
local, non-administrative account, MACHINE\sqlservices. The reason I
did this is, if someone were to compromise the machine via the SQL
Services, they would not have administrative access to the machine.
Now, my DTS package fails. I have been reading many posts on the
Internet to try to understand why the package fails, but I have not
been able to figure it out. When it fails, I am logged into the
Internal machine as DOMAIN\Administrator. The owner of the package is
also DOMAIN\Administrator.
Step 1: (succeeds) - sp_UserKill. This procedure kills all SPIDs with
the specified database (DATABASENAME). It appears to be successful.
Step 2: (fails) - drop database DATABASENAME. This fails with the
following message: "Cannot drop the database 'DATABASENAME' because it
is currently in use." Checking enterprise manager, it does not appear
to be in use. I've cycled the SQL Services and rerun with no luck.
I have a feeling that this is an issue with security, but I can't
figure it out. Can someone please assist me?
Thank you in advance.
Jeremy|||Okay, I figured it out (right after I made my post). The issue was
that my connection in the DTS package was connecting to the database
that I was trying to drop. I changed it to default to the master
database and it worked fine.
Thank You,
Jeremy
jbiros wrote:
> We have a DTS package that refreshes our external website data on a
> nightly basis. I recently moved our external website off of our
> domain. The SQL Services were previously running as
> DOMAIN\Administrator account. When I moved the server off of the
> domain, I reinstalled SQL Server and set the services to run as a
> local, non-administrative account, MACHINE\sqlservices. The reason I
> did this is, if someone were to compromise the machine via the SQL
> Services, they would not have administrative access to the machine.
> Now, my DTS package fails. I have been reading many posts on the
> Internet to try to understand why the package fails, but I have not
> been able to figure it out. When it fails, I am logged into the
> Internal machine as DOMAIN\Administrator. The owner of the package is
> also DOMAIN\Administrator.
> Step 1: (succeeds) - sp_UserKill. This procedure kills all SPIDs with
> the specified database (DATABASENAME). It appears to be successful.
> Step 2: (fails) - drop database DATABASENAME. This fails with the
> following message: "Cannot drop the database 'DATABASENAME' because it
> is currently in use." Checking enterprise manager, it does not appear
> to be in use. I've cycled the SQL Services and rerun with no luck.
> I have a feeling that this is an issue with security, but I can't
> figure it out. Can someone please assist me?
> Thank you in advance.
> Jeremy

DTS Package

Hi,

I create DTS Package, when I do the Transform Data Task Properties, the Table/View (Source Tab), and Table Name (Destination Tab), how can I see the whole table name? Especially, Table Name in Destination Tab, it show [database name].[dbo].[table name], I only see the 1st char of the table name in the dropdown list. Could you please let me know how can I resize the dropdownlist in order to see the table name?

Thanks.If you select that column on destination tab, using key board left arrow key you can move to the end of the value.

DTS migration wizard, where does it put migrated version?

From SQL Server Management Studio I right click on Management/Legacy/DTS and select Migration Wizard and enter a SQL Server Source and Destination. A list of DTS packages from the source server is displayed and I select one of the DTS packages and give the destination a unique name. The wizard runs and says it's successful. I can't find the new SSIS version of the DTS package with the new name that I've given it, however. Where does it put the new migrated copy of the DTS package.

Thanks,

John

In Sql Management Studio, under the Object Explorer window in the Connect drop down choose Integration Services, and it will show you all stored packages on that instance.|||Thanks, this helps. Now I'd like to find out how to edit the migrated version. How to I get to it from BI Dev Studio?|||

All of the stored packages will usually be in ~\Program Files\Microsoft SQL Server\90\DTS\Packages

DTS Insert or Update

Is there an easy way with DTS to pump data from one table to another so that it will update the row if it exists (the source and destination have the same value for the ID colum) or insert it if it doesn't.

I know this can be done with stored procedures/sql by doing IF EXISTS UPDATE ELSE INSERT but there are many tables and columns and this will be very tiime consuming.

i think your going to have to use VBScript in the transformation step of the DTS Package to evaluate if the record exists. Similar to what one would do in the stored proc.

hth,

mcm

DTS Import error on CkDate

Im trying to import a tbl from access why am i getting this msg?
Error at destination for row #58
Insert error, colm 6 ('Check Date, dbtype_dbtimestamp), status 6: Data over flow.
Invalid character value for cast specification.Please look in Access at row 58 and see what the illegal character is. Use the dts to clean the data as it come in.

Take a look aWarning: Non-existent step referenced by
@.on_success_step_id.
attachment as see what these errors are???

Warning: Non-existent step referenced by @.on_success_step_id.
Warning: Non-existent step referenced by @.on_success_step_id.
Warning: Non-existent step referenced by @.on_success_step_id.
Warning: Non-existent step referenced by @.on_success_step_id.

Thanks|||The data in row 58 for check date is a regular date

I dont understand what your saying about :

Take a look aWarning: Non-existent step referenced by
@.on_success_step_id.

or how to transform data from dts|||Specifically, what do you have set in SQL Server as the datatype for your date/time column?

Can you show us several rows that you're attempting to import, including the one that failed?|||I was trying to use datetime as the colm type. I end up with no rows in my table and all the rows in my access tbl are the same format :

Date_Entered Initials Vendor Name CheckNum Type ChkDate ChkAmt
11/19/02 ns 21' Club Inc. 1083 Meal 10/16/02 1,170.59
11/15/02 ns 21' Club Inc. 1091 Meal 11/12/02 1,634.68|||how are the delimited during your import? Tabs?|||I was just using Dts wizard import from an access database.|||Just a thought.. make a backup of your MDB, pull out all the rows but one and see if it imports ok. Then try this again with the row #58 row and see if it goes through or bombs again.