DTS package not copying full set of data

[MacLeod72]

Hello-

We have a DTS package that truncates the destination table and copies data into the destination from a source database on the same server. When we schedule the package or run it through Enterprise manager on the local machine, it succeeds. When it is run through Enterprise Manager from a remote machine (Win2000 or WinXP), the DTS package does not give us the correct results. The SQL Task to truncate runs successfully. When the Data Transform Task runs the task itself succeeds, but only transfers a small portion of the records (31 out of 14500). The server is up to date on patches and is on Win2000. This package has worked for over a year previous to this problem. The only known change to the server since the last run is the installation of MDAC 2.8. Any help you can give would be great.

Thanks!

--Rob

 

[gradley]

When you run the package remotely, are you running it from the DTS designer or from SQL Server Agent? If you are running it from designer, check that the connections you are using in the package are not set to (Local).

When you run a package remotely from designer, if the connections are set to local, it uses your local database connection.

 

[MacLeod72]

Thanks for the reply!

On the remote machine, the package is run from the DTS designer. All of our connections within the package are specified with the machine name, not '(local)'. In fact, the remote machines only have the client software installed.

--Rob

 

[gradley]

I don't believe that MDAC 2.8 is the problem if it still runs fine when you are signed onto that machine and the MDAC 2.8 is installed on that machine.

Have you tried running it from SQL Server Agent remotely? I would think this would run correctly.

It still sounds to me like there is something being executed locally that may not be appearant. You are not performing any data importing from disk are you?

 

[MacLeod72]

We have scheduled the job from a remote machine, and that does run successfully. This package doesn't do anything with flat files or local Access databases or the sort. It is just a SQL Task connected to a Data Transform task, which is making the whole thing that much more confusing.

We do have tasks that import a flat file from a UNC path to the database, and those packages run fine from both the local and remote machines.

--Rob

 

[gradley]

Have you tried running the steps one at a time? I'm wondering if the connection is timing out because of the remote call, and the package is not raising the error. Are you using the same connection for all of the tasks?

 

[MacLeod72]

I hadn't tried that yet. Unfortunately, running the Data Transform task as a single step yields the same result. There are actually two connections in the package; one is for the source and one is for the destination. The SQL Task runs off of the destination connection.

--Rob

 

[gradley]

Just curious.... Is it always 31 records, and what operating system does the SQL Server reside on?

 

[MacLeod72]

The Server itself is on Win2000, and each time the package is run remotely it always transfers 31 records.

 

[gradley]

I know I'm picking at straws here, but have you tried a different package / process to see if it runs successfully? I'm sure you know where i'm going with this.

Not sure why there would be an issue with data remotely but not locally. It's the only other things I could suggest at this time. Good luck!

If you don't get any more responses, try reposting the question so that one of the others can try to address it.

Good luck!

 

[MacLeod72]

I appreciate all of your help. I actually have a large number of similar packages and they all exhibit this problem. I'm just picking on this one .

Thanks again!

--Rob

 

[yksvaan]

Are you sure the other machine's sql client tools EM is sql2000 and the same sp as server?

What is the logon for the source connection? If it is integrated security, what windows logon yuo are in the "remote" machine. Unless you are working with other than admin priviledges, might there be a user scehema view in source db for that windows logon? I know this is far fetch, but anyway.

What does the Task's Source Tab Preview button show, those 31 records ?

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[MacLeod72]

Thanks for replying!

The client tools are the latest SQL2000 client tools. The login used for both the source and destination does not have admin rights, but is dbo on both databases. Also, the source pane shows 200 rows of data.

Thanks!

--Rob

 

[yksvaan]

Are you absolutely sure you are running the exact same instance of the package (and version), is it a local package at the sql server, or a structured file? And have you looked at the remote machinem that the options of the transform task don't have any restrictions to the amount of rows. BTW, is the Use Transactions check-box checked? If so, try with it unchecked. If your package is only one table transfer, you don't need transactions.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[MacLeod72]

The package itself sits locally on the SQL Server instance that houses the source and destination databases. The "Use Transactions" box was checked, but unchecking it did not resolve my problem, unfortunately.

 

[yksvaan]

What does package log look like ?

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[MacLeod72]

I don't have the rights to view the SQL Server DTS logs, but the Error file looks like this:

The execution of the following DTS Package succeeded: 

Package Name: TEST_S3_LOOKUP_IDX_SUBGROUP
Package Description: (null)
Package ID: {09D38C0E-9172-41A7-93E7-C2A03B198B1E}
Package Version: {9302DAAF-6050-47DE-B220-F268E48D426C}
Package Execution Lineage: {D72D9082-A480-49A5-9AF2-F01EC6E7CCAA}
Executed On: MIS-RAMOUR
Executed By: RAMOUR
Execution Started: 4/1/2005 9:43:24 AM
Execution Completed: 4/1/2005 9:43:24 AM
Total Execution Time: 0.141 seconds

Package Steps execution information:


Step 'DTSStep_DTSDataPumpTask_1' succeeded
Step Execution Started: 4/1/2005 9:43:24 AM
Step Execution Completed: 4/1/2005 9:43:24 AM
Total Step Execution Time: 0.101 seconds
Progress count in Step: 31

Step 'DTSStep_DTSExecuteSQLTask_3' succeeded
Step Execution Started: 4/1/2005 9:43:24 AM
Step Execution Completed: 4/1/2005 9:43:24 AM
Total Step Execution Time: 0.03 seconds
Progress count in Step: 0

As I read it, the package appears to be successful.

Does this help?

Thanks!

--Rob

 

[yksvaan]

What system produced that log ?

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[MacLeod72]

This log was created from my local machine (WinXP).

--Rob

 

[yksvaan]

Do you have some metadata / data lineage features in yuor package, or is this a "normal, plain" package ?

Cheers


[blue]Backup system is as good as the latest recovery[/blue]