Data Transformation Job Keeps Executing

[mamartin]

Hi,

I have created a data transformation job (local package) withing SQL Server 7.0 to export a SQL Server database to a local Access database. The Access tables are dropped, created and then the data copied (there are 170 tables to be transferred). This part works fine when executed directly from the local package. However, when I schedule the job under SQL Server Agent and execute the job from there it never finishes. The Access tables are dropped, recreated and the table data copied to access, it just never shows the job as being completed. Any ideas?

Thanks,
Michael A. Martin

 

[SQLSister]

Are you sure it keeps executing or have you just forgotten to refresh your jobs?

 

[mamartin]

Yes, I refreshed the job many times and each time it was still executing. I have traced it back to the dtsrun.exe process. When the job began executing, I checked Task Manager and saw a process for dtsrun.exe. After a certain period of time passed, I checked the Access database and saw that it was populated with the tables. Checking Task Manager again, the dtsrun.exe process was still executing, but using with 0 CPU usage. We recently upgraded our server from 7.0 SP2 to 7.0 SP4. This (I think) is the problem. If I run the same job on a 7.0 SP2 SQL Server, no problem. SQL tables are exported to Access and the job completes. Something to do with SP4.

 

[susanma]

Hi,

We are experiencing this very same problem. Do you know if sql 2000 corrects it?

cheers, Sue

 

[mamartin]

I am not sure, I do not have access to SQL Server 2000. Good luck and hope someone out there can solve our problem.

 

[mamartin]

Sue,

a solution has been found. You must set the "close connection" option on the last task for a given connection. If you check Workflow properties on your connection, select the options tab and click the "close connection" box. In my export job, I had connections 1-4, so I had to locate the last task for each of these connections and select the "close connection". Hope this works for you.

Cheers,

Michael A. Martin